[Home] [Help]
PACKAGE BODY: APPS.WMS_PICKING_PKG
Source
1 PACKAGE BODY wms_picking_pkg AS
2 /* $Header: WMSPLPDB.pls 120.46.12020000.5 2013/03/29 13:32:49 raminoch ship $ */
3
4 g_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
5 g_group_sequence_number NUMBER := 1;
6 g_max_group_sequence_number NUMBER := -1; -- Bug#5185031
7 g_period_id NUMBER;
8
9 --for UCC128, same as in WMSUCCSB.pls
10 g_gtin_cross_ref_type VARCHAR2(25) := fnd_profile.value('INV:GTIN_CROSS_REFERENCE_TYPE');
11 g_gtin_code_length NUMBER := 14;
12
13 g_cartonization_ids numset_tabType; --Bug: 7254397 Store LPNs for ClusterPickByLabel
14 g_cartonization_ids_inx NUMBER := 0; --Bug: 7254397
15
16 --Added for Case Picking Project start
17 g_order_numbers numset_tabType;
18 g_order_numbers_inx NUMBER := 0;
19
20 g_pick_slip_numbers numset_tabType;
21 g_pick_slip_numbers_inx NUMBER := 0;
22 --Added for Case Picking Project end
23
24 PROCEDURE mydebug(p_msg IN VARCHAR2, p_api_name IN VARCHAR2) IS
25 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
26 BEGIN
27 IF g_trace_on = 1 THEN
28 inv_mobile_helper_functions.tracelog( p_err_msg => p_msg
29 , p_module => 'WMS_PICKING_PKG.' || p_api_name
30 , p_level => 4
31 );
32 END IF;
33 END;
34 -- Bug: 7254397
35 PROCEDURE insert_cartonization_id (
36 p_lpn_id IN NUMBER,
37 x_return_status OUT nocopy VARCHAR2,
38 x_msg_count OUT nocopy NUMBER,
39 x_msg_data OUT nocopy VARCHAR2)
40 IS
41 l_api_name VARCHAR2(30) := 'insert_cartonization_id';
42 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
43 BEGIN
44 IF (l_debug = 1) THEN
45 mydebug('Enter:', l_api_name);
46 mydebug('p_lpn_id: ' || TO_CHAR(p_lpn_id), l_api_name);
47 END IF;
48 g_cartonization_ids_inx := g_cartonization_ids_inx + 1;
49 g_cartonization_ids(g_cartonization_ids_inx) := p_lpn_id;
50 x_return_status := 'S';
51 EXCEPTION
52 WHEN OTHERS THEN
53 x_return_status :='U';
54 IF (l_debug = 1) THEN
55 mydebug('Unknown Exception occurred: ' || SQLERRM,'insert_cartonization_id');
56 END IF;
57 END;
58
59 -- Bug: 7254397
60 FUNCTION list_cartonization_id RETURN numset_t PIPELINED
61 IS
62 l_api_name VARCHAR2(30) := 'list_cartonization_id';
63 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
64 l_count NUMBER;
65 BEGIN
66 IF (l_debug = 1) THEN
67 mydebug('Enter:', l_api_name);
68 END IF;
69 l_count := g_cartonization_ids.COUNT;
70 FOR i IN 1..l_count LOOP
71 PIPE ROW(g_cartonization_ids(i));
72 END LOOP;
73 RETURN;
74 EXCEPTION
75 WHEN OTHERS THEN
76 IF (l_debug = 1) THEN
77 mydebug('Unknown Exception occurred: ' || SQLERRM,'list_cartonization_id');
78 END IF;
79 RETURN;
80 END;
81 -- Bug: 7254397
82 PROCEDURE clear_cartonization_id(
83 x_return_status OUT nocopy VARCHAR2,
84 x_msg_count OUT nocopy NUMBER,
85 x_msg_data OUT nocopy VARCHAR2)
86 IS
87 l_api_name VARCHAR2(30) := 'clear_cartonization_id';
88 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
89 BEGIN
90 IF (l_debug = 1) THEN
91 mydebug('Enter:', l_api_name);
92 END IF;
93 g_cartonization_ids.DELETE;
94 g_cartonization_ids_inx := 0;
95 x_return_status :='S';
96 EXCEPTION
97 WHEN OTHERS THEN
98 x_return_status :='U';
99 IF (l_debug = 1) THEN
100 mydebug('Unknown Exception occurred: ' || SQLERRM,'clear_cartonization_id');
101 END IF;
102 END;
103
104 FUNCTION get_total_lpns RETURN NUMBER IS
105 BEGIN
106 RETURN g_cartonization_ids_inx;
107 END;
108
109 --Added for Case Picking Project start
110 PROCEDURE insert_order_numbers (
111 p_order_number IN NUMBER,
112 x_return_status OUT nocopy VARCHAR2,
113 x_msg_count OUT nocopy NUMBER,
114 x_msg_data OUT nocopy VARCHAR2)
115 IS
116 l_api_name VARCHAR2(30) := 'insert_order_numbers';
117 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
118 BEGIN
119 IF (l_debug = 1) THEN
120 mydebug('Enter:', l_api_name);
121 mydebug('p_order_number: ' || TO_CHAR(p_order_number), l_api_name);
122 END IF;
123 g_order_numbers_inx := g_order_numbers_inx + 1;
124 g_order_numbers(g_order_numbers_inx) := p_order_number;
125 x_return_status := 'S';
126 EXCEPTION
127 WHEN OTHERS THEN
128 x_return_status :='U';
129 IF (l_debug = 1) THEN
130 mydebug('Unknown Exception occurred: ' || SQLERRM,'insert_order_numbers');
131 END IF;
132 END;
133
134 FUNCTION list_order_numbers RETURN numset_t PIPELINED
135 IS
136 l_api_name VARCHAR2(30) := 'list_order_numbers';
137 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
138 l_count NUMBER;
139 BEGIN
140 IF (l_debug = 1) THEN
141 mydebug('Enter:', l_api_name);
142 END IF;
143 l_count := g_order_numbers.COUNT;
144 mydebug('l_count'||l_count, l_api_name);
145 FOR i IN 1..l_count LOOP
146 PIPE ROW(g_order_numbers(i));
147 END LOOP;
148 RETURN;
149 EXCEPTION
150 WHEN NO_DATA_NEEDED THEN
151 IF (l_debug = 1) THEN
152 mydebug('NO_DATA_NEEDED Exception occurred: ' || SQLERRM,'list_order_numbers');
153 END IF;
154 WHEN OTHERS THEN
155 IF (l_debug = 1) THEN
156 mydebug('Unknown Exception occurred: ' || SQLERRM,'list_order_numbers');
157 END IF;
158 RETURN;
159 END;
160
161 PROCEDURE clear_order_numbers(
162 x_return_status OUT nocopy VARCHAR2,
163 x_msg_count OUT nocopy NUMBER,
164 x_msg_data OUT nocopy VARCHAR2)
165 IS
166 l_api_name VARCHAR2(30) := 'clear_order_numbers';
167 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
168 BEGIN
169 IF (l_debug = 1) THEN
170 mydebug('Enter:', l_api_name);
171 END IF;
172 g_order_numbers.DELETE;
173 g_order_numbers_inx := 0;
174 x_return_status :='S';
175 EXCEPTION
176 WHEN OTHERS THEN
177 x_return_status :='U';
178 IF (l_debug = 1) THEN
179 mydebug('Unknown Exception occurred: ' || SQLERRM,'clear_order_numbers');
180 END IF;
181 END;
182
183 PROCEDURE insert_pick_slip_number (
184 p_pick_slip_number IN NUMBER,
185 x_return_status OUT nocopy VARCHAR2,
186 x_msg_count OUT nocopy NUMBER,
187 x_msg_data OUT nocopy VARCHAR2)
188 IS
189 l_api_name VARCHAR2(30) := 'insert_pick_slip_number';
190 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
191 BEGIN
192 IF (l_debug = 1) THEN
193 mydebug('Enter:', l_api_name);
194 mydebug('p_pick_slip_number: ' || TO_CHAR(p_pick_slip_number), l_api_name);
195 END IF;
196 g_pick_slip_numbers_inx := g_pick_slip_numbers_inx + 1;
197 g_pick_slip_numbers(g_pick_slip_numbers_inx) := p_pick_slip_number;
198 x_return_status := 'S';
199 EXCEPTION
200 WHEN OTHERS THEN
201 x_return_status :='U';
202 IF (l_debug = 1) THEN
203 mydebug('Unknown Exception occurred: ' || SQLERRM,'insert_pick_slip_number');
204 END IF;
205 END;
206
207 FUNCTION list_pick_slip_numbers RETURN numset_t PIPELINED
208 IS
209 l_api_name VARCHAR2(30) := 'list_pick_slip_numbers';
210 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
211 l_count NUMBER;
212 BEGIN
213 IF (l_debug = 1) THEN
214 mydebug('Enter:', l_api_name);
215 END IF;
216 l_count := g_pick_slip_numbers.COUNT;
217 mydebug('l_count'||l_count, l_api_name);
218 FOR i IN 1..l_count LOOP
219 PIPE ROW(g_pick_slip_numbers(i));
220 END LOOP;
221 RETURN;
222 EXCEPTION
223 WHEN NO_DATA_NEEDED THEN
224 IF (l_debug = 1) THEN
225 mydebug('NO_DATA_NEEDED Exception occurred: ' || SQLERRM,'clear_order_numbers');
226 END IF;
227
228 WHEN OTHERS THEN
229 IF (l_debug = 1) THEN
230 mydebug('Unknown Exception occurred: ' || SQLERRM,'list_pick_slip_numbers');
231 END IF;
232 RETURN;
233 END;
234
235 PROCEDURE clear_pick_slip_number(
236 x_return_status OUT nocopy VARCHAR2,
237 x_msg_count OUT nocopy NUMBER,
238 x_msg_data OUT nocopy VARCHAR2)
239 IS
240 l_api_name VARCHAR2(30) := 'clear_pick_slip_number';
241 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
242 BEGIN
243 IF (l_debug = 1) THEN
244 mydebug('Enter:', l_api_name);
245 END IF;
246 g_pick_slip_numbers.DELETE;
247 g_pick_slip_numbers_inx := 0;
248 x_return_status :='S';
249 EXCEPTION
250 WHEN OTHERS THEN
251 x_return_status :='U';
252 IF (l_debug = 1) THEN
253 mydebug('Unknown Exception occurred: ' || SQLERRM,'clear_pick_slip_number');
254 END IF;
255 END;
256
257
258 --Added for Case Picking Project end
259
260
261
262 PROCEDURE change_task_to_active(p_transaction_temp_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2) IS
263 --PRAGMA AUTONOMOUS_TRANSACTION;
264 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
265 BEGIN
266 x_return_status := fnd_api.g_ret_sts_success;
267
268 UPDATE wms_dispatched_tasks
269 SET status = 9
270 WHERE transaction_temp_id = p_transaction_temp_id;
271
272 COMMIT;
273 EXCEPTION
274 WHEN OTHERS THEN
275 x_return_status := fnd_api.g_ret_sts_error;
276 END;
277
278 --
279 -- Name
280 -- PROCEDURE GET_NEXT_TASK_INFO
281 --
282 -- Purpose
283 -- Gets the task information.
284 --
285 -- Input Parameters
286 -- p_sign_on_emp_id => Employee ID
287 -- p_sign_on_org_id => Organization ID
288 -- p_transaction_temp_id => Transaction Temp ID (For Manual Pick)
289 -- p_cartonization_id => Cartonization ID (For Label Picking)
290 -- p_device_id => Device ID
291 -- p_is_cluster_pick => Cluster Pick or not
292 -- p_cartons_list => Carton Grouping ID List (For Cluster Picking)
293 --
294 -- Output Parameters
295 -- x_task_info => Ref Cursor containing the Task Information
296 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
297 -- FND_API.G_RET_STS_ERROR
298 -- x_error_code => Code indicating the error message.
299 -- x_error_mesg => Error Messages
300 -- x_mesg_count => Error Messages Count
301 PROCEDURE get_next_task_info(
302 p_sign_on_emp_id IN NUMBER
303 , p_sign_on_org_id IN NUMBER
304 , p_transaction_temp_id IN NUMBER := NULL
305 , p_cartonization_id IN NUMBER := NULL
306 , p_device_id IN NUMBER := NULL
307 , x_return_status OUT NOCOPY VARCHAR2
308 , x_error_code OUT NOCOPY NUMBER
309 , x_mesg_count OUT NOCOPY NUMBER
310 , x_error_mesg OUT NOCOPY VARCHAR2
311 , x_task_info OUT NOCOPY t_genref
312 , p_is_cluster_pick IN VARCHAR2
313 , p_cartons_list IN VARCHAR2
314 , p_is_manifest_pick IN VARCHAR2 --Added for Case Picking Project
315 ) IS
316
317 cursor c_lot_csr(p_temp_id NUMBER) is
318 select lot_number
319 from mtl_transaction_lots_temp
320 where transaction_temp_id = p_temp_id;
321
322 cursor c_same_lot_csr(p_temp_id NUMBER, p_lot VARCHAR2) is
323 select primary_quantity, transaction_quantity, serial_transaction_temp_id
324 from mtl_transaction_lots_temp
325 where transaction_temp_id = p_temp_id
326 and lot_number = p_lot;
327
328 --OVPK Start 1
329
330 l_check_overpick_passed VARCHAR2(1);
331 l_is_bulk_picked_task VARCHAR2(1);
332 l_move_order_type NUMBER;
333 l_temp VARCHAR2(1);
334
335 --OVPK End 1
336
337 l_api_name VARCHAR2(30) := 'GET_NEXT_TASK_INFO';
338 l_transaction_temp_id NUMBER;
339 l_task_id NUMBER;
340 l_org_id NUMBER;
341 l_inventory_item_id NUMBER;
342 l_cartonization_id NUMBER;
343 l_allocated_lpn_id NUMBER;
344 l_serial_number_control_code NUMBER;
345 l_lot_control_code NUMBER;
346 l_sl_alloc_flag VARCHAR2(1) := 'N';
347 l_serial_temp_id NUMBER := -999;
348 l_carton_name VARCHAR2(30);
349 l_carton_item_id NUMBER := -999;
350 l_carton_item_name VARCHAR2(40) := '';
351 l_allocated_lpn_name VARCHAR2(30) := '';
352 l_allocated_outermost_lpn_name VARCHAR2(30) := '';
353 l_nbr_tasks NUMBER := -999;
354 l_sql VARCHAR2(20000);
355 l_task_cur t_genref;
356 b_is_cluster_pick BOOLEAN := FALSE;
357 l_cartons_list VARCHAR2(4000) := ' (-999) ';
358 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
359
360 l_same_lot_rec_cnt NUMBER;
361 l_same_lot_pri_qty NUMBER;
362 l_same_lot_tra_qty NUMBER;
363 l_sum_same_lot_pri_qty NUMBER;
364 l_sum_same_lot_tra_qty NUMBER;
365 l_mtlt_rec mtl_transaction_lots_temp%ROWTYPE;
366 l_lot VARCHAR2(2000); --Bug 6148865
367 l_old_lot VARCHAR2(2000); --Bug 6148865
368 l_new_serial_temp_id NUMBER;
369 l_delivery_id NUMBER;
370 l_carton_grouping_id NUMBER;
371 l_cluster_key VARCHAR2(80);
372 l_transaction_action_id NUMBER;
373
374 --For UCC128
375 l_item_type VARCHAR2(1);
376
377 --Start Bug 6682436
378 l_honor_case_pick_flag VARCHAR2(1) := 'N';
379 l_template_name VARCHAR2(128) := NULL;
380 --End Bug 6682436
381 l_allocate_lot_flag VARCHAR2(1); -- Added for 14699845 (Flexible Lot Allocation)
382
383 -- Start Bug 4434111
384 CURSOR c_get_serials(p_transaction_temp_id NUMBER) IS
385 SELECT msnt.fm_serial_number,
386 msnt.to_serial_number
387 FROM mtl_serial_numbers_temp msnt
388 WHERE msnt.transaction_temp_id = p_transaction_temp_id;
389 --
390 l_serial_alloc_flag mtl_material_transactions_temp.serial_allocated_flag%TYPE := 'N';
391 l_user_id NUMBER;
392 l_fm_serial_number VARCHAR2(30);
393 l_to_serial_number VARCHAR2(30);
394 --
395 CURSOR c_get_serialLots(p_transaction_temp_id NUMBER) IS
396 SELECT msnt.fm_serial_number, msnt.to_serial_number
397 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
398 WHERE msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
399 AND mtlt.transaction_temp_id = p_transaction_temp_id;
400 -- End Bug 4434111
401 BEGIN
402 IF (l_debug = 1) THEN
403 mydebug('Enter to GET_NEXT_TASK_INFO procedure', l_api_name);
404 END IF;
405 x_error_code := 0;
406 x_mesg_count := 0;
407 x_error_mesg := '';
408 x_return_status := fnd_api.g_ret_sts_success;
409 IF (l_debug = 1) THEN
410 mydebug('Get the transaction temp id which will be the next task to be processed', l_api_name);
411 END IF;
412 SAVEPOINT next_task_inquiry;
413
414 IF p_is_cluster_pick = 'Y'
415 OR p_is_cluster_pick = 'y' THEN
416 --{
417 IF (l_debug = 1) THEN
418 mydebug('Cluster pick task', l_api_name);
419 END IF;
420 --}
421 ELSE -- not a cluster pick task
422 --{
423 IF (p_transaction_temp_id IS NOT NULL) THEN
424 IF (l_debug = 1) THEN
425 mydebug('Get the relevant info for transaction temp id: '|| p_transaction_temp_id, l_api_name);
426 END IF;
427
428 BEGIN
429 SELECT mmtt.transaction_temp_id
430 , wdt.task_id
431 , mmtt.organization_id
432 , mmtt.inventory_item_id
433 , mmtt.cartonization_id
434 , mmtt.allocated_lpn_id
435 , mmtt.transaction_action_id
436 , DECODE (mmtt.parent_line_id, mmtt.transaction_temp_id, 'Y', 'N')
437 , mmtt.serial_allocated_flag -- Bug 4434111
438 INTO l_transaction_temp_id
439 , l_task_id
440 , l_org_id
441 , l_inventory_item_id
442 , l_cartonization_id
443 , l_allocated_lpn_id
444 , l_transaction_action_id
445 , l_is_bulk_picked_task
446 , l_serial_alloc_flag -- Bug 4434111
447 FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
448 WHERE wdt.person_id = p_sign_on_emp_id
449 AND wdt.organization_id = p_sign_on_org_id
450 AND wdt.status <= 3
451 AND wdt.task_type IN (1, 4, 5, 6)
452 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
453 AND mmtt.transaction_temp_id = p_transaction_temp_id;
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 x_error_code := 1;
457 RAISE fnd_api.g_exc_unexpected_error;
458 END;
459 ELSIF (p_cartonization_id IS NOT NULL) THEN
460 IF (l_debug = 1) THEN
461 mydebug('Get the relevant info for carton id:'|| p_cartonization_id, l_api_name);
462 END IF;
463
464 BEGIN
465 --bugfix 2961842. Changed the SQL into a subquery so that ORDER BY is evaluated before filtering the first task
466 SELECT tt.transaction_temp_id
467 , tt.task_id
468 , tt.organization_id
469 , tt.inventory_item_id
470 , tt.cartonization_id
471 , tt.allocated_lpn_id
472 , tt.serial_allocated_flag -- Bug 4434111
473 INTO l_transaction_temp_id
474 , l_task_id
475 , l_org_id
476 , l_inventory_item_id
477 , l_cartonization_id
478 , l_allocated_lpn_id
479 , l_serial_alloc_flag -- Bug 4434111
480 FROM
481 (
482 SELECT mmtt.transaction_temp_id transaction_temp_id
483 , wdt.task_id task_id
484 , mmtt.organization_id organization_id
485 , mmtt.inventory_item_id inventory_item_id
486 , mmtt.cartonization_id cartonization_id
487 , mmtt.allocated_lpn_id allocated_lpn_id
488 , mmtt.serial_allocated_flag serial_allocated_flag
489 FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
490 WHERE wdt.person_id = p_sign_on_emp_id
491 AND wdt.organization_id = p_sign_on_org_id
492 AND wdt.status <= 3
493 AND (wdt.task_type IN (1, 4, 5, 6))
494 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
495 AND mmtt.cartonization_id = p_cartonization_id
496 AND sub.organization_id = mmtt.organization_id
497 AND sub.secondary_inventory_name = mmtt.subinventory_code
498 AND loc.organization_id = mmtt.organization_id
499 AND loc.inventory_location_id = mmtt.locator_id
500 ORDER BY wdt.priority, sub.picking_order, -- for task resequencing in the pick load page
501 loc.picking_order, wdt.task_id
502
503 ) tt
504 WHERE ROWNUM < 2;
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 x_error_code := 1;
508 RAISE fnd_api.g_exc_unexpected_error;
509 END;
510 ELSIF (p_device_id IS NOT NULL) THEN
511 IF (l_debug = 1) THEN
512 mydebug('Get the relevant info for device id:'|| p_device_id, l_api_name);
513 END IF;
514
515 BEGIN
516 --bugfix 2961842. Changed the SQL into a subquery so that ORDER BY is evaluated before filtering the first task
517 SELECT tt.transaction_temp_id
518 , tt.task_id
519 , tt.organization_id
520 , tt.inventory_item_id
521 , tt.cartonization_id
522 , tt.allocated_lpn_id
523 , tt.serial_allocated_flag -- Bug 4434111
524 INTO l_transaction_temp_id
525 , l_task_id
526 , l_org_id
527 , l_inventory_item_id
528 , l_cartonization_id
529 , l_allocated_lpn_id
530 , l_serial_alloc_flag -- Bug 4434111
531 FROM
532 (
533 SELECT mmtt.transaction_temp_id transaction_temp_id
534 , wdt.task_id task_id
535 , mmtt.organization_id organization_id
536 , mmtt.inventory_item_id inventory_item_id
537 , mmtt.cartonization_id cartonization_id
538 , mmtt.allocated_lpn_id allocated_lpn_id
539 , mmtt.serial_allocated_flag serial_allocated_flag
540 FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
541 WHERE wdt.person_id = p_sign_on_emp_id
542 AND wdt.organization_id = p_sign_on_org_id
543 AND wdt.status <= 3
544 AND (wdt.task_type IN (1, 4, 5, 6))
545 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
546 AND wdt.device_id = p_device_id
547 AND wdt.device_invoked = 'Y'
548 AND sub.organization_id = mmtt.organization_id
549 AND sub.secondary_inventory_name = mmtt.subinventory_code
550 AND loc.organization_id = mmtt.organization_id
551 AND loc.inventory_location_id = mmtt.locator_id
552 ORDER BY wdt.priority, sub.picking_order, -- for task resequencing in the pick load page
553 loc.picking_order, wdt.task_id
554 )tt
555 WHERE ROWNUM < 2;
556 EXCEPTION
557 WHEN NO_DATA_FOUND THEN
558 x_error_code := 1;
559 RAISE fnd_api.g_exc_unexpected_error;
560 END;
561 ELSE
562 IF (l_debug = 1) THEN
563 mydebug('Open cursor eligible_tasks', l_api_name);
564 END IF;
565
566 BEGIN
567 --bugfix 2961842. Changed the SQL into a subquery so that ORDER BY is evaluated before filtering the first task
568 SELECT tt.transaction_temp_id
569 , tt.task_id
570 , tt.organization_id
571 , tt.inventory_item_id
572 , tt.cartonization_id
573 , tt.allocated_lpn_id
574 , tt.serial_allocated_flag -- Bug 4434111
575 INTO l_transaction_temp_id
576 , l_task_id
577 , l_org_id
578 , l_inventory_item_id
579 , l_cartonization_id
580 , l_allocated_lpn_id
581 , l_serial_alloc_flag -- Bug 4434111
582 FROM
583 (
584 SELECT mmtt.transaction_temp_id transaction_temp_id
585 , wdt.task_id task_id
586 , mmtt.organization_id organization_id
587 , mmtt.inventory_item_id inventory_item_id
588 , mmtt.cartonization_id cartonization_id
589 , mmtt.allocated_lpn_id allocated_lpn_id
590 , mmtt.serial_allocated_flag serial_allocated_flag
591 FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
592 WHERE wdt.person_id = p_sign_on_emp_id
593 AND wdt.organization_id = p_sign_on_org_id
594 AND wdt.status <= 3
595 AND (wdt.task_type IN (1, 4, 5, 6))
596 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
597 AND sub.organization_id = mmtt.organization_id
598 AND sub.secondary_inventory_name = mmtt.subinventory_code
599 AND loc.organization_id = mmtt.organization_id
600 AND loc.inventory_location_id = mmtt.locator_id
601 ORDER BY wdt.priority, sub.picking_order, -- for task resequencing in the pick load page
602 loc.picking_order, wdt.task_id
603 )tt
604 WHERE ROWNUM < 2;
605 EXCEPTION
606 WHEN NO_DATA_FOUND THEN
607 x_error_code := 1;
608 RAISE fnd_api.g_exc_unexpected_error;
609 END;
610 END IF;
611 --}
612 END IF; -- cluster pick check
613
614 -- Bug# 4185621: update child line posting flag to 'N' for bulking picking task
615 IF (l_is_bulk_picked_task = 'Y') THEN
616 UPDATE mtl_material_transactions_temp mmtt
617 SET posting_flag = 'N'
618 WHERE parent_line_id = p_transaction_temp_id
619 AND parent_line_id <> transaction_temp_id;
620 END IF;
621 -- Bug# 4185621: end change
622
623 IF (l_debug = 1) THEN
624 mydebug('Get the next task to be performed. temp id:'|| l_transaction_temp_id, l_api_name);
625 END IF;
626
627 SELECT serial_number_control_code
628 , lot_control_code
629 INTO l_serial_number_control_code
630 , l_lot_control_code
631 FROM mtl_system_items_b
632 WHERE inventory_item_id = l_inventory_item_id
633 AND organization_id = l_org_id;
634
635 IF (l_debug = 1) THEN
636 mydebug('Serial control code : '|| l_serial_number_control_code, l_api_name);
637 mydebug('Lot control code : '|| l_lot_control_code, l_api_name);
638 END IF;
639
640 IF l_serial_number_control_code <> 1
641 AND l_serial_number_control_code <> 6 THEN
642 IF (l_debug = 1) THEN
643 mydebug('Check if the serial numbers are already allocated or not', l_api_name);
644 END IF;
645
646 BEGIN
647 IF l_lot_control_code = 1 THEN -- not lot controlled
648 SELECT msnt.transaction_temp_id
649 INTO l_serial_temp_id
650 FROM mtl_serial_numbers_temp msnt
651 WHERE msnt.transaction_temp_id = l_transaction_temp_id
652 AND ROWNUM < 2;
653 ELSE -- lot controlled
654 --Bug 9473783 Modified the where clause
655 SELECT msnt.transaction_temp_id
656 INTO l_serial_temp_id
657 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt, mtl_material_transactions_temp mmtt
658 WHERE ((msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, -1)
659 AND mtlt.transaction_temp_id = l_transaction_temp_id)
660 OR (msnt.transaction_temp_id = l_transaction_temp_id
661 AND mmtt.transaction_temp_id = l_transaction_temp_id AND mmtt.lot_number IS NOT NULL ))
662 AND ROWNUM < 2;
663 END IF;
664
665 l_sl_alloc_flag := 'Y';
666 EXCEPTION
667 WHEN NO_DATA_FOUND THEN
668 l_sl_alloc_flag := 'N';
669 END;
670 END IF;
671
672 -- Start bug 4434111
673 IF (NVL(l_serial_alloc_flag,'N') = 'N' )
674 AND l_sl_alloc_flag = 'Y'
675 THEN
676 --{
677 l_sl_alloc_flag := 'N';
678 l_user_id := fnd_global.user_id;
679
680 IF l_lot_control_code = 1 THEN
681 --{
682 -- Only serial controlled item
683 --
684 OPEN c_get_serials(l_transaction_temp_id);
685 LOOP
686 --{
687 EXIT WHEN c_get_serials%NOTFOUND;
688 FETCH c_get_serials INTO
689 l_fm_serial_number, l_to_serial_number;
690 --
691 IF (l_debug = 1) THEN
692 mydebug('Not a lot controlled item, just serial', l_api_name);
693 mydebug('From Serial Number = ' || l_fm_serial_number, l_api_name);
694 mydebug('To Serial Number = ' || l_to_serial_number, l_api_name);
695 mydebug('Inventory Item ID = ' || l_inventory_item_id, l_api_name);
696 mydebug('Org ID = ' || l_org_id, l_api_name);
697 END IF;
698 --
699 UPDATE mtl_serial_numbers
700 SET group_mark_id = NULL,
701 last_update_date = Sysdate,
702 last_updated_by = l_user_id
703 WHERE serial_number >= l_fm_serial_number
704 AND serial_number <= l_to_serial_number
705 AND inventory_item_id = l_inventory_item_id
706 AND current_organization_id = l_org_id;
707
708 IF (l_debug = 1) THEN
709 mydebug('Rows updated ' || sql%rowcount, l_api_name);
710 END IF;
711 --}
712 END LOOP;
713 CLOSE c_get_serials;
714 --
715 DELETE FROM mtl_serial_numbers_temp
716 WHERE transaction_temp_id = l_transaction_temp_id;
717 --
718 IF (l_debug = 1) THEN
719 mydebug('Rows deleted ' || sql%rowcount, l_api_name);
720 END IF;
721 --}
722 ELSE
723 --{
724 -- Lot and serial controlled item
725 --
726 OPEN c_get_serialLots(l_transaction_temp_id);
727 LOOP
728 --{
729 EXIT WHEN c_get_serialLots%NOTFOUND;
730 FETCH c_get_serialLots INTO
731 l_fm_serial_number, l_to_serial_number;
732 --
733 IF (l_debug = 1) THEN
734 mydebug('Lot and serial controlled item', l_api_name);
735 mydebug('From Serial Number = ' || l_fm_serial_number, l_api_name);
736 mydebug('To Serial Number = ' || l_to_serial_number, l_api_name);
737 mydebug('Inventory Item ID = ' || l_inventory_item_id, l_api_name);
738 mydebug('Org ID = ' || l_org_id, l_api_name);
739 END IF;
740 --
741 UPDATE mtl_serial_numbers
742 SET group_mark_id = NULL,
743 last_update_date = Sysdate,
744 last_updated_by = l_user_id
745 WHERE serial_number >= l_fm_serial_number
746 AND serial_number <= l_to_serial_number
747 AND inventory_item_id = l_inventory_item_id
748 AND current_organization_id = l_org_id;
749
750 IF (l_debug = 1) THEN
751 mydebug('Rows updated ' || sql%rowcount, l_api_name);
752 END IF;
753 --}
754 END LOOP;
755 CLOSE c_get_serialLots;
756 --
757 DELETE FROM mtl_serial_numbers_temp
758 WHERE transaction_temp_id IN
759 (
760 SELECT serial_transaction_temp_id
761 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
762 WHERE msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
763 AND mtlt.transaction_temp_id = l_transaction_temp_id
764 );
765 --
766 IF (l_debug = 1) THEN
767 mydebug('Rows deleted ' || sql%rowcount, l_api_name);
768 END IF;
769 --}
770 END IF;
771 --}
772 END IF;
773 -- End bug 4434111
774
775 --bug 2755138. Consolidate mtlt if there are multi record for the same lot number
776 -- if it is not exact lpn match and item is lot controlled
777 -- then check to see if need to consolidate of mtlt table in case there are multi record for the same lot number,
778 -- we need to consolidate them before continue
779
780 IF (l_lot_control_code > 1) THEN
781
782 open c_lot_csr(l_transaction_temp_id);
783 l_old_lot := null;
784 loop
785 fetch c_lot_csr into l_lot;
786 exit when c_lot_csr%NOTFOUND;
787
788 IF (l_debug = 1) THEN
789 mydebug('consolidate mtlt 00 : lot number:'|| l_lot, l_api_name);
790 END IF;
791
792 if l_lot <> nvl(l_old_lot, '-999') then
793 begin
794 select count(*),sum(primary_quantity),sum(transaction_quantity)
795 into l_same_lot_rec_cnt, l_sum_same_lot_pri_qty, l_sum_same_lot_tra_qty
796 from mtl_transaction_lots_temp
797 where transaction_temp_id = l_transaction_temp_id
798 and lot_number = l_lot;
799 exception
800 when NO_DATA_FOUND then
801 IF (l_debug = 1) THEN
802 mydebug('consolidate mtlt 11: there is no mtlt record for the lot:'|| l_lot, l_api_name);
803 END IF;
804 l_same_lot_rec_cnt :=0;
805 when others then
806 IF (l_debug = 1) THEN
807 mydebug(' consolidate mtlt 11: there is an exception', l_api_name);
808 END IF;
809 l_same_lot_rec_cnt :=0;
810 end;
811
812 IF (l_debug = 1) THEN
813 mydebug('consolidate mtlt 22 : record count for the lot number:'|| l_lot||' is :' ||l_same_lot_rec_cnt, l_api_name);
814 END IF;
815
816 if nvl(l_same_lot_rec_cnt,0) > 1 then
817 select *
818 into l_mtlt_rec
819 from mtl_transaction_lots_temp
820 where transaction_temp_id = l_transaction_temp_id
821 and lot_number = l_lot
822 and rownum = 1;
823
824 IF (l_serial_number_control_code >1 AND l_serial_number_control_code <>6) -- lot serial controlled
825 AND l_sl_alloc_flag = 'Y' -- and allocate to serial ON
826
827 THEN
828 IF (l_debug = 1) THEN
829 mydebug('consolidate mtlt 33: lot/serial controlled and allocate to serial ON', l_api_name);
830 END IF;
831 SELECT mtl_material_transactions_s.NEXTVAL
832 INTO l_new_serial_temp_id
833 FROM dual;
834 l_mtlt_rec.serial_transaction_temp_id := l_new_serial_temp_id;
835 l_sum_same_lot_pri_qty := 0;
836 l_sum_same_lot_tra_qty := 0;
837 open c_same_lot_csr(l_transaction_temp_id, l_lot);
838 loop
839 fetch c_same_lot_csr into l_same_lot_pri_qty, l_same_lot_tra_qty, l_serial_temp_id;
840 exit when c_same_lot_csr%NOTFOUND;
841 l_sum_same_lot_pri_qty := l_sum_same_lot_pri_qty + l_same_lot_pri_qty;
842 l_sum_same_lot_tra_qty := l_sum_same_lot_tra_qty + l_same_lot_tra_qty;
843
844 UPDATE mtl_serial_numbers
845 SET group_mark_id = l_new_serial_temp_id
846 WHERE current_organization_id = l_org_id
847 AND inventory_item_id = l_inventory_item_id
848 AND serial_number in (select fm_serial_number
849 from mtl_serial_numbers_temp
850 where transaction_temp_id = l_serial_temp_id);
851 UPDATE mtl_serial_numbers_temp
852 SET transaction_temp_id = l_new_serial_temp_id
853 WHERE transaction_temp_id = l_serial_temp_id;
854
855 end loop;
856 close c_same_lot_csr;
857 ELSIF (l_serial_number_control_code >1 AND l_serial_number_control_code <>6) -- lot serial controlled
858 AND l_sl_alloc_flag = 'N' -- and allocate to serial OFF
859 THEN
860 IF (l_debug = 1) THEN
861 mydebug('consolidate mtlt 44: lot controlled only OR lot/serial controlled but Allocate to serial OFF', l_api_name);
862 END IF;
863 SELECT mtl_material_transactions_s.NEXTVAL
864 INTO l_new_serial_temp_id
865 FROM dual;
866 l_mtlt_rec.serial_transaction_temp_id := l_new_serial_temp_id;
867 begin
868 select sum(primary_quantity), sum(transaction_quantity)
869 into l_sum_same_lot_pri_qty, l_sum_same_lot_tra_qty
870 from mtl_transaction_lots_temp
871 where transaction_temp_id = l_transaction_temp_id
872 and lot_number = l_lot;
873 exception
874 when NO_DATA_FOUND then
875 IF (l_debug = 1) THEN
876 mydebug(' consolidate mtlt 50: there is no mtlt record for lot:'||l_lot, l_api_name);
877 END IF;
878 when others then
879 IF (l_debug = 1) THEN
880 mydebug(' consolidate mtlt 50: there is an exception', l_api_name);
881 END IF;
882 end;
883 END IF; -- if l_serial_conde > 1 adn l_serial_code <> 6 and p_sn_allocated_flag = 'Y'
884 l_mtlt_rec.transaction_temp_id := l_transaction_temp_id;
885 l_mtlt_rec.primary_quantity := l_sum_same_lot_pri_qty;
886 l_mtlt_rec.transaction_quantity := l_sum_same_lot_tra_qty;
887 IF (l_debug = 1) THEN
888 mydebug(' consolidate mtlt 55: Inserting into MTLT', l_api_name);
889 mydebug(' consolidate mtlt 55: primary_quantity = ' || l_mtlt_rec.primary_quantity, l_api_name);
890 mydebug(' consolidate mtlt 55 :transaction_quantity = ' || l_mtlt_rec.transaction_quantity, l_api_name);
891 mydebug(' consolidate mtlt 55 :serial_transaction_temp_id = ' || l_mtlt_rec.serial_transaction_temp_id, l_api_name);
892 END IF;
893 delete mtl_transaction_lots_temp
894 where transaction_temp_id = l_transaction_temp_id
895 and lot_number = l_lot;
896 -- Insert new line into MTLT
897 inv_rcv_common_apis.insert_mtlt(l_mtlt_rec);
898
899 begin
900 select count(*)
901 into l_same_lot_rec_cnt
902 from mtl_transaction_lots_temp
903 where transaction_temp_id = l_transaction_temp_id
904 and lot_number = l_lot;
905 exception
906 when NO_DATA_FOUND then
907 IF (l_debug = 1) THEN
908 mydebug(' consolidate mtlt 66: there is no mtlt record for mmtt', l_api_name);
909 END IF;
910 l_same_lot_rec_cnt :=0;
911 when others then
912 IF (l_debug = 1) THEN
913 mydebug(' consolidate mtlt 77: there is an exception', l_api_name);
914 END IF;
915 l_same_lot_rec_cnt :=0;
916 end;
917
918 IF (l_debug = 1) THEN
919 mydebug('consolidate mtlt 66 : record count for lot number:'||l_lot||' is: '|| l_same_lot_rec_cnt, l_api_name);
920 END IF;
921
922 end if; -- if l_same_lot_rec_cnt > 1
923 end if; -- if l_lot <> nvl(l_old_lot, '-999');
924 l_old_lot := l_lot;
925 end loop;
926 close c_lot_csr;
927
928 END IF; -- if l_lot_control_code > 1
929
930 --end of bug 2755138.
931
932 IF (l_allocated_lpn_id IS NOT NULL) THEN
933 IF (l_debug = 1) THEN
934 mydebug('Get the allocated LPN info', l_api_name);
935 END IF;
936
937 SELECT wlpn1.license_plate_number
938 , wlpn2.license_plate_number
939 INTO l_allocated_lpn_name
940 , l_allocated_outermost_lpn_name
941 FROM wms_license_plate_numbers wlpn1, wms_license_plate_numbers wlpn2
942 WHERE wlpn1.lpn_id = l_allocated_lpn_id
943 AND wlpn1.outermost_lpn_id = wlpn2.lpn_id;
944 END IF;
945
946 IF (l_cartonization_id IS NOT NULL) THEN
947
948 IF (l_debug = 1) THEN
949 mydebug('Get carton information', l_api_name);
950 END IF;
951
952 -- Bug : 6682436 Start
953 -- To handle the case for pick slip cartonization
954 SELECT license_plate_number
955 INTO l_carton_name
956 FROM wms_license_plate_numbers
957 WHERE lpn_id = l_cartonization_id;
958
959 BEGIN
960 SELECT l.license_plate_number lpn
961 , l.inventory_item_id itemid
962 , k.concatenated_segments item
963 INTO l_carton_name
964 , l_carton_item_id
965 , l_carton_item_name
966 FROM wms_license_plate_numbers l, mtl_system_items_vl k /* Bug 5581528 */
967 WHERE l.lpn_id = l_cartonization_id
968 AND l.inventory_item_id = k.inventory_item_id
969 AND l.organization_id = k.organization_id;
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 NULL;
974 END;
975 -- Bug : 6682436 End
976
977 IF (l_debug = 1) THEN
978 mydebug('Check to see how many unloaded tasks are there for this carton', l_api_name);
979 END IF;
980 wms_task_dispatch_gen.check_carton(
981 p_carton_id => l_cartonization_id
982 , p_org_id => l_org_id
983 , x_nbr_tasks => l_nbr_tasks
984 , x_return_status => x_return_status
985 , x_msg_count => x_mesg_count
986 , x_msg_data => x_error_mesg
987 );
988
989 IF x_return_status <> fnd_api.g_ret_sts_success THEN
990 x_error_code := 3;
991 RAISE fnd_api.g_exc_unexpected_error;
992 END IF;
993 END IF;
994
995 -- get the cluster key for the cluster picking ---patchset J APL cluster picking
996 IF p_cartons_list is not null THEN
997 IF (l_debug = 1) THEN
998 mydebug('Start query the cluster key', l_api_name);
999 END IF;
1000 l_delivery_id := null;
1001 IF l_transaction_action_id = 28 THEN -- SO/IO
1002 SELECT
1003 wda.delivery_id,mtrl.carton_grouping_id
1004 INTO l_delivery_id,l_carton_grouping_id
1005 FROM
1006 mtl_material_transactions_temp mmtt
1007 , mtl_txn_request_lines mtrl
1008 , wsh_delivery_details wdd
1009 , wsh_delivery_assignments_v wda
1010 WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1011 AND mmtt.move_order_line_id = mtrl.line_id
1012 AND wdd.move_order_line_id = mtrl.line_id
1013 AND wda.delivery_detail_id = wdd.delivery_detail_id
1014 AND wdd.released_status='S';
1015 ELSE -- WIP picking or others
1016 SELECT
1017 mtrl.carton_grouping_id
1018 INTO l_carton_grouping_id
1019 FROM
1020 mtl_material_transactions_temp mmtt
1021 , mtl_txn_request_lines mtrl
1022 WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1023 AND mmtt.move_order_line_id = mtrl.line_id;
1024 END IF;
1025 IF l_delivery_id is not null THEN
1026 l_cluster_key := l_delivery_id || 'D';
1027 ELSE
1028 l_cluster_key := l_carton_grouping_id || 'C';
1029 END IF;
1030 IF (l_debug = 1) THEN
1031 mydebug('Cluster key :'||l_cluster_key, l_api_name);
1032 END IF;
1033 END IF;
1034
1035
1036 --Added for Case Picking Project start
1037 IF p_is_manifest_pick ='Y' THEN
1038 l_delivery_id := null;
1039 l_carton_grouping_id:=null;
1040 IF (l_debug = 1) THEN
1041 mydebug('l_transaction_temp_id:'||l_transaction_temp_id, l_api_name);
1042 END IF;
1043 IF l_transaction_action_id = 28 THEN -- SO/IO
1044 BEGIN
1045 SELECT wda.delivery_id,mtrl.carton_grouping_id
1046 INTO l_delivery_id,l_carton_grouping_id
1047 FROM mtl_material_transactions_temp mmtt
1048 , mtl_txn_request_lines mtrl
1049 , wsh_delivery_details wdd
1050 , wsh_delivery_assignments_v wda
1051 WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1052 AND mmtt.move_order_line_id = mtrl.line_id
1053 AND wdd.move_order_line_id = mtrl.line_id
1054 AND wda.delivery_detail_id = wdd.delivery_detail_id
1055 AND wdd.released_status='S';
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 IF (l_debug = 1) THEN
1059 mydebug('In exception block of OTHERS', l_api_name);
1060 END IF;
1061 SELECT mtrl.carton_grouping_id
1062 INTO l_carton_grouping_id
1063 FROM mtl_material_transactions_temp mmtt
1064 , mtl_txn_request_lines mtrl
1065 WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1066 AND mmtt.move_order_line_id = mtrl.line_id;
1067 END;
1068
1069 IF l_delivery_id is not null THEN
1070 l_cluster_key := l_delivery_id || 'D';
1071 ELSIF l_carton_grouping_id is not null THEN
1072 l_cluster_key := l_carton_grouping_id || 'C';
1073 ELSE
1074 l_cluster_key :=NULL;
1075 END IF;
1076
1077 IF (l_debug = 1) THEN
1078 mydebug('Cluster key :'||l_cluster_key, l_api_name);
1079 mydebug('l_delivery_id :'||l_delivery_id, l_api_name);
1080 mydebug('l_carton_grouping_id :'||l_carton_grouping_id, l_api_name);
1081 END IF;
1082 END IF;
1083 END IF;
1084 --Added for Case Picking Project end
1085
1086
1087 --OVPK Start 2
1088 IF (l_debug = 1) THEN
1089 mydebug('OVPK:WMSPLPDB:Checking if it is a Bulk Picked task...',l_api_name);
1090 mydebug('OVPK:WMSPLPDB:l_transaction_temp_id = ' || l_transaction_temp_id, l_api_name);
1091 mydebug('OVPK:WMSPLPDB:l_is_bulk_picked_task = '||l_is_bulk_picked_task, l_api_name);
1092 END IF;
1093
1094 --Check if it is a bulk picked task
1095 /*
1096 SELECT DECODE (parent_line_id, transaction_temp_id, 'Y', 'N')
1097 INTO l_is_bulk_picked_task
1098 FROM mtl_material_transactions_temp mmtt
1099 WHERE transaction_temp_id = l_transaction_temp_id;
1100 */
1101
1102 -- If Yes then set l_check_overpick_passed to 'Y'
1103 IF (l_is_bulk_picked_task = 'Y') THEN
1104 l_check_overpick_passed := 'Y';
1105
1106 IF (l_debug = 1) THEN
1107 mydebug('OVPK:WMSPLPDB:It IS a bulk picked task', l_api_name);
1108 mydebug('OVPK:WMSPLPDB:NOT calling any OVPK code', l_api_name);
1109 mydebug('OVPK:WMSPLPDB:l_check_overpick_passed = '||l_check_overpick_passed, l_api_name);
1110 END IF;
1111
1112 ELSE
1113 -- Else make some minimal checks to allow/disallow overpicking
1114 IF (l_debug = 1) THEN
1115 mydebug('OVPK:WMSPLPDB:It is NOT a bulk picked task', l_api_name);
1116 mydebug('OVPK:WMSPLPDB:Need to make some minimal checks to allow/disallow overpicking', l_api_name);
1117 mydebug('OVPK:WMSPLPDB:l_transaction_temp_id = ' || l_transaction_temp_id, l_api_name);
1118 END IF;
1119 /*
1120 inv_replenish_detail_pub.check_overpick_minimal(
1121 p_transaction_temp_id => l_transaction_temp_id
1122 , x_check_overpick_passed => l_check_overpick_passed
1123 , x_return_status => x_return_status
1124 , x_msg_count => x_mesg_count
1125 , x_msg_data => x_error_mesg
1126 );
1127 */
1128 --Resolve move_order_type from l_transaction_temp_id
1129 SELECT mtrh.move_order_type
1130 INTO l_move_order_type
1131 FROM mtl_txn_request_headers mtrh,
1132 mtl_txn_request_lines mtrl,
1133 mtl_material_transactions_temp mmtt
1134 WHERE mmtt.move_order_line_id = mtrl.line_id
1135 AND mtrl.header_id = mtrh.header_id
1136 AND mmtt.transaction_temp_id = l_transaction_temp_id;
1137
1138 IF l_debug = 1 THEN
1139 mydebug('OVPK: l_org_id = '||l_org_id, l_api_name);
1140 mydebug('OVPK: l_move_order_type = '||l_move_order_type, l_api_name);
1141 END IF;
1142
1143 --If the MO is of type replenishment / requisition
1144 IF (l_move_order_type IN
1145 (inv_globals.g_move_order_replenishment,
1146 inv_globals.g_move_order_requisition)
1147 ) THEN
1148
1149 SELECT OVPK_TRANSFER_ORDERS_ENABLED
1150 INTO l_temp
1151 FROM mtl_parameters
1152 WHERE organization_id = l_org_id;
1153 l_check_overpick_passed := NVL(l_temp, 'Y');
1154
1155 IF l_debug = 1 THEN
1156 mydebug('OVPK: l_temp for replenishment/requisition MO = '||l_temp, l_api_name);
1157 mydebug('OVPK: l_check_overpick_passed for replenishment/requisition MO = '||l_check_overpick_passed, l_api_name);
1158 END IF;
1159
1160 ELSIF (l_move_order_type = inv_globals.g_move_order_mfg_pick) THEN
1161
1162 SELECT wip_overpick_enabled
1163 INTO l_temp
1164 FROM mtl_parameters
1165 WHERE organization_id = l_org_id;
1166 l_check_overpick_passed := NVL(l_temp, 'N');
1167
1168 IF l_debug = 1 THEN
1169 mydebug('OVPK: l_temp for WIP MO = '||l_temp, l_api_name);
1170 mydebug('OVPK: l_check_overpick_passed for WIP MO = '||l_check_overpick_passed, l_api_name);
1171 END IF;
1172
1173 ELSIF (l_move_order_type = inv_globals.g_move_order_pick_wave) THEN
1174
1175 l_check_overpick_passed := NVL(fnd_profile.VALUE('WSH_OVERPICK_ENABLED'), 'N');
1176
1177 IF l_debug = 1 THEN
1178 mydebug('OVPK: l_check_overpick_passed for PickWave MO = '||l_check_overpick_passed, l_api_name);
1179 END IF;
1180
1181 END IF;
1182
1183 END IF; -- Not Bulk Picked task
1184
1185
1186 --OVPK End 2
1187 IF (l_debug = 1) THEN
1188 mydebug('get the item type for ucc128', l_api_name);
1189 END IF;
1190
1191 BEGIN
1192 SELECT 'C'
1193 INTO l_item_type
1194 FROM mtl_material_transactions_temp mmtt,
1195 mtl_cross_references mcr
1196 WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1197 AND mmtt.inventory_item_id = mcr.inventory_item_id
1198 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1199 AND (mcr.organization_id = mmtt.organization_id
1200 OR mcr.org_independent_flag = 'Y')
1201 AND rownum = 1;
1202
1203 IF (l_debug = 1) THEN
1204 mydebug('After the Query for item_type :'||l_item_type,l_api_name);
1205 END IF;
1206
1207 EXCEPTION
1208 WHEN no_data_found THEN
1209 l_item_type := NULL;
1210 END;
1211
1212 --Start Bug 6682436
1213 IF WMS_CONTROL.G_CURRENT_RELEASE_LEVEL >= 120001 THEN
1214 IF (l_debug = 1) THEN
1215 mydebug('Fetching the values of user task attributes', l_api_name);
1216 END IF;
1217
1218 BEGIN
1219 SELECT wutta.honor_case_pick_flag, pgvl.template_name into l_honor_case_pick_flag, l_template_name
1220 FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta , wms_page_templates_tl pgtl, WMS_PAGE_TEMPLATES_VL pgvl
1221 WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1222 AND mmtt.standard_operation_id = wutta.user_task_type_id
1223 AND mmtt.organization_id = wutta.organization_id
1224 AND pgtl.template_id = wutta.pick_load_page_template_id
1225 AND pgtl.template_id = pgvl.template_id
1226 AND pgtl.language = userenv('LANG');
1227 EXCEPTION
1228 WHEN NO_DATA_FOUND THEN
1229 l_honor_case_pick_flag := 'N';
1230 l_template_name := '-999';
1231 END;
1232
1233 -- Added for 14699845 (Flexible Lot Allocation)
1234 BEGIN
1235 SELECT 'Y'
1236 INTO l_allocate_lot_flag
1237 FROM DUAL
1238 WHERE EXISTS (SELECT 1
1239 FROM mtl_transaction_lots_temp mtlt
1240 WHERE mtlt.transaction_temp_id = l_transaction_temp_id);
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 l_allocate_lot_flag := 'N';
1244 END;
1245 -- Added for 14699845 (Flexible Lot Allocation)
1246 IF (l_debug = 1) THEN
1247 mydebug('Before opening the ref cursor for WMS_CONTROL.G_CURRENT_RELEASE_LEVEL' || WMS_CONTROL.G_CURRENT_RELEASE_LEVEL, l_api_name);
1248 mydebug('l_honor_case_pick_flag ' || l_honor_case_pick_flag, l_api_name);
1249 mydebug('l_template_name ' || l_template_name, l_api_name);
1250 mydebug('l_allocate_lot_flag ' || l_allocate_lot_flag, l_api_name);-- Added for 14699845 (Flexible Lot Allocation)
1251 END IF;
1252
1253 OPEN x_task_info FOR
1254 SELECT mmtt.cartonization_id
1255 , mmtt.container_item_id
1256 , mmtt.inventory_item_id
1257 , mmtt.lot_number
1258 , mmtt.revision
1259 , decode(fulfillment_base, 'S', decode(mmtt.transaction_uom, msik.primary_uom_code, mmtt.secondary_transaction_quantity
1260 , msik.secondary_uom_code, mmtt.secondary_transaction_quantity
1261 , mmtt.transaction_quantity)
1262 ,mmtt.transaction_quantity) -- muom:sk
1263 -- , mmtt.transaction_quantity -- muom:sk
1264 , decode (mmtt.fulfillment_base,'S', decode(mmtt.transaction_uom, msik.primary_uom_code, mmtt.secondary_uom_code
1265 , msik.secondary_uom_code, mmtt.secondary_uom_code
1266 , mmtt.transaction_uom)
1267 , mmtt.transaction_uom) --muom:sk
1268 -- , mmtt.transaction_uom --muom:sk
1269 , mmtt.locator_id locator_id
1270 , mmtt.subinventory_code
1271 , inv_project.get_locsegs(mmtt.locator_id, mmtt.organization_id) loc
1272 -- 11
1273 , msik.concatenated_segments item
1274 , mmtt.transaction_temp_id
1275 , mmtt.transfer_subinventory
1276 , mmtt.transfer_to_location
1277 , NVL(msik.lot_control_code, 1) lot_code
1278 , NVL(msik.serial_number_control_code, 1) serial_code
1279 , mmtt.transaction_type_id
1280 , NVL(msik.restrict_subinventories_code, 2) subrest
1281 , NVL(msik.restrict_locators_code, 2) locrest
1282 , NVL(msik.location_control_code, 1) loccode
1283 --21
1284 , msik.primary_uom_code
1285 , NVL(msik.allowed_units_lookup_code, 2) allunits
1286 , NVL(revision_qty_control_code, 1) revcode
1287 , wdt.task_id
1288 , mmtt.cost_group_id
1289 , mmtt.transaction_header_id
1290 , mp.allocate_serial_flag
1291 , mtrl.txn_source_id
1292 , mmtt.wip_entity_type
1293 , wdt.task_type
1294 --31
1295 , mmtt.transaction_source_type_id
1296 , NVL(mmtt.allocated_lpn_id, 0)
1297 , mmtt.pick_slip_number
1298 , inv_project.get_project_id
1299 , inv_project.get_task_id
1300 , inv_project.get_project_number
1301 , inv_project.get_task_number
1302 , mmtt.transaction_action_id
1303 , wdt.device_request_id
1304 , l_sl_alloc_flag
1305 --41
1306 , l_serial_temp_id
1307 , l_allocated_lpn_name
1308 , l_allocated_outermost_lpn_name
1309 , l_carton_name
1310 , l_carton_item_id
1311 , l_carton_item_name
1312 , l_nbr_tasks
1313 , l_cluster_key cluster_key -- patchset J APL changed cluster id to cluster key
1314 , mmtt.parent_line_id
1315 , msi.lpn_controlled_flag
1316 --51
1317 , msik.tracking_quantity_ind
1318 , msik.ont_pricing_qty_source
1319 , msik.secondary_default_ind
1320 , msik.secondary_uom_code
1321 , msik.dual_uom_deviation_high
1322 , msik.dual_uom_deviation_low
1323 , mmtt.trx_source_line_id
1324 , l_check_overpick_passed --OVPK
1325 , Sysdate
1326 , mp.negative_inv_receipt_code
1327 --61
1328 , l_item_type
1329 , msik.description
1330 , inv_ui_item_lovs.get_conversion_rate(
1331 decode(fulfillment_base, 'S', decode(mmtt.transaction_uom, msik.primary_uom_code, mmtt.SECONDARY_UOM_CODE
1332 , msik.secondary_uom_code, mmtt.SECONDARY_UOM_CODE
1333 , mmtt.transaction_uom)
1334 , mmtt.transaction_uom),
1335 mmtt.organization_id,
1336 mmtt.inventory_item_id,
1337 mmtt.fulfillment_base) -- MUOM:SK
1338 -- Bug# 4141928
1339 -- For OPM convegence
1340 -- Fetching the sec txn qty and the additional Item attributes
1341 --64
1342 , mmtt.secondary_uom_code
1343 , mmtt.secondary_transaction_quantity
1344 , nvl(msik.lot_divisible_flag,'Y')
1345 /* Added for LMS project */
1346 , wdt.user_task_type
1347 , mmtt.operation_plan_id
1348 /* end for LMS project */
1349
1350 , l_honor_case_pick_flag --69
1351 , l_template_name --70
1352 , l_allocate_lot_flag --71 -- Added for 14699845 (Flexible Lot Allocation)
1353 , NVL(mmtt.fulfillment_base, 'P') -- MUOM:SK (72)
1354 FROM wms_dispatched_tasks wdt
1355 , mtl_material_transactions_temp mmtt
1356 , mtl_system_items_vl msik /* Bug 5581528 */
1357 , mtl_parameters mp
1358 , mtl_txn_request_lines mtrl
1359 , mtl_secondary_inventories msi
1360 WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1361 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
1362 AND mp.organization_id = wdt.organization_id
1363 AND mmtt.organization_id = msik.organization_id
1364 AND mmtt.inventory_item_id = msik.inventory_item_id
1365 AND mmtt.move_order_line_id = mtrl.line_id (+)
1366 AND mmtt.subinventory_code = msi.secondary_inventory_name
1367 AND mmtt.organization_id = msi.organization_id;
1368
1369 IF (l_debug = 1) THEN
1370 mydebug('After opening the ref cursor', l_api_name);
1371 END IF;
1372 --End Bug 6682436
1373 ELSE
1374 IF (l_debug = 1) THEN
1375 mydebug('Before opening the ref cursor', l_api_name);
1376 END IF;
1377
1378 OPEN x_task_info FOR
1379 SELECT mmtt.cartonization_id
1380 , mmtt.container_item_id
1381 , mmtt.inventory_item_id
1382 , mmtt.lot_number
1383 , mmtt.revision
1384 /* if txn uom is either pri/ sec then take secondary transaction qty. if any thing else take txn qty as is.
1385 this is for fulfillment_base = 'S'. for 'P' it should txn qty direclty*/
1386 , decode(fulfillment_base, 'S', decode(mmtt.transaction_uom, msik.primary_uom_code, mmtt.secondary_transaction_quantity
1387 , msik.secondary_uom_code, mmtt.secondary_transaction_quantity
1388 , mmtt.transaction_quantity)
1389 ,mmtt.transaction_quantity) -- muom:sk
1390 -- , mmtt.transaction_quantity -- muom:sk
1391 , decode (mmtt.fulfillment_base,'S', decode(mmtt.transaction_uom, msik.primary_uom_code, mmtt.secondary_uom_code
1392 , msik.secondary_uom_code, mmtt.secondary_uom_code
1393 , mmtt.transaction_uom)
1394 , mmtt.transaction_uom) --muom:sk
1395 -- , mmtt.transaction_uom --muom:sk
1396 , mmtt.locator_id locator_id
1397 , mmtt.subinventory_code
1398 , inv_project.get_locsegs(mmtt.locator_id, mmtt.organization_id) loc
1399 -- 11
1400 , msik.concatenated_segments item
1401 , mmtt.transaction_temp_id
1402 , mmtt.transfer_subinventory
1403 , mmtt.transfer_to_location
1404 , NVL(msik.lot_control_code, 1) lot_code
1405 , NVL(msik.serial_number_control_code, 1) serial_code
1406 , mmtt.transaction_type_id
1407 , NVL(msik.restrict_subinventories_code, 2) subrest
1408 , NVL(msik.restrict_locators_code, 2) locrest
1409 , NVL(msik.location_control_code, 1) loccode
1410 --21
1411 , msik.primary_uom_code
1412 , NVL(msik.allowed_units_lookup_code, 2) allunits
1413 , NVL(revision_qty_control_code, 1) revcode
1414 , wdt.task_id
1415 , mmtt.cost_group_id
1416 , mmtt.transaction_header_id
1417 , mp.allocate_serial_flag
1418 , mtrl.txn_source_id
1419 , mmtt.wip_entity_type
1420 , wdt.task_type
1421 --31
1422 , mmtt.transaction_source_type_id
1423 , NVL(mmtt.allocated_lpn_id, 0)
1424 , mmtt.pick_slip_number
1425 , inv_project.get_project_id
1426 , inv_project.get_task_id
1427 , inv_project.get_project_number
1428 , inv_project.get_task_number
1429 , mmtt.transaction_action_id
1430 , wdt.device_request_id
1431 , l_sl_alloc_flag
1432 --41
1433 , l_serial_temp_id
1434 , l_allocated_lpn_name
1435 , l_allocated_outermost_lpn_name
1436 , l_carton_name
1437 , l_carton_item_id
1438 , l_carton_item_name
1439 , l_nbr_tasks
1440 , l_cluster_key cluster_key -- patchset J APL changed cluster id to cluster key
1441 , mmtt.parent_line_id
1442 , msi.lpn_controlled_flag
1443 --51
1444 , msik.tracking_quantity_ind
1445 , msik.ont_pricing_qty_source
1446 , msik.secondary_default_ind
1447 , msik.secondary_uom_code
1448 , msik.dual_uom_deviation_high
1449 , msik.dual_uom_deviation_low
1450 , mmtt.trx_source_line_id
1451 , l_check_overpick_passed --OVPK
1452 , Sysdate
1453 , mp.negative_inv_receipt_code
1454 --61
1455 , l_item_type
1456 , msik.description
1457 , inv_ui_item_lovs.get_conversion_rate(
1458 decode(fulfillment_base, 'S', decode(mmtt.transaction_uom, msik.primary_uom_code, mmtt.SECONDARY_UOM_CODE
1459 , msik.secondary_uom_code, mmtt.SECONDARY_UOM_CODE
1460 , mmtt.transaction_uom)
1461 , mmtt.transaction_uom ),
1462 mmtt.organization_id,
1463 mmtt.inventory_item_id,
1464 mmtt.fulfillment_base) -- MUOM:SK
1465 -- Bug# 4141928
1466 -- For OPM convegence
1467 -- Fetching the sec txn qty and the additional Item attributes
1468 --64
1469 , mmtt.secondary_uom_code
1470 , mmtt.secondary_transaction_quantity
1471 , nvl(msik.lot_divisible_flag,'Y')
1472 /* Added for LMS project */
1473 , wdt.user_task_type
1474 , mmtt.operation_plan_id
1475 /* end for LMS project */
1476 FROM wms_dispatched_tasks wdt
1477 , mtl_material_transactions_temp mmtt
1478 , mtl_system_items_vl msik /* Bug 5581528 */
1479 , mtl_parameters mp
1480 , mtl_txn_request_lines mtrl
1481 , mtl_secondary_inventories msi
1482 WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1483 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
1484 AND mp.organization_id = wdt.organization_id
1485 AND mmtt.organization_id = msik.organization_id
1486 AND mmtt.inventory_item_id = msik.inventory_item_id
1487 AND mmtt.move_order_line_id = mtrl.line_id (+)
1488 AND mmtt.subinventory_code = msi.secondary_inventory_name
1489 AND mmtt.organization_id = msi.organization_id;
1490
1491 IF (l_debug = 1) THEN
1492 mydebug('After opening the ref cursor', l_api_name);
1493 END IF;
1494 END IF;
1495 EXCEPTION
1496 WHEN fnd_api.g_exc_error THEN
1497 x_return_status := fnd_api.g_ret_sts_error;
1498 ROLLBACK TO next_task_inquiry;
1499 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_mesg_count, p_data => x_error_mesg);
1500 IF (l_debug = 1) THEN
1501 mydebug('Error ! SQL Code : '|| SQLCODE, l_api_name);
1502 END IF;
1503 WHEN fnd_api.g_exc_unexpected_error THEN
1504 x_return_status := fnd_api.g_ret_sts_unexp_error;
1505 ROLLBACK TO next_task_inquiry;
1506 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_mesg_count, p_data => x_error_mesg);
1507 IF (l_debug = 1) THEN
1508 mydebug('Unexpected Error ! SQL Code : '|| SQLCODE, l_api_name);
1509 END IF;
1510 WHEN OTHERS THEN
1511 x_return_status := fnd_api.g_ret_sts_unexp_error;
1512 ROLLBACK TO next_task_inquiry;
1513
1514 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1515 fnd_msg_pub.add_exc_msg('WMS_PICKING_PKG', 'GET_NEXT_TASK_INFO');
1516 END IF;
1517
1518 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_mesg_count, p_data => x_error_mesg);
1519 IF (l_debug = 1) THEN
1520 mydebug('Other Error ! SQL Code : '|| SQLCODE, l_api_name);
1521 END IF;
1522 END get_next_task_info;
1523
1524 --
1525 -- Name
1526 -- PROCEDURE HANDLE_BULK_PICKING
1527 --
1528 -- Purpose
1529 -- If the LPN has any Bulk Picked Line, then the Parent MMTT record is deleted and the
1530 -- Txn Header ID, Transfer LPN ID and LPN ID of the Parent MMTT record are stamped in
1531 -- each Child MMTT record.
1532 -- Added as part of Bug#2666620. Refer it for any information.
1533 --
1534 -- Input Parameters
1535 -- p_organization_id => Organization ID
1536 -- p_transfer_lpn_id => LPN ID
1537 --
1538 -- Output Parameters
1539 -- x_return_status => FND_API.G_RET_STS_UNEXP_ERROR or
1540 -- FND_API.G_RET_STS_SUCCESS
1541
1542 PROCEDURE handle_bulk_picking(
1543 x_return_status OUT NOCOPY VARCHAR2
1544 , p_organization_id NUMBER
1545 , p_transfer_lpn_id NUMBER
1546 ) IS
1547 CURSOR c_get_bulk_txn(p_org_id IN NUMBER, p_lpn_id IN NUMBER) IS
1548 SELECT transaction_temp_id, transaction_header_id, lpn_id, transfer_lpn_id
1549 FROM mtl_material_transactions_temp t1
1550 WHERE transfer_lpn_id = p_lpn_id
1551 AND organization_id = p_org_id
1552 AND parent_line_id IS NULL
1553 AND EXISTS(
1554 SELECT 1
1555 FROM mtl_material_transactions_temp t2
1556 WHERE t2.parent_line_id = t1.transaction_temp_id
1557 AND t2.organization_id = t1.organization_id
1558 );
1559 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1560 BEGIN
1561 x_return_status := fnd_api.g_ret_sts_success;
1562 -- For each transaction returned by the cursor update the Child Records and delete the Parent.
1563 FOR v_rec IN c_get_bulk_txn(p_organization_id, p_transfer_lpn_id) LOOP
1564 -- Updating the Child Records.
1565 UPDATE mtl_material_transactions_temp
1566 SET transaction_header_id = v_rec.transaction_header_id
1567 , transfer_lpn_id = v_rec.transfer_lpn_id
1568 , lpn_id = v_rec.lpn_id
1569 , parent_line_id = NULL
1570 WHERE parent_line_id = v_rec.transaction_temp_id
1571 AND organization_id = p_organization_id;
1572
1573 -- Deleting the Parent Record.
1574 DELETE FROM mtl_material_transactions_temp
1575 WHERE transaction_temp_id = v_rec.transaction_temp_id;
1576 END LOOP;
1577 EXCEPTION
1578 WHEN OTHERS THEN
1579 x_return_status := fnd_api.g_ret_sts_unexp_error;
1580 IF (l_debug = 1) THEN
1581 mydebug('Unknown Exception occurred: ' || SQLERRM,'HANDLE_BULK_PICKING');
1582 END IF;
1583 END handle_bulk_picking;
1584
1585 --
1586 -- Name
1587 -- PROCEDURE GET_TASKS
1588 --
1589 -- Purpose
1590 -- Gets a list of Tasks given the LPN and Organization.
1591 -- Changed as part of Bug#2666620. Refer it for any information.
1592 --
1593 -- Input Parameters
1594 -- p_organization_id => Organization ID
1595 -- p_transfer_lpn_id => LPN ID
1596 --
1597 -- Output Parameters
1598 -- x_tasks => Ref Cursor containing the Tasks
1599 -- x_drop_type => Either MFG or OTHERS depending on whether LPN has Mfg Picks or not
1600 -- x_multiple_drops => Whether or not there are multiple drops on LPN
1601 -- x_drop_lpn_option => Drop LPN Option
1602 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
1603 -- FND_API.G_RET_STS_ERROR or "W" (warning)
1604
1605 PROCEDURE get_tasks(
1606 x_tasks OUT NOCOPY t_genref
1607 , x_drop_type OUT NOCOPY VARCHAR2
1608 , x_multiple_drops OUT NOCOPY VARCHAR2
1609 , x_drop_lpn_option OUT NOCOPY NUMBER
1610 , x_return_status OUT NOCOPY VARCHAR2
1611 , p_organization_id IN NUMBER
1612 , p_transfer_lpn_id IN NUMBER
1613 ) IS
1614
1615 l_api_name VARCHAR2(30) := 'GET_TASKS';
1616 l_return_status VARCHAR2(1);
1617 l_message VARCHAR2(400);
1618
1619 CURSOR c_det_pick_type(p_org_id IN NUMBER, p_lpn_id IN NUMBER) IS
1620 SELECT DECODE (mtrh.move_order_type
1621 , inv_globals.g_move_order_mfg_pick, 'MFG'
1622 , 'OTHERS')
1623 FROM mtl_material_transactions_temp mmtt
1624 , mtl_txn_request_lines mtrl
1625 , mtl_txn_request_headers mtrh
1626 WHERE mmtt.organization_id = p_org_id
1627 AND mmtt.transfer_lpn_id = p_lpn_id
1628 AND mtrl.line_id = mmtt.move_order_line_id
1629 AND mtrh.header_id = mtrl.header_id
1630 AND rownum = 1;
1631
1632 CURSOR c_get_mfg_drop_details(p_org_id IN NUMBER, p_lpn_id IN NUMBER) IS
1633 SELECT nvl(mmtt.transfer_subinventory,0) transfer_subinventory
1634 , nvl(mmtt.transfer_to_location,0) transfer_to_location
1635 , mmtt.transaction_type_id
1636 , mtrl.txn_source_id
1637 , mtrl.txn_source_line_id
1638 , mtrl.reference_id
1639 FROM mtl_material_transactions_temp mmtt
1640 , mtl_txn_request_lines mtrl
1641 WHERE mmtt.organization_id = p_org_id
1642 AND mmtt.transfer_lpn_id = p_lpn_id
1643 AND mtrl.line_id = mmtt.move_order_line_id;
1644
1645 mfg_drop_rec c_get_mfg_drop_details%ROWTYPE;
1646 mfg_orig_drop_rec c_get_mfg_drop_details%ROWTYPE;
1647
1648 b_multiple_drops BOOLEAN := FALSE;
1649
1650 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1651 BEGIN
1652 -- This API just update transfer_locator of each MMTT record for
1653 -- passed transfer_lpn_id. In case it fails, MMTT will not be updated
1654 -- and pick drop will continue.
1655 wms_op_runtime_pub_apis.update_drop_locator_for_task(
1656 x_return_status => l_return_status
1657 , x_message => l_message
1658 , x_drop_lpn_option => x_drop_lpn_option
1659 , p_transfer_lpn_id => p_transfer_lpn_id
1660 );
1661
1662 x_return_status := l_return_status;
1663 x_drop_type := 'OTHERS';
1664 x_multiple_drops := 'FALSE';
1665
1666 -- Determine whether the LPN has Manufacturing Picks or Other Pick Types
1667 OPEN c_det_pick_type(p_organization_id, p_transfer_lpn_id);
1668 FETCH c_det_pick_type INTO x_drop_type;
1669 CLOSE c_det_pick_type;
1670
1671 IF x_drop_type = 'MFG' THEN
1672 -- Handling the case of Bulk Picked Lines in the LPN.
1673 handle_bulk_picking(x_return_status, p_organization_id, p_transfer_lpn_id);
1674 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1675 RAISE fnd_api.g_exc_unexpected_error;
1676 END IF;
1677
1678 -- Determine if the LPN has multiple drop destinations
1679 OPEN c_get_mfg_drop_details(p_organization_id, p_transfer_lpn_id);
1680 FETCH c_get_mfg_drop_details INTO mfg_drop_rec;
1681
1682 IF c_get_mfg_drop_details%FOUND THEN
1683 mfg_orig_drop_rec := mfg_drop_rec;
1684 LOOP
1685 FETCH c_get_mfg_drop_details INTO mfg_drop_rec;
1686 EXIT WHEN c_get_mfg_drop_details%NOTFOUND;
1687
1688 IF ( mfg_drop_rec.transaction_type_id = INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
1689 AND mfg_orig_drop_rec.transaction_type_id = INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
1690 AND(( mfg_drop_rec.txn_source_id <> mfg_orig_drop_rec.txn_source_id)
1691 OR ( mfg_drop_rec.txn_source_line_id <> mfg_orig_drop_rec.txn_source_line_id)
1692 OR ( mfg_drop_rec.reference_id <> mfg_orig_drop_rec.reference_id)))
1693 OR ( mfg_drop_rec.transaction_type_id = INV_Globals.G_TYPE_XFER_ORDER_REPL_SUBXFR
1694 AND mfg_orig_drop_rec.transaction_type_id = INV_Globals.G_TYPE_XFER_ORDER_REPL_SUBXFR
1695 AND (( mfg_drop_rec.transfer_subinventory <> mfg_orig_drop_rec.transfer_subinventory)
1696 OR ( mfg_drop_rec.transfer_to_location <> mfg_orig_drop_rec.transfer_to_location)))
1697 OR ( mfg_drop_rec.transaction_type_id <> mfg_orig_drop_rec.transaction_type_id)
1698 THEN
1699 b_multiple_drops := TRUE;
1700 ELSE
1701 mfg_orig_drop_rec := mfg_drop_rec;
1702 END IF;
1703
1704 EXIT WHEN b_multiple_drops;
1705 END LOOP;
1706 END IF;
1707
1708 CLOSE c_get_mfg_drop_details;
1709
1710 IF b_multiple_drops THEN
1711 x_multiple_drops := 'TRUE';
1712 END IF;
1713
1714 -- For Mfg drops, sub xfers are first followed by component issues,
1715 -- so drops are ordered by txn action descending order
1716 -- (action id of 2 = sub xfer, action id of 1 = comp issue)
1717 OPEN x_tasks FOR
1718 SELECT mmtt.transaction_action_id
1719 , mmtt.transaction_temp_id
1720 , mmtt.inventory_item_id item_id
1721 , msik.concatenated_segments item
1722 , msik.revision_qty_control_code
1723 , msik.lot_control_code
1724 , msik.serial_number_control_code
1725 , mmtt.revision
1726 , mmtt.primary_quantity
1727 , msik.primary_uom_code
1728 , mmtt.transfer_subinventory transfer_sub
1729 , NVL(msi.dropping_order, 0) sub_dropping_order
1730 , NVL(msi.picking_order, 0) sub_picking_order
1731 , inv_project.get_locsegs(mmtt.transfer_to_location,
1732 mmtt.organization_id) transfer_loc
1733 , NVL(mil.dropping_order, 0) loc_dropping_order
1734 , NVL(mil.picking_order, 0) loc_picking_order
1735 , mmtt.transaction_type_id
1736 , mmtt.wip_entity_type
1737 , wdt.priority
1738 , wdt.task_id taskid
1739 , wdt.task_type
1740 , inv_project.get_project_id
1741 , inv_project.get_project_number
1742 , inv_project.get_task_id
1743 , inv_project.get_task_number
1744 , 0 wip_entity_id
1745 , 0 repetitive_schedule_id
1746 , 0 operation_seq_num
1747 , mmtt.transfer_to_location --Bug#2756609
1748 FROM mtl_material_transactions_temp mmtt
1749 , mtl_secondary_inventories msi
1750 , mtl_item_locations mil
1751 , wms_dispatched_tasks wdt
1752 , mtl_system_items_vl msik /* Bug 5581528 */
1753 WHERE mmtt.organization_id = p_organization_id
1754 AND mmtt.transfer_lpn_id = p_transfer_lpn_id
1755 AND mmtt.transaction_source_type_id = inv_globals.g_sourcetype_inventory
1756 AND mmtt.transaction_action_id = inv_globals.g_action_subxfr
1757 AND msi.organization_id = mmtt.organization_id
1758 AND msi.secondary_inventory_name = mmtt.transfer_subinventory
1759 AND mmtt.organization_id = mil.organization_id
1760 AND mmtt.transfer_subinventory = mil.subinventory_code
1761 AND mmtt.transfer_to_location = mil.inventory_location_id
1762 AND wdt.organization_id = mmtt.organization_id
1763 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
1764 AND wdt.status IN (3, 4)
1765 AND wdt.task_type = 1
1766 AND msik.organization_id = mmtt.organization_id
1767 AND msik.inventory_item_id = mmtt.inventory_item_id
1768 UNION ALL
1769 SELECT mmtt.transaction_action_id
1770 , mmtt.transaction_temp_id
1771 , mmtt.inventory_item_id item_id
1772 , msik.concatenated_segments item
1773 , msik.revision_qty_control_code
1774 , msik.lot_control_code
1775 , msik.serial_number_control_code
1776 , mmtt.revision
1777 , mmtt.primary_quantity
1778 , msik.primary_uom_code
1779 , to_char(NULL) transfer_sub
1780 , to_number(NULL) sub_dropping_order
1781 , to_number(NULL) sub_picking_order
1782 , to_char(NULL) transfer_loc
1783 , to_number(NULL) loc_dropping_order
1784 , to_number(NULL) loc_picking_order
1785 , mmtt.transaction_type_id
1786 , mmtt.wip_entity_type
1787 , wdt.priority
1788 , wdt.task_id taskid
1789 , wdt.task_type
1790 , to_char(NULL)
1791 , to_char(NULL)
1792 , to_char(NULL)
1793 , to_char(NULL)
1794 , mtrl.txn_source_id wip_entity_id
1795 , mtrl.reference_id repetitive_schedule_id
1796 , mtrl.txn_source_line_id operation_seq_num
1797 , to_number(NULL) transfer_to_location --Bug#2756609
1798 FROM mtl_material_transactions_temp mmtt
1799 , mtl_txn_request_lines mtrl
1800 , wms_dispatched_tasks wdt
1801 , mtl_system_items_vl msik /* Bug 5581528 */
1802 WHERE mmtt.organization_id = p_organization_id
1803 AND mmtt.transfer_lpn_id = p_transfer_lpn_id
1804 AND mmtt.transaction_source_type_id = inv_globals.g_sourcetype_wip
1805 AND mmtt.transaction_action_id = inv_globals.g_action_issue
1806 AND mtrl.line_id = mmtt.move_order_line_id
1807 AND wdt.organization_id = mmtt.organization_id
1808 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
1809 AND wdt.status IN (3, 4)
1810 AND wdt.task_type = 1
1811 AND msik.organization_id = mmtt.organization_id
1812 AND msik.inventory_item_id = mmtt.inventory_item_id
1813 ORDER BY transaction_action_id DESC
1814 , sub_dropping_order
1815 , sub_picking_order
1816 , transfer_sub
1817 , loc_dropping_order
1818 , loc_picking_order
1819 , transfer_loc
1820 , wip_entity_id
1821 , repetitive_schedule_id
1822 , operation_seq_num
1823 , item_id
1824 , revision
1825 , priority
1826 , taskid;
1827 ELSE
1828 x_drop_type := 'OTHERS';
1829 OPEN x_tasks FOR
1830 SELECT mmtt.inventory_item_id
1831 , mmtt.lot_number
1832 , mmtt.revision
1833 , mmtt.transaction_quantity
1834 , mmtt.transaction_uom
1835 , mmtt.locator_id
1836 , mmtt.subinventory_code
1837 , nvl(inv_project.get_locsegs(mmtt.transfer_to_location, mmtt.organization_id),'') loc
1838 , msik.concatenated_segments item
1839 , mmtt.transaction_temp_id
1840 , mmtt.transfer_subinventory
1841 , mmtt.transfer_to_location
1842 , mmtt.transaction_type_id
1843 , mmtt.wip_entity_type
1844 , mmtt.transaction_source_type_id
1845 , wdt.priority priority
1846 , wdt.task_id taskid
1847 , wdt.task_type task_type
1848 , inv_project.get_project_id
1849 , inv_project.get_project_number
1850 , inv_project.get_task_id
1851 , inv_project.get_task_number
1852 FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt, mtl_system_items_vl msik /* Bug 5581528 */
1853 WHERE wdt.organization_id = p_organization_id
1854 AND wdt.status IN (3, 4)
1855 AND wdt.task_type IN (1, 4, 5, 7)
1856 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
1857 AND mmtt.transfer_lpn_id = p_transfer_lpn_id
1858 AND mmtt.organization_id = msik.organization_id
1859 AND mmtt.inventory_item_id = msik.inventory_item_id
1860 ORDER BY subinventory_code, loc, priority, taskid;
1861 END IF; -- x_drop_type = 'MFG'
1862
1863 EXCEPTION
1864 WHEN fnd_api.g_exc_unexpected_error THEN
1865 x_return_status := fnd_api.g_ret_sts_unexp_error;
1866 WHEN OTHERS THEN
1867 x_return_status := fnd_api.g_ret_sts_error;
1868 IF (l_debug = 1) THEN
1869 mydebug('Unknown exception occurred: ' || SQLERRM, l_api_name);
1870 END IF;
1871 END get_tasks;
1872
1873 --
1874 -- Name
1875 -- PROCEDURE GET_LOT_NUMBER_INFO
1876 --
1877 -- Purpose
1878 -- Gets the list of all Lots and its Quantity for the passed in list of Transaction Temp IDs.
1879 -- Added as part of Bug#2666620. Refer it for any information.
1880 --
1881 -- Input Parameters
1882 -- p_txn_temp_id_list => Comma delimited Transaction Temp ID List
1883 --
1884 -- Output Parameters
1885 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
1886 -- FND_API.G_RET_STS_ERROR
1887 -- x_lot_num_list => Comma delimited Lot Number List
1888 -- x_lot_qty_list => Comma delimited Lot Qty List
1889 -- x_display_serials => Whether Serials are associated with the Txn Temp ID list.
1890
1891 PROCEDURE get_lot_number_info(
1892 x_return_status OUT NOCOPY VARCHAR2
1893 , x_lot_num_list OUT NOCOPY VARCHAR2
1894 , x_lot_qty_list OUT NOCOPY VARCHAR2
1895 , x_display_serials OUT NOCOPY VARCHAR2
1896 , p_txn_temp_id_list IN VARCHAR2
1897 ) IS
1898 l_api_name VARCHAR2(30) := 'GET_LOT_NUMBER_INFO';
1899 l_temp_lot_num_tbl inv_globals.varchar_tbl_type;
1900 l_temp_lot_qty_tbl inv_globals.number_tbl_type;
1901 l_lot_num_tbl inv_globals.varchar_tbl_type;
1902 l_lot_qty_tbl inv_globals.number_tbl_type;
1903 l_start NUMBER;
1904 l_end NUMBER;
1905 l_txn_temp_id NUMBER;
1906 l_found BOOLEAN;
1907
1908 CURSOR c_lot_list(p_txn_temp_id IN NUMBER) IS
1909 SELECT lot_number, SUM(primary_quantity)
1910 FROM mtl_transaction_lots_temp
1911 WHERE transaction_temp_id = p_txn_temp_id
1912 GROUP BY lot_number;
1913
1914 CURSOR c_display_serials(p_txn_temp_id IN NUMBER) IS
1915 SELECT DECODE(COUNT(serial_transaction_temp_id), 0, 'N', 'Y')
1916 FROM mtl_transaction_lots_temp
1917 WHERE transaction_temp_id = p_txn_temp_id;
1918 BEGIN
1919 x_return_status := fnd_api.g_ret_sts_success;
1920
1921 IF (g_trace_on = 1) THEN
1922 mydebug('Txn Temp ID List = '||p_txn_temp_id_list, l_api_name);
1923 END IF;
1924
1925 l_start := 1;
1926 LOOP
1927 l_end := INSTR(p_txn_temp_id_list,',',l_start);
1928 IF l_end = 0 THEN
1929 l_end := LENGTH(p_txn_temp_id_list) + 1;
1930 END IF;
1931 -- Get the next Transaction Temp ID.
1932 l_txn_temp_id := TO_NUMBER(SUBSTR(p_txn_temp_id_list, l_start, l_end - l_start));
1933
1934 -- For each Transaction Temp ID fetch the lots associated with it.
1935 OPEN c_lot_list(l_txn_temp_id);
1936 FETCH c_lot_list BULK COLLECT INTO l_temp_lot_num_tbl, l_temp_lot_qty_tbl;
1937 CLOSE c_lot_list;
1938
1939 IF l_temp_lot_num_tbl.COUNT > 0 THEN
1940 -- If the Lot Number already Exists, then add the Quantity. Otherwise create a new record.
1941 FOR j IN l_temp_lot_num_tbl.FIRST .. l_temp_lot_num_tbl.LAST LOOP
1942 IF l_lot_num_tbl.COUNT = 0 THEN
1943 l_lot_num_tbl(1) := l_temp_lot_num_tbl(j);
1944 l_lot_qty_tbl(1) := l_temp_lot_qty_tbl(j);
1945 ELSE
1946 l_found := FALSE;
1947 FOR k IN l_lot_num_tbl.FIRST..l_lot_num_tbl.LAST LOOP
1948 IF l_lot_num_tbl(k) = l_temp_lot_num_tbl(j) THEN
1949 l_lot_qty_tbl(k) := l_lot_qty_tbl(k) + l_temp_lot_qty_tbl(j);
1950 l_found := TRUE;
1951 EXIT;
1952 END IF;
1953 END LOOP;
1954 IF l_found = FALSE THEN
1955 l_lot_num_tbl(l_lot_num_tbl.COUNT + 1) := l_temp_lot_num_tbl(j);
1956 l_lot_qty_tbl(l_lot_qty_tbl.COUNT + 1) := l_temp_lot_qty_tbl(j);
1957 END IF;
1958 END IF;
1959 END LOOP;
1960 END IF;
1961
1962 EXIT WHEN l_end = LENGTH(p_txn_temp_id_list) + 1;
1963 l_start := l_end + 1;
1964 END LOOP;
1965
1966 -- Converting the PLSQL records into a comma separated String.
1967 IF l_lot_num_tbl.COUNT > 0 THEN
1968 x_lot_num_list := l_lot_num_tbl(1);
1969 x_lot_qty_list := l_lot_qty_tbl(1);
1970 FOR i IN 2..l_lot_num_tbl.LAST LOOP
1971 x_lot_num_list := x_lot_num_list || ',' || l_lot_num_tbl(i);
1972 x_lot_qty_list := x_lot_qty_list || ',' || l_lot_qty_tbl(i);
1973 END LOOP;
1974 ELSE
1975 IF (g_trace_on = 1) THEN
1976 mydebug('No Lots retrieved for the current Query Criteria', l_api_name);
1977 END IF;
1978 x_return_status := fnd_api.g_ret_sts_error;
1979 RETURN;
1980 END IF;
1981
1982 -- Determine whether the Item is Serial Controlled or not.
1983 OPEN c_display_serials(l_txn_temp_id);
1984 FETCH c_display_serials INTO x_display_serials;
1985 CLOSE c_display_serials;
1986 EXCEPTION
1987 WHEN OTHERS THEN
1988 IF c_lot_list%ISOPEN THEN
1989 CLOSE c_lot_list;
1990 END IF;
1991 IF c_display_serials%ISOPEN THEN
1992 CLOSE c_display_serials;
1993 END IF;
1994 IF (g_trace_on = 1) THEN
1995 mydebug('Exception while getting the Lots: ' || SQLERRM, l_api_name);
1996 END IF;
1997 x_return_status := fnd_api.g_ret_sts_error;
1998 END get_lot_number_info;
1999
2000 --
2001 -- Name
2002 -- PROCEDURE GET_SERIAL_NUMBERS
2003 --
2004 -- Purpose
2005 -- Gets the list of all Serials for the passed in list of Transaction Temp IDs. If Lot is given
2006 -- the list contains Serials belonging to that Lot alone.
2007 -- Added as part of Bug#2666620. Refer it for any information.
2008 --
2009 -- Input Parameters
2010 -- p_txn_temp_id_list => Comma delimited Transaction Temp ID List
2011 -- p_lot_number => Lot Number
2012 --
2013 -- Output Parameters
2014 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
2015 -- FND_API.G_RET_STS_ERROR
2016 -- x_serial_list => Comma delimited Serial List.
2017
2018 PROCEDURE get_serial_numbers(
2019 x_return_status OUT NOCOPY VARCHAR2
2020 , x_serial_list OUT NOCOPY VARCHAR2
2021 , p_txn_temp_id_list IN VARCHAR2
2022 , p_lot_number IN VARCHAR2
2023 ) IS
2024 l_api_name VARCHAR2(30) := 'GET_SERIAL_NUMBERS';
2025 l_temp_serial_list inv_globals.varchar_tbl_type;
2026 l_start NUMBER;
2027 l_end NUMBER;
2028 l_txn_temp_id NUMBER;
2029
2030 CURSOR c_serial_list(p_txn_temp_id IN NUMBER, p_lot_num IN VARCHAR2) IS
2031 SELECT msnt.fm_serial_number
2032 FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers_temp msnt
2033 WHERE p_lot_num IS NOT NULL
2034 AND mtlt.transaction_temp_id = p_txn_temp_id
2035 AND mtlt.lot_number = p_lot_num
2036 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
2037 UNION ALL
2038 SELECT msnt.fm_serial_number
2039 FROM mtl_serial_numbers_temp msnt
2040 WHERE p_lot_num IS NULL
2041 AND msnt.transaction_temp_id = p_txn_temp_id
2042 ORDER BY 1;
2043 BEGIN
2044 x_return_status := fnd_api.g_ret_sts_success;
2045
2046 IF (g_trace_on = 1) THEN
2047 mydebug('Txn Temp ID List = '||p_txn_temp_id_list||' : Lot Number = '||p_lot_number, l_api_name);
2048 END IF;
2049
2050 l_start := 1;
2051 LOOP
2052 l_end := INSTR(p_txn_temp_id_list,',',l_start);
2053 IF l_end = 0 THEN
2054 l_end := LENGTH(p_txn_temp_id_list) + 1;
2055 END IF;
2056 -- Get the next Transaction Temp ID.
2057 l_txn_temp_id := TO_NUMBER(SUBSTR(p_txn_temp_id_list, l_start, l_end - l_start));
2058
2059 -- Fetch the Serials associated with the Transaction Temp ID and Lot Number (If Lot Ctrl).
2060 OPEN c_serial_list(l_txn_temp_id,p_lot_number);
2061 FETCH c_serial_list BULK COLLECT INTO l_temp_serial_list;
2062 CLOSE c_serial_list;
2063
2064 -- Converting the PLSQL records into a comma separated String
2065 IF l_temp_serial_list.COUNT > 0 THEN
2066 IF x_serial_list IS NULL THEN
2067 x_serial_list := l_temp_serial_list(1);
2068 ELSE
2069 x_serial_list := x_serial_list || ',' || l_temp_serial_list(1);
2070 END IF;
2071 FOR ii IN 2..l_temp_serial_list.LAST LOOP
2072 x_serial_list := x_serial_list || ',' || l_temp_serial_list(ii);
2073 END LOOP;
2074 END IF;
2075 l_start := l_end + 1;
2076 EXIT WHEN l_end = LENGTH(p_txn_temp_id_list) + 1;
2077 END LOOP;
2078
2079 IF x_serial_list IS NULL THEN
2080 IF (g_trace_on = 1) THEN
2081 mydebug('No Serials retrieved for the given Query Criteria',l_api_name);
2082 END IF;
2083 x_return_status := fnd_api.g_ret_sts_error;
2084 END IF;
2085 EXCEPTION
2086 WHEN OTHERS THEN
2087 IF c_serial_list%ISOPEN THEN
2088 CLOSE c_serial_list;
2089 END IF;
2090 IF (g_trace_on = 1) THEN
2091 mydebug('Exception on getting the Serials : '|| SQLERRM, l_api_name);
2092 END IF;
2093 x_return_status := fnd_api.g_ret_sts_error;
2094 END get_serial_numbers;
2095
2096 PROCEDURE manual_pick
2097 (p_employee_id IN NUMBER,
2098 p_effective_start_date IN DATE,
2099 p_effective_end_date IN DATE,
2100 p_organization_id IN NUMBER,
2101 p_subinventory_code IN VARCHAR2,
2102 p_equipment_id IN NUMBER,
2103 p_equipment_serial IN VARCHAR2,
2104 p_transaction_temp_id IN NUMBER,
2105 p_allow_unreleased_task IN VARCHAR2 :='Y', -- for manual picking only bug 4718145
2106 x_task_type_id OUT NOCOPY NUMBER,
2107 x_return_status OUT NOCOPY VARCHAR2,
2108 x_msg_count OUT NOCOPY NUMBER,
2109 x_msg_data OUT NOCOPY VARCHAR2) IS
2110
2111 l_person_resource_id NUMBER;
2112 l_machine_resource_id NUMBER;
2113 l_standard_operation_id NUMBER;
2114 l_operation_plan_id NUMBER;
2115 l_move_order_line_id NUMBER;
2116 l_user_id NUMBER;
2117 l_mmtt_rowcnt NUMBER;
2118 l_wdt_rowcnt NUMBER;
2119 l_undispatched_picks NUMBER;
2120 l_equipment_serial WMS_DISPATCHED_TASKS.EQUIPMENT_INSTANCE%TYPE;
2121 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2122 l_status NUMBER ; --Bug#5157839.
2123
2124 /*6009436 Begin */
2125 CURSOR c_fm_to_serial_number IS
2126 SELECT
2127 msnt.fm_serial_number,
2128 msnt.to_serial_number
2129 FROM mtl_serial_numbers_temp msnt
2130 WHERE msnt.transaction_temp_id = p_transaction_temp_id;
2131
2132 CURSOR c_fm_to_lot_serial_number IS
2133 SELECT
2134 msnt.fm_serial_number,
2135 msnt.to_serial_number
2136 FROM
2137 mtl_serial_numbers_temp msnt,
2138 mtl_transaction_lots_temp mtlt
2139 WHERE mtlt.transaction_temp_id = p_transaction_temp_id
2140 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
2141
2142 l_item_id NUMBER := NULL;
2143 l_serial_ctrl_code NUMBER;
2144 l_lot_ctrl_code NUMBER ;
2145 l_fm_serial_number MTL_SERIAL_NUMBERS_TEMP.FM_SERIAL_NUMBER%TYPE;
2146 l_to_serial_number MTL_SERIAL_NUMBERS_TEMP.TO_SERIAL_NUMBER%TYPE;
2147 l_txn_hdr_id NUMBER;
2148 /*6009436 End */
2149
2150 BEGIN
2151
2152 IF (l_debug = 1) THEN
2153 mydebug('MANUAL_PICK: In Manual Pick API', 'MANUAL_PICK');
2154 mydebug('p_allow_unreleased_task is : ' || p_allow_unreleased_task, 'MANUAL_PICK');
2155 END IF;
2156
2157 -- Bug #4090630 - inserting NULL into WMS_DISPATCHED_TASKS.equipement_instance
2158 -- if p_equipment_serial is NONE
2159 l_equipment_serial := p_equipment_serial;
2160 IF (p_equipment_serial = 'NONE') then
2161 IF (l_debug = 1) THEN
2162 mydebug('l_equipment_serial is null', 'MANUAL_PICK');
2163 END IF;
2164 l_equipment_serial := NULL;
2165 END IF;
2166
2167 x_return_status := fnd_api.g_ret_sts_success;
2168
2169 l_mmtt_rowcnt := 0;
2170 l_wdt_rowcnt := 0;
2171
2172
2173 -- Restricting the user not to load the child task
2174 -- that are merged using bulk pick. Added the condition parent_line_id
2175 -- not null for the same.
2176 BEGIN
2177 IF nvl(p_allow_unreleased_task,'Y') = 'Y' THEN
2178 SELECT 1
2179 INTO l_mmtt_rowcnt
2180 FROM dual
2181 WHERE exists (SELECT 1
2182 FROM mtl_material_transactions_temp
2183 WHERE transaction_temp_id = p_transaction_temp_id
2184 AND organization_id = p_organization_id
2185 AND (parent_line_id is NULL -- regular task
2186 OR parent_line_id = transaction_temp_id)); -- bulk task
2187 ELSE
2188 SELECT 1
2189 INTO l_mmtt_rowcnt
2190 FROM dual
2191 WHERE exists (SELECT 1
2192 FROM mtl_material_transactions_temp
2193 WHERE transaction_temp_id = p_transaction_temp_id
2194 AND organization_id = p_organization_id
2195 AND wms_task_status <> 8 -- unreleased
2196 AND (
2197 parent_line_id is NULL -- regular task
2198 OR parent_line_id = transaction_temp_id)); -- bulk task
2199
2200 END IF;
2201 EXCEPTION
2202 WHEN no_data_found THEN
2203 IF (l_debug = 1) THEN
2204 mydebug('MANUAL_PICK: No mmtt rows found for pick slip' || p_transaction_temp_id, 'MANUAL_PICK');
2205 END IF;
2206
2207 l_mmtt_rowcnt := 0;
2208 fnd_message.set_name('WMS', 'WMS_INVALID_PICKID');
2209 fnd_msg_pub.ADD;
2210 RAISE fnd_api.g_exc_error;
2211 END;
2212
2213 IF (l_debug = 1) THEN
2214 mydebug('MANUAL_PICK: MMTT record is available', 'MANUAL_PICK');
2215 END IF;
2216
2217 IF l_mmtt_rowcnt > 0 THEN
2218 -- Check if this line has been sent to somebody else
2219
2220 BEGIN
2221 SELECT 1
2222 INTO l_wdt_rowcnt
2223 FROM dual
2224 WHERE exists (SELECT 1
2225 FROM wms_dispatched_tasks t
2226 WHERE t.transaction_temp_id = p_transaction_temp_id
2227 AND person_id <> p_employee_id);
2228 EXCEPTION
2229 WHEN NO_DATA_FOUND THEN
2230 l_wdt_rowcnt := 0;
2231 END;
2232
2233 IF l_wdt_rowcnt > 0 THEN
2234 IF (l_debug = 1) THEN
2235 mydebug('MANUAL_PICK: Task has been assigned to somebody else', 'MANUAL_PICK');
2236 END IF;
2237
2238 fnd_message.set_name('WMS', 'WMS_TASK_UNAVAIL');
2239 fnd_msg_pub.ADD;
2240 RAISE fnd_api.g_exc_error;
2241 ELSE
2242 BEGIN --bug#5157839.Start fix
2243 SELECT wdt.status INTO l_status FROM wms_dispatched_tasks wdt
2244 WHERE wdt.transaction_temp_id = p_transaction_temp_id;
2245
2246 IF l_status NOT IN (1,2,3) THEN
2247 IF (l_debug = 1) THEN
2248 mydebug('MANUAL_PICK: The WDT has status other than 1 or 2 ', 'MANUAL_PICK');
2249 END IF;
2250 fnd_message.set_name('WMS', 'WMS_INVALID_PICKID');
2251 fnd_msg_pub.ADD;
2252 RAISE fnd_api.g_exc_error;
2253 END IF;
2254 EXCEPTION
2255 WHEN NO_DATA_FOUND THEN
2256 IF (l_debug = 1) THEN
2257 mydebug('MANUAL_PICK: The WDT is not present', 'MANUAL_PICK');
2258 END IF;
2259 END; --bug#5157839.End of fix
2260
2261 IF (l_debug = 1) THEN
2262 mydebug('MANUAL_PICK: WDT record is available', 'MANUAL_PICK');
2263 END IF;
2264
2265 -- Update MMTT record with a new header
2266 l_user_id := fnd_global.user_id;
2267
2268 SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
2269 INTO l_txn_hdr_id
2270 FROM DUAL;
2271
2272 UPDATE mtl_material_transactions_temp
2273 SET transaction_header_id = l_txn_hdr_id ,
2274 last_update_date = Sysdate,
2275 last_updated_by = l_user_id,
2276 creation_date = Sysdate,
2277 created_by = l_user_id
2278 , posting_flag = 'Y' -- Bug4185621: this will change the parent posting flag to 'Y' for bulking picking
2279 -- If not bulking picking, this has not effect
2280 WHERE transaction_temp_id = p_transaction_temp_id
2281 returning wms_task_type INTO x_task_type_id;
2282
2283
2284
2285
2286 BEGIN
2287 SELECT 1
2288 INTO l_wdt_rowcnt
2289 FROM dual
2290 WHERE exists (SELECT 1
2291 FROM wms_dispatched_tasks t
2292 WHERE t.transaction_temp_id = p_transaction_temp_id);
2293
2294 g_previous_task_status(p_transaction_temp_id) := 2;
2295 EXCEPTION
2296 WHEN NO_DATA_FOUND THEN
2297 l_wdt_rowcnt := 0;
2298 g_previous_task_status(p_transaction_temp_id) := 1;
2299 END;
2300
2301 IF l_wdt_rowcnt = 0 THEN
2302 BEGIN
2303 SELECT bremp.resource_id role_id,
2304 t.wms_task_type,
2305 t.standard_operation_id,
2306 t.operation_plan_id,
2307 t.move_order_line_id,
2308 t.inventory_item_id
2309 INTO l_person_resource_id,
2310 x_task_type_id,
2311 l_standard_operation_id,
2312 l_operation_plan_id,
2313 l_move_order_line_id,
2314 l_item_id
2315 FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
2316 WHERE t.transaction_temp_id = p_transaction_temp_id
2317 AND t.standard_operation_id = bsor.standard_operation_id
2318 AND bsor.resource_id = bremp.resource_id
2319 AND bremp.resource_type = 2
2320 AND ROWNUM < 2;
2321
2322 IF (l_debug = 1) THEN
2323 mydebug('MANUAL_PICK: After getting Resource ID....', 'MANUAL_PICK');
2324 END IF;
2325
2326 EXCEPTION
2327 WHEN no_data_found THEN
2328 IF (l_debug = 1) THEN
2329 mydebug('MANUAL_PICK: No Person Resource ID found', 'MANUAL_PICK');
2330 END IF;
2331
2332 RAISE fnd_api.g_exc_error;
2333 END;
2334
2335 IF p_equipment_id IS NOT NULL AND
2336 p_equipment_id <> -999 THEN
2337 BEGIN
2338 -- bug fix 1772907, lezhang
2339
2340 SELECT resource_id
2341 INTO l_machine_resource_id
2342 FROM bom_resource_equipments
2343 WHERE inventory_item_id = p_equipment_id
2344 AND ROWNUM < 2;
2345 EXCEPTION
2346 WHEN no_data_found THEN
2347 IF (l_debug = 1) THEN
2348 mydebug('MANUAL_PICK: No Machine Resource ID found', 'MANUAL_PICK');
2349 END IF;
2350
2351 RAISE fnd_api.g_exc_error;
2352 END;
2353 END IF;
2354
2355 --Bug6009436.Begin
2356 SELECT msi.serial_number_control_code
2357 , msi.lot_control_code
2358 INTO l_serial_ctrl_code
2359 , l_lot_ctrl_code
2360 FROM mtl_system_items msi
2361 WHERE msi.inventory_item_id = l_item_id
2362 AND msi.organization_id =p_organization_id ;
2363
2364 IF (l_debug = 1) THEN
2365 mydebug('manual_pick:serial control code:'||l_serial_ctrl_code || ',lot control code :'||l_lot_ctrl_code,'MANUAL_PICK');
2366 END IF;
2367
2368 IF (l_serial_ctrl_code NOT IN (1,6) ) THEN --Serial controlled item
2369 BEGIN
2370 IF (l_lot_ctrl_code > 1 ) THEN --Serial and lot controlled item
2371 OPEN c_fm_to_lot_serial_number;
2372 LOOP
2373 FETCH c_fm_to_lot_serial_number
2374 INTO l_fm_serial_number,l_to_serial_number;
2375 EXIT WHEN c_fm_to_lot_serial_number%NOTFOUND;
2376
2377 UPDATE MTL_SERIAL_NUMBERS msn
2378 SET GROUP_MARK_ID=l_txn_hdr_id
2379 WHERE msn.current_organization_id=p_organization_id
2380 AND msn.inventory_item_id= l_item_id
2381 AND msn.SERIAL_NUMBER BETWEEN l_fm_serial_number AND
2382 l_to_serial_number;
2383 END LOOP;
2384 CLOSE c_fm_to_lot_serial_number;
2385
2386 UPDATE mtl_serial_numbers_temp
2387 SET group_header_id= l_txn_hdr_id
2388 WHERE transaction_temp_id in ( SELECT serial_transaction_temp_id
2389 FROM mtl_transaction_lots_temp
2390 WHERE transaction_temp_id= p_transaction_temp_id );
2391 ELSE --Non-Lot item
2392
2393 OPEN c_fm_to_serial_number;
2394 LOOP
2395 FETCH c_fm_to_serial_number
2396 INTO l_fm_serial_number,l_to_serial_number;
2397 EXIT WHEN c_fm_to_serial_number%NOTFOUND;
2398
2399 UPDATE MTL_SERIAL_NUMBERS msn
2400 SET GROUP_MARK_ID=l_txn_hdr_id
2401 WHERE msn.current_organization_id=p_organization_id
2402 AND msn.inventory_item_id= l_item_id
2403 AND msn.SERIAL_NUMBER BETWEEN l_fm_serial_number AND
2404 l_to_serial_number;
2405 END LOOP;
2406 CLOSE c_fm_to_serial_number;
2407
2408 UPDATE mtl_serial_numbers_temp
2409 SET group_header_id= l_txn_hdr_id
2410 WHERE transaction_temp_id=p_transaction_temp_id ;
2411
2412 END IF;
2413
2414 IF (l_debug = 1) THEN
2415 mydebug('manual_pick: Updated MSNT', 'MANUAL_PICK');
2416 END IF;
2417
2418 EXCEPTION
2419 WHEN OTHERS THEN
2420 IF (l_debug = 1) THEN
2421 mydebug('manual_pick:EXCEPTION!!! while updating MSNT', 'MANUAL_PICK');
2422 END IF;
2423 raise fnd_api.g_exc_error;
2424 END ;
2425 END IF;
2426 --Bug6009436.End
2427
2428 -- Insert into WMS_DISPATCHED_TASKS for this user
2429 INSERT INTO wms_dispatched_tasks
2430 (task_id,
2431 transaction_temp_id,
2432 organization_id,
2433 user_task_type,
2434 person_id,
2435 effective_start_date,
2436 effective_end_date,
2437 equipment_id,
2438 equipment_instance,
2439 person_resource_id,
2440 machine_resource_id,
2441 status,
2442 dispatched_time,
2443 last_update_date,
2444 last_updated_by,
2445 creation_date,
2446 created_by,
2447 task_type,
2448 operation_plan_id,
2449 move_order_line_id)
2450 VALUES
2451 (wms_dispatched_tasks_s.NEXTVAL,
2452 p_transaction_temp_id,
2453 p_organization_id,
2454 l_standard_operation_id,
2455 p_employee_id,
2456 p_effective_start_date,
2457 p_effective_end_date,
2458 p_equipment_id,
2459 l_equipment_serial,
2460 l_person_resource_id,
2461 l_machine_resource_id,
2462 3, -- Dispatched
2463 SYSDATE,
2464 SYSDATE,
2465 l_user_id,
2466 SYSDATE,
2467 l_user_id,
2468 x_task_type_id,
2469 l_operation_plan_id,
2470 l_move_order_line_id);
2471
2472 IF (l_debug = 1) THEN
2473 mydebug('MANUAL_PICK: After Insert into WDT', 'MANUAL_PICK');
2474 END IF;
2475 END IF;
2476 END IF;
2477 END IF; --mmtt rowcount if
2478
2479 x_return_status := fnd_api.g_ret_sts_success;
2480 EXCEPTION
2481 WHEN fnd_api.g_exc_error THEN
2482 x_return_status := fnd_api.g_ret_sts_error;
2483 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2484 WHEN OTHERS THEN
2485 x_return_status := fnd_api.g_ret_sts_unexp_error;
2486 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2487 END manual_pick;
2488
2489
2490 PROCEDURE get_next_task_in_group
2491 (p_employee_id IN NUMBER,
2492 p_organization_id IN NUMBER,
2493 p_subinventory_code IN VARCHAR2,
2494 p_device_id IN NUMBER,
2495 p_grouping_document_type IN VARCHAR2,
2496 p_grouping_document_number IN NUMBER,
2497 p_grouping_source_type_id IN NUMBER,
2498 x_task_id OUT nocopy NUMBER,
2499 x_transaction_temp_id OUT nocopy NUMBER,
2500 x_task_type_id OUT nocopy NUMBER,
2501 x_return_status OUT nocopy VARCHAR2,
2502 x_msg_data OUT nocopy VARCHAR2,
2503 x_msg_count OUT nocopy NUMBER)
2504 IS
2505 BEGIN
2506 IF (g_trace_on = 1) THEN
2507 mydebug('Inside get_next_task_in_group', 'GET_NEXT_TASK_IN_GROUP');
2508 mydebug('Device ID: ' || p_device_id, 'GET_NEXT_TASK_IN_GROUP');
2509 mydebug('Grouping Document Type: ' || p_grouping_document_type, 'GET_NEXT_TASK_IN_GROUP');
2510 mydebug('Grouping Document Number: ' || p_grouping_document_number, 'GET_NEXT_TASK_IN_GROUP');
2511 mydebug('Grouping Source Type ID: ' || p_grouping_source_type_id, 'GET_NEXT_TASK_IN_GROUP');
2512 END IF;
2513
2514 BEGIN
2515 g_group_sequence_number := g_group_sequence_number + 1;
2516
2517 IF (g_trace_on = 1) THEN
2518 mydebug('Looking for task with Group Sequence Number: ' || g_group_sequence_number, 'GET_NEXT_TASK_IN_GROUP');
2519 END IF;
2520
2521 IF p_grouping_document_type = 'PICK_SLIP' THEN
2522
2523 SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2524 INTO x_task_id, x_transaction_temp_id, x_task_type_id
2525 FROM wms_dispatched_tasks wdt,
2526 mtl_material_transactions_temp mmtt
2527 WHERE wdt.person_id = p_employee_id
2528 AND wdt.organization_id = p_organization_id
2529 AND wdt.task_type in (1,4,5,6) -- Picking,Replenishment bug#8770642 BUG13985098
2530 AND wdt.status = 3
2531 AND Nvl(wdt.device_id, -1) = Nvl(p_device_id, -1)
2532 AND Decode(wdt.device_id, NULL, 'Y', wdt.device_invoked) = 'Y'
2533 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
2534 AND Decode(p_subinventory_code, NULL, mmtt.subinventory_code, p_subinventory_code) = mmtt.subinventory_code
2535 AND mmtt.pick_slip_number = p_grouping_document_number
2536 AND wdt.task_group_id = g_group_sequence_number;
2537
2538 ELSIF p_grouping_document_type = 'ORDER' THEN
2539
2540 SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2541 INTO x_task_id, x_transaction_temp_id, x_task_type_id
2542 FROM wms_dispatched_tasks wdt,
2543 mtl_material_transactions_temp mmtt
2544 WHERE wdt.person_id = p_employee_id
2545 AND wdt.organization_id = p_organization_id
2546 AND wdt.task_type = 1 -- Picking
2547 AND wdt.status = 3
2548 AND Nvl(wdt.device_id, -1) = Nvl(p_device_id, -1)
2549 AND Decode(wdt.device_id, NULL, 'Y', wdt.device_invoked) = 'Y'
2550 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
2551 AND Decode(p_subinventory_code, NULL, mmtt.subinventory_code, p_subinventory_code) = mmtt.subinventory_code
2552 AND mmtt.transaction_source_type_id = p_grouping_source_type_id
2553 AND mmtt.transaction_source_id = p_grouping_document_number
2554 AND wdt.task_group_id = g_group_sequence_number;
2555
2556 ELSIF p_grouping_document_type = 'CARTON' THEN
2557 /* bug9507152 */
2558 SELECT task_id, transaction_temp_id, task_type
2559 INTO x_task_id, x_transaction_temp_id, x_task_type_id
2560 FROM (SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2561 FROM wms_dispatched_tasks wdt,
2562 mtl_material_transactions_temp mmtt,
2563 mtl_secondary_inventories msi,
2564 mtl_item_locations mil
2565 WHERE wdt.person_id = p_employee_id
2566 AND wdt.organization_id = p_organization_id
2567 AND wdt.task_type = 1 -- Picking
2568 AND wdt.status = 3
2569 AND Nvl(wdt.device_id, -1) = Nvl(p_device_id, -1)
2570 AND Decode(wdt.device_id, NULL, 'Y', wdt.device_invoked) = 'Y'
2571 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
2572 AND Decode(p_subinventory_code, NULL, mmtt.subinventory_code, p_subinventory_code) = mmtt.subinventory_code
2573 AND mmtt.cartonization_id = p_grouping_document_number
2574 --AND wdt.task_group_id = g_group_sequence_number;
2575 AND mmtt.subinventory_code = msi.secondary_inventory_name
2576 AND mmtt.organization_id = msi.organization_id
2577 AND mmtt.locator_id = mil.inventory_location_id
2578 AND mmtt.organization_id = mil.organization_id
2579 ORDER BY wdt.priority DESC, msi.picking_order, mil.picking_order,wdt.task_id
2580 )
2581 WHERE ROWNUM < 2;
2582 /* bug9507152 */
2583 --Bug: 7254397 added for ClusterPickByLabel
2584 ELSIF p_grouping_document_type = 'CLUSTERPICKBYLABEL' THEN
2585
2586 SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2587 INTO x_task_id, x_transaction_temp_id, x_task_type_id
2588 FROM wms_dispatched_tasks wdt,
2589 mtl_material_transactions_temp mmtt
2590 WHERE wdt.person_id = p_employee_id
2591 AND wdt.organization_id = p_organization_id
2592 AND wdt.task_type = 1 -- Picking
2593 AND wdt.status = 3
2594 AND Nvl(wdt.device_id, -1) = Nvl(p_device_id, -1)
2595 AND Decode(wdt.device_id, NULL, 'Y', wdt.device_invoked) = 'Y'
2596 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
2597 AND Decode(p_subinventory_code, NULL, mmtt.subinventory_code, p_subinventory_code) = mmtt.subinventory_code
2598 --AND mmtt.cartonization_id = p_grouping_document_number
2599 AND mmtt.cartonization_id IN (SELECT * FROM TABLE(list_cartonization_id))
2600 AND wdt.task_group_id = g_group_sequence_number;
2601 ELSIF p_grouping_document_type = 'CLUSTER' THEN -- for cluster picking
2602
2603 SELECT task_id, transaction_temp_id, task_type
2604 INTO x_task_id, x_transaction_temp_id, x_task_type_id
2605 FROM (select wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2606 FROM wms_dispatched_tasks wdt,
2607 mtl_material_transactions_temp mmtt,
2608 mtl_secondary_inventories msi,
2609 mtl_item_locations mil
2610 WHERE wdt.person_id = p_employee_id
2611 AND wdt.organization_id = p_organization_id
2612 AND wdt.task_type = 1 -- Picking
2613 AND wdt.status = 3
2614 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
2615 AND Decode(p_subinventory_code, NULL, mmtt.subinventory_code,
2616 p_subinventory_code) = mmtt.subinventory_code
2617 AND mmtt.subinventory_code = msi.secondary_inventory_name
2618 AND mmtt.organization_id = msi.organization_id
2619 AND mmtt.locator_id = mil.inventory_location_id
2620 AND mmtt.organization_id = mil.organization_id
2621 AND wdt.task_method = 'CLUSTER'
2622 ORDER BY msi.picking_order, mil.picking_order, wdt.priority, wdt.status, wdt.task_id
2623 )
2624 WHERE rownum <2;
2625 --Added for Case Picking Project start
2626 ELSIF ( p_grouping_document_type = 'MANIFESTORDER' ) THEN
2627
2628 IF (g_trace_on = 1) THEN
2629 mydebug('MANIFESTORDER ' || g_group_sequence_number, 'GET_NEXT_TASK_IN_GROUP');
2630 END IF;
2631
2632 SELECT task_id ,
2633 transaction_temp_id,
2634 task_type
2635 INTO x_task_id ,
2636 x_transaction_temp_id,
2637 x_task_type_id
2638 FROM (
2639 SELECT wdt.task_id ,
2640 mmtt.transaction_temp_id,
2641 wdt.task_type
2642 FROM wms_dispatched_tasks wdt ,
2643 mtl_material_transactions_temp mmtt,
2644 mtl_secondary_inventories msi ,
2645 mtl_item_locations mil
2646 WHERE wdt.person_id = p_employee_id
2647 AND wdt.organization_id = p_organization_id
2648 AND wdt.task_type = 1 -- Picking
2649 AND wdt.status = 3
2650 AND NVL(wdt.device_id, -1) = NVL(p_device_id, -1)
2651 AND DECODE(wdt.device_id, NULL, 'Y', wdt.device_invoked) ='Y'
2652 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
2653 AND DECODE(p_subinventory_code, NULL, mmtt.subinventory_code,p_subinventory_code) = mmtt.subinventory_code
2654 AND mmtt.subinventory_code = msi.secondary_inventory_name
2655 AND mmtt.organization_id = msi.organization_id
2656 AND mmtt.locator_id = mil.inventory_location_id
2657 AND mmtt.organization_id = mil.organization_id
2658 AND wdt.task_method = 'MANIFESTORDER'
2659 AND mmtt.transaction_source_type_id = p_grouping_source_type_id
2660 AND mmtt.transaction_source_id IN ( SELECT sales_order_id FROM mtl_sales_orders WHERE segment1 IN (SELECT * FROM TABLE(list_order_numbers)) ) --Modified for bug 13644087,previously comparing transaction source id with order number
2661 AND wdt.task_group_id = g_group_sequence_number
2662 AND mmtt.parent_line_id IS NULL -- Added for bulk task
2663 ORDER BY msi.picking_order, mil.picking_order, wdt.priority , wdt.status , wdt.task_id
2664 )
2665 WHERE rownum <2;
2666
2667 IF (g_trace_on = 1) THEN
2668 mydebug('MANIFESTORDER x_task_id' || x_task_id, 'GET_NEXT_TASK_IN_GROUP');
2669 mydebug('MANIFESTORDER x_transaction_temp_id' || x_transaction_temp_id, 'GET_NEXT_TASK_IN_GROUP');
2670 mydebug('MANIFESTORDER x_task_type_id' || x_task_type_id, 'GET_NEXT_TASK_IN_GROUP');
2671 END IF;
2672
2673 ELSIF ( p_grouping_document_type = 'MANIFESTPICKSLIP' ) THEN
2674
2675 IF (g_trace_on = 1) THEN
2676 mydebug('Looking for task with Group Sequence Number: ' || g_group_sequence_number, 'GET_NEXT_TASK_IN_GROUP');
2677 END IF;
2678
2679 SELECT task_id ,
2680 transaction_temp_id,
2681 task_type
2682 INTO x_task_id ,
2683 x_transaction_temp_id,
2684 x_task_type_id
2685 FROM (
2686 SELECT wdt.task_id ,
2687 mmtt.transaction_temp_id,
2688 wdt.task_type
2689 FROM wms_dispatched_tasks wdt ,
2690 mtl_material_transactions_temp mmtt,
2691 mtl_secondary_inventories msi ,
2692 mtl_item_locations mil
2693 WHERE wdt.person_id = p_employee_id
2694 AND wdt.organization_id = p_organization_id
2695 AND wdt.task_type = 1 -- Picking
2696 AND wdt.status = 3
2697 AND NVL(wdt.device_id, -1) = NVL(p_device_id, -1)
2698 AND DECODE(wdt.device_id, NULL, 'Y', wdt.device_invoked) ='Y'
2699 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
2700 AND DECODE(p_subinventory_code, NULL, mmtt.subinventory_code,p_subinventory_code) = mmtt.subinventory_code
2701 AND mmtt.subinventory_code = msi.secondary_inventory_name
2702 AND mmtt.organization_id = msi.organization_id
2703 AND mmtt.locator_id = mil.inventory_location_id
2704 AND mmtt.organization_id = mil.organization_id
2705 AND wdt.task_method = 'MANIFESTPICKSLIP'
2706 AND mmtt.pick_slip_number IN (SELECT * FROM TABLE(list_pick_slip_numbers) )
2707 AND wdt.task_group_id = g_group_sequence_number
2708 AND mmtt.parent_line_id IS NULL -- Added for bulk task
2709 ORDER BY msi.picking_order, mil.picking_order, wdt.priority , wdt.status , wdt.task_id
2710 )
2711 WHERE rownum <2;
2712
2713 IF (g_trace_on = 1) THEN
2714 mydebug('MANIFESTPICKSLIP x_task_id' || x_task_id, 'GET_NEXT_TASK_IN_GROUP');
2715 mydebug('MANIFESTPICKSLIP x_transaction_temp_id' || x_transaction_temp_id, 'GET_NEXT_TASK_IN_GROUP');
2716 mydebug('MANIFESTPICKSLIP x_task_type_id' || x_task_type_id, 'GET_NEXT_TASK_IN_GROUP');
2717 END IF;
2718 --Added for Case Picking Project start
2719
2720 END IF;
2721 EXCEPTION
2722 WHEN no_data_found THEN
2723 NULL;
2724 END;
2725 END get_next_task_in_group;
2726
2727 PROCEDURE next_task
2728 (p_employee_id IN NUMBER,
2729 p_effective_start_date IN DATE,
2730 p_effective_end_date IN DATE,
2731 p_organization_id IN NUMBER,
2732 p_subinventory_code IN VARCHAR2,
2733 p_equipment_id IN NUMBER,
2734 p_equipment_serial IN VARCHAR2,
2735 p_number_of_devices IN NUMBER,
2736 p_device_id IN NUMBER,
2737 p_task_filter IN VARCHAR2,
2738 p_task_method IN VARCHAR2,
2739 p_prioritize_dispatched_tasks IN VARCHAR2 := 'N', -- 4560814
2740 p_retain_dispatch_task IN VARCHAR2 := 'N', -- 4560814
2741 p_allow_unreleased_task IN VARCHAR2 :='Y', -- for manual picking only bug 4718145
2742 p_max_clusters IN NUMBER := null, -- added for cluster picking
2743 p_dispatch_needed IN VARCHAR2 := 'Y', -- added for cluster picking
2744 x_grouping_document_type IN OUT nocopy VARCHAR2,
2745 x_grouping_document_number IN OUT nocopy NUMBER,
2746 x_grouping_source_type_id IN OUT nocopy NUMBER,
2747 x_is_changed_group IN OUT nocopy VARCHAR2,
2748 x_task_info OUT nocopy t_genref,
2749 x_task_number OUT nocopy NUMBER,
2750 x_num_of_tasks OUT nocopy NUMBER,
2751 x_task_type_id OUT nocopy NUMBER,
2752 x_avail_device_id OUT nocopy NUMBER,
2753 x_device_request_id OUT nocopy NUMBER,
2754 x_return_status OUT nocopy VARCHAR2,
2755 x_msg_count OUT nocopy NUMBER,
2756 x_msg_data OUT nocopy VARCHAR2)
2757 IS
2758 l_cartonization_id NUMBER;
2759 l_device_id NUMBER := p_device_id;
2760 l_equipment_id NUMBER := p_equipment_id;
2761 l_equipment_serial WMS_DISPATCHED_TASKS.EQUIPMENT_INSTANCE%TYPE;
2762 l_subinventory_code VARCHAR2(10) := p_subinventory_code;
2763 l_assignment_temp_id NUMBER;
2764 l_loop_device_id NUMBER;
2765
2766 l_task_id NUMBER;
2767 l_transaction_temp_id NUMBER;
2768 l_next_transaction_temp_id NUMBER;
2769 l_device_invoked VARCHAR2(1);
2770
2771 task_record task_record_type;
2772 l_task_cursor task_cursor_type;
2773
2774 l_request_msg VARCHAR2(200);
2775
2776 l_error_code NUMBER;
2777
2778 l_task_from_group BOOLEAN := FALSE;
2779 l_need_dispatch BOOLEAN := TRUE;
2780 l_invoked_device_id NUMBER;
2781 l_first_task VARCHAR2(1);
2782
2783 l_open_past_period BOOLEAN;
2784 l_request_id NUMBER;
2785
2786 l_group_sequence_number NUMBER;
2787 l_deliveries_list VARCHAR2(2000);
2788 l_cartons_list VARCHAR2(2000);
2789 l_user_id NUMBER := fnd_global.user_id;
2790 l_count NUMBER := 0;
2791 I NUMBER :=0;
2792 J NUMBER :=NULL;
2793
2794 -- Following variables were added for bug fix 4507435
2795 l_task_method_wdt VARCHAR2(15);
2796 l_count_wdt NUMBER;
2797 l_cluster_size_wdt NUMBER;
2798 l_return_status VARCHAR2(1);
2799 l_pick_slip_number NUMBER;
2800 l_is_manifest_pick VARCHAR2(1); --Added for Case Picking Project
2801 l_is_cluster_pick VARCHAR2(1);
2802
2803
2804 CURSOR following_device_list(p_emp_id NUMBER, p_current_device_temp_id NUMBER) IS
2805 SELECT wdat.device_id,
2806 wdat.assignment_temp_id,
2807 wdb.subinventory_code
2808 FROM wms_device_assignment_temp wdat, wms_devices_b wdb
2809 WHERE wdat.assignment_temp_id >= p_current_device_temp_id
2810 AND wdat.employee_id = p_emp_id
2811 AND wdb.device_type_id <> 100
2812 AND wdat.device_id = wdb.device_id
2813 ORDER BY wdat.assignment_temp_id;
2814
2815 CURSOR preceding_device_list(p_emp_id NUMBER, p_current_device_temp_id NUMBER) IS
2816 SELECT wdat.device_id,
2817 wdat.assignment_temp_id,
2818 wdb.subinventory_code
2819 FROM wms_device_assignment_temp wdat, wms_devices_b wdb
2820 WHERE wdat.assignment_temp_id < p_current_device_temp_id
2821 AND wdat.employee_id = p_emp_id
2822 AND wdb.device_type_id <> 100
2823 AND wdat.device_id = wdb.device_id
2824 ORDER BY wdat.assignment_temp_id;
2825
2826 -- Following cursor is for 4507435, to count the dispatched cluster size
2827 CURSOR cluster_size_wdt IS SELECT mmtt.transaction_source_id, count (*)
2828 FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
2829 WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
2830 AND wdt.status = 3
2831 AND wdt.person_id = p_employee_id
2832 AND wdt.organization_id = p_organization_id
2833 AND wdt.task_method = 'CLUSTER'
2834 GROUP BY mmtt.transaction_source_id;
2835
2836 --Bug#5188179.Cursor for task filter
2837 CURSOR c_task_filter(v_filter_name VARCHAR2) IS
2838 SELECT task_filter_source, task_filter_value
2839 FROM wms_task_filter_b wtf, wms_task_filter_dtl wtfd
2840 WHERE task_filter_name = v_filter_name
2841 AND wtf.task_filter_id = wtfd.task_filter_id;
2842
2843 l_task_status NUMBER; -- bug 4310093
2844
2845 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2846 l_api_name VARCHAR2(30) := 'WMS_PICKING_PKG.NEXT_TASK';
2847 --
2848 -- Bug 4722574
2849 --
2850 l_ignore_equipment NUMBER;
2851 --
2852 l_max_seq_number NUMBER :=0; -- Bug#5185031
2853
2854 --Bug#5188179
2855 l_so_allowed NUMBER := 0;
2856 l_io_allowed NUMBER := 0;
2857 l_wip_allowed NUMBER := 0;
2858 l_mot_allowed NUMBER := 0;
2859 l_rep_allowed NUMBER := 0;
2860 l_moi_allowed NUMBER := 0;
2861 --Bug#5188179
2862
2863 l_wdt_count NUMBER; -- Bug# 5599049
2864
2865 --Bug#8322661
2866 l_dup_task BOOLEAN := FALSE;
2867 l_first_task_id NUMBER;
2868 l_task_type_id NUMBER;
2869 BEGIN
2870
2871 IF (l_debug = 1) THEN
2872 mydebug('- - - - - - - - -', l_api_name);
2873 END IF;
2874
2875 -- Establish a savepoint
2876 SAVEPOINT next_task_sp;
2877
2878 x_return_status := fnd_api.g_ret_sts_success;
2879
2880 IF (l_debug = 1) THEN
2881 mydebug('Need to check if the period is open', l_api_name);
2882 END IF;
2883
2884 -- Clear the message stack
2885 fnd_msg_pub.delete_msg;
2886
2887 -- Check if the period is open
2888 -- TODO: Cache this
2889 IF g_period_id IS NULL THEN
2890 invttmtx.tdatechk(org_id => p_organization_id,
2891 transaction_date => SYSDATE,
2892 period_id => g_period_id,
2893 open_past_period => l_open_past_period);
2894 END IF;
2895
2896 IF g_period_id <= 0 THEN
2897 IF (l_debug = 1) THEN
2898 mydebug('Period is invalid', l_api_name);
2899 END IF;
2900
2901 fnd_message.set_name('INV', 'INV_NO_OPEN_PERIOD');
2902 fnd_msg_pub.ADD;
2903 RAISE fnd_api.g_exc_unexpected_error;
2904 END IF;
2905
2906 -- Ignore devices if multiple device signon and task methods not in
2907 -- discrete/wave
2908 IF p_number_of_devices > 1 AND p_task_method NOT IN ('DISCRETE', 'WAVE') THEN
2909 l_device_id := NULL;
2910 END IF;
2911
2912 IF l_device_id = 0 THEN
2913 l_device_id := NULL;
2914 END IF;
2915
2916 IF l_equipment_id = 0 THEN
2917 l_equipment_id := NULL;
2918 END IF;
2919
2920 -- Bug #4090630 - passing NONE to the task dispatching engine if
2921 -- p_equipment_serial is NULL
2922 l_equipment_serial := p_equipment_serial;
2923 IF (l_equipment_id is null and l_equipment_serial is null) then
2924 IF (l_debug = 1) THEN
2925 mydebug('l_equipment_id is null and l_equipment_serial is null', l_api_name);
2926 END IF;
2927 --
2928 -- Start Bug 4722574
2929 --
2930 l_ignore_equipment := NVL(fnd_profile.VALUE('WMS_IGNORE_EQUIPMENT'), 1);
2931 IF l_ignore_equipment = 2 THEN
2932 l_equipment_serial := 'NONE';
2933 END IF;
2934 --
2935 -- End Bug 4722574
2936 --
2937 END IF;
2938
2939
2940 IF x_grouping_document_type IS NULL THEN
2941 IF p_task_method = 'DISCRETE' THEN
2942 x_grouping_document_type := 'PICK_SLIP';
2943 ELSIF p_task_method = 'ORDERPICK' THEN
2944 x_grouping_document_type := 'ORDER';
2945 ELSIF p_task_method = 'PICKBYLABEL' THEN
2946 x_grouping_document_type := 'CARTON';
2947 ELSIF p_task_method = 'CLUSTER' THEN
2948 x_grouping_document_type := 'CLUSTER'; -- for cluster picking
2949 ELSIF p_task_method = 'MANUAL' THEN
2950 x_grouping_document_type := 'TRANSACTION_TEMP_ID';
2951 END IF;
2952 END IF;
2953
2954 IF x_grouping_document_number = 0 THEN
2955 x_grouping_document_number := NULL;
2956 END IF;
2957
2958 IF x_grouping_document_type = 'CARTON' THEN
2959 l_cartonization_id := x_grouping_document_number;
2960 ELSIF x_grouping_document_type = 'CYCLE_COUNT' THEN
2961 x_grouping_document_number := NULL;
2962 END IF;
2963
2964 x_num_of_tasks := 0;
2965
2966 IF (l_debug = 1) THEN
2967 mydebug('Device ID: ' || l_device_id, l_api_name);
2968 mydebug('Equipment ID: ' || l_equipment_id, l_api_name);
2969 mydebug('Equipment Instance:'|| l_equipment_serial, l_api_name);
2970 mydebug('Grouping Document Type: ' || x_grouping_document_type, l_api_name);
2971 mydebug('Grouping Document Number: ' || x_grouping_document_number, l_api_name);
2972 mydebug('Task Method: ' || p_task_method, l_api_name);
2973
2974 mydebug('Current Group Sequence Number: ' || g_group_sequence_number, l_api_name);
2975 mydebug('Maximum Group Sequence Number: ' || g_max_group_sequence_number, l_api_name);
2976 END IF;
2977
2978 --viks selecting temp-ids form pl/sql table for the split task when start_over
2979 --button is pressed
2980
2981 l_count := g_start_over_tempid.COUNT;
2982
2983 IF l_count > 0 THEN
2984
2985
2986 IF (l_debug = 1) THEN
2987 FOR I in g_start_over_tempid.FIRST .. g_start_over_tempid.LAST LOOP
2988 mydebug('Tempid in I loop : ', g_start_over_tempid(I));
2989 IF SQL%NOTFOUND THEN
2990 mydebug ('Tempid not found ',I );
2991 END IF;
2992 END LOOP;
2993 END IF;
2994
2995
2996 l_next_transaction_temp_id := g_start_over_tempid(g_start_over_tempid.FIRST);
2997 IF (l_debug = 1) THEN
2998 mydebug('Tempid count ' || l_count ,l_next_transaction_temp_id);
2999 mydebug ('First Index is ' , g_start_over_tempid.FIRST);
3000 END IF;
3001
3002 UPDATE mtl_material_transactions_temp
3003 SET transaction_header_id = mtl_material_transactions_s.NEXTVAL,
3004 last_update_date = Sysdate,
3005 last_updated_by = l_user_id
3006 WHERE transaction_temp_id = l_next_transaction_temp_id
3007 returning wms_task_type INTO x_task_type_id;
3008 IF (l_debug = 1) THEN
3009 mydebug('Tempid in table to be deleted viks : ', l_next_transaction_temp_id );
3010 END IF;
3011
3012 g_start_over_tempid.DELETE(g_start_over_tempid.FIRST);
3013
3014 END IF; --viks changes end
3015
3016 -- BugFix #4507435
3017 IF(p_prioritize_dispatched_tasks = 'Y') THEN -- default 'N'
3018
3019 IF (l_debug = 1) THEN
3020 mydebug('Before fetching from MMTT...','');
3021 mydebug('PRIORITIZE_DISPATCHED_TASK: ' || p_prioritize_dispatched_tasks, '');
3022 mydebug('GROUPING DOCUMENT NUMBER: '|| x_grouping_document_number,'');
3023 mydebug('GROUPING SOURCE TYPE ID: '|| x_grouping_source_type_id,'');
3024 END IF;
3025 BEGIN
3026
3027 --Bug#5188179.The dispatched tasks should be filtered as per task_filter.
3028 FOR task_filter_rec IN c_task_filter(p_task_filter) LOOP
3029 IF (l_debug = 1) THEN
3030 mydebug('Task Filter Source: ' || task_filter_rec.task_filter_source, '');
3031 mydebug('Task Filter Value: ' || task_filter_rec.task_filter_value, '');
3032 END IF;
3033
3034 IF task_filter_rec.task_filter_value = 'Y' THEN
3035 IF task_filter_rec.task_filter_source = 1 THEN -- Internal Order
3036 l_io_allowed := 1;
3037 ELSIF task_filter_rec.task_filter_source = 2 THEN -- Move Order Issue
3038 l_moi_allowed := 1;
3039 ELSIF task_filter_rec.task_filter_source = 3 THEN -- Move Order Transfer
3040 l_mot_allowed := 1;
3041 ELSIF task_filter_rec.task_filter_source = 4 THEN -- Replenishment
3042 l_rep_allowed := 1;
3043 ELSIF task_filter_rec.task_filter_source = 5 THEN -- Sales Order
3044 l_so_allowed := 1;
3045 ELSIF task_filter_rec.task_filter_source = 6 THEN -- Work Order
3046 l_wip_allowed := 1;
3047 END IF;
3048 END IF;
3049 END LOOP; --Bug#5188179.End of fix.
3050
3051
3052 -- Bug#5185031 Fetched the value for l_max_seq_number.
3053 select wdt.task_method, count(*), max(wdt.task_group_id)
3054 into l_task_method_wdt, l_count_wdt ,l_max_seq_number
3055 from wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
3056 where mmtt.transaction_temp_id = wdt.transaction_temp_id
3057 and wdt.status = 3
3058 and wdt.person_id = p_employee_id
3059 and wdt.organization_id = p_organization_id
3060 and Decode(mmtt.transaction_source_type_id, /*Bug5188179. Apply task filter*/
3061 2, l_so_allowed,
3062 4, Decode(mmtt.transaction_action_id, 1, l_moi_allowed, 2, decode(wms_task_type, 4, l_rep_allowed, l_mot_allowed)),
3063 5, Decode(mmtt.transaction_type_id, 35, l_wip_allowed),
3064 8, l_io_allowed,
3065 13, Decode(mmtt.transaction_type_id,
3066 51, l_wip_allowed,
3067 Decode(mmtt.transaction_action_id, 2, decode(wms_task_type, 4, l_rep_allowed)))) = 1
3068
3069 group by wdt.task_method;
3070
3071 IF (l_debug = 1) THEN
3072 mydebug(l_task_method_wdt|| ' : ' || l_count_wdt,'');
3073 END IF;
3074
3075 IF l_count_wdt > 0 AND p_task_method <> l_task_method_wdt THEN
3076 mydebug('Dispatched tasks need be completed first. Use the picking method used before','');
3077 x_return_status := 'E';
3078 x_msg_data := l_task_method_wdt;
3079 RETURN;
3080 END IF;
3081 EXCEPTION WHEN NO_DATA_FOUND THEN
3082 mydebug('No Tasks in WDT','');
3083 END;
3084
3085 IF l_task_method_wdt = 'CLUSTER' THEN
3086 l_cluster_size_wdt := 0;
3087 for rec in cluster_size_wdt loop
3088 l_cluster_size_wdt := l_cluster_size_wdt + 1;
3089 end loop;
3090 IF l_cluster_size_wdt <> p_max_clusters THEN
3091 mydebug('Dispatched cluster size and input cluster size doesnt match','');
3092 l_return_status := 'W';
3093 x_msg_data := l_cluster_size_wdt;
3094 ELSE
3095 mydebug('Dispatched cluster size and input cluster size matches','');
3096 END IF;
3097 END IF;
3098 -- check if any dispatched tasks in WDT
3099 -- fetch the document number, source type id
3100 IF l_count_wdt > 0 AND x_grouping_source_type_id IS NULL AND ( x_grouping_document_number IS NULL OR
3101 ( x_grouping_document_number IS NOT NULL AND x_grouping_document_type = 'CARTON') ) THEN
3102 -- Bug 4597257, Changed the condition since for cartonized tasks, the grouping_document_number wont be null
3103 BEGIN
3104
3105
3106 -- bug 5266450
3107 -- Change select pending tasks as sub-query with order by first
3108 -- Move rownum clause out of the order by query to ensure the lowerest
3109 -- row is selected from the pending tasks
3110 --
3111 -- bug 5094839
3112 -- Restore task_goup_id in the select statement to
3113 -- (task_group_id - 1) for getting the correct task_group_id
3114
3115 select transaction_source_id, transaction_source_type_id, device_id,
3116 (task_group_id - 1), pick_slip_number, cartonization_id
3117
3118 INTO x_grouping_document_number, x_grouping_source_type_id, l_device_id,
3119 g_group_sequence_number, l_pick_slip_number, l_cartonization_id
3120
3121 From
3122 ( select mmtt.transaction_source_id, mmtt.transaction_source_type_id, wdt.device_id,
3123 wdt.task_group_id, mmtt.pick_slip_number, mmtt.cartonization_id
3124
3125 from mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
3126 where mmtt.transaction_temp_id = wdt.transaction_temp_id
3127 and wdt.status = 3
3128 and wdt.person_id = p_employee_id
3129 and wdt.organization_id = p_organization_id
3130 and Decode(mmtt.transaction_source_type_id, /*Bug5188179. Apply task filter*/
3131 2, l_so_allowed,
3132 4, Decode(mmtt.transaction_action_id, 1, l_moi_allowed, 2, decode(wms_task_type, 4, l_rep_allowed, l_mot_allowed)),
3133 5, Decode(mmtt.transaction_type_id, 35, l_wip_allowed),
3134 8, l_io_allowed,
3135 13, Decode(mmtt.transaction_type_id,
3136 51, l_wip_allowed,
3137 Decode(mmtt.transaction_action_id, 2, decode(wms_task_type, 4, l_rep_allowed)))) = 1
3138 order by wdt.task_group_id, wdt.transaction_temp_id )-- 4584860
3139 Where rownum = 1; -- bug 5264450
3140
3141
3142 IF x_grouping_document_type = 'PICK_SLIP' THEN -- Added this for 4580273
3143 x_grouping_document_number := l_pick_slip_number;
3144 ELSIF x_grouping_document_type = 'CARTON' THEN
3145 x_grouping_document_number := l_cartonization_id;
3146 END IF;
3147
3148 IF (l_debug = 1) THEN
3149 mydebug('After fetching from MMTT...','');
3150 mydebug('PRIORITIZE_DISPATCHED_TASK: ' || p_prioritize_dispatched_tasks, '');
3151 mydebug('GROUPING DOCUMENT NUMBER: '|| x_grouping_document_number,'');
3152 mydebug('GROUPING SOURCE TYPE ID: '|| x_grouping_source_type_id,'');
3153 END IF;
3154 -- If the above select doesnt return any rows, just fetch it from MMTT as usual
3155 EXCEPTION WHEN NO_DATA_FOUND THEN
3156 mydebug('No Task in WDT, so fetching from MMTT: ','');
3157 END;
3158 END IF;
3159 END IF;
3160 -- End of Code #4507435
3161
3162 --Bug#5185031 Added the IF block.
3163 IF g_max_group_sequence_number <= 0 THEN
3164 g_max_group_sequence_number := l_max_seq_number;
3165 END IF;
3166
3167 -- Get next task in group
3168
3169 IF (l_next_transaction_temp_id IS NULL) THEN
3170
3171 IF (p_task_method NOT IN ('MANUAL') AND
3172 x_grouping_document_number IS NOT NULL AND
3173 (g_group_sequence_number <= g_max_group_sequence_number) AND
3174 (p_number_of_devices <= 1 OR l_device_id IS null))
3175 OR (p_task_method = 'CLUSTER' AND p_dispatch_needed = 'N') -- cluster picking
3176 OR (p_task_method = 'CLUSTERPICKBYLABEL') -- cluster pick by label
3177 THEN
3178 get_next_task_in_group
3179 (p_employee_id => p_employee_id,
3180 p_organization_id => p_organization_id,
3181 p_subinventory_code => p_subinventory_code,
3182 p_device_id => l_device_id,
3183 p_grouping_document_type => x_grouping_document_type,
3184 p_grouping_document_number => x_grouping_document_number,
3185 p_grouping_source_type_id => x_grouping_source_type_id,
3186 x_task_id => l_task_id,
3187 x_transaction_temp_id => l_next_transaction_temp_id,
3188 x_task_type_id => x_task_type_id,
3189 x_return_status => x_return_status,
3190 x_msg_data => x_msg_data,
3191 x_msg_count => x_msg_count);
3192
3193 IF (l_debug = 1) THEN
3194 mydebug('Return Status from get_next_task_in_group: ' || x_return_status,l_api_name);
3195 END IF;
3196
3197 IF x_return_status = 'U' THEN
3198 RAISE fnd_api.g_exc_unexpected_error;
3199 ELSIF x_return_status = 'E' THEN
3200 RAISE fnd_api.g_exc_error;
3201 END IF;
3202
3203 IF l_next_transaction_temp_id IS NOT NULL THEN
3204 x_num_of_tasks := 1;
3205 l_task_from_group := TRUE;
3206 END IF;
3207
3208 ELSIF p_task_method = 'MANUAL' THEN
3209 manual_pick
3210 (p_employee_id => p_employee_id,
3211 p_effective_start_date => p_effective_start_date,
3212 p_effective_end_date => p_effective_end_date,
3213 p_organization_id => p_organization_id,
3214 p_subinventory_code => p_subinventory_code,
3215 p_equipment_id => p_equipment_id,
3216 p_equipment_serial => l_equipment_serial,
3217 p_transaction_temp_id => x_grouping_document_number,
3218 p_allow_unreleased_task => p_allow_unreleased_task,
3219 x_task_type_id => x_task_type_id,
3220 x_return_status => x_return_status,
3221 x_msg_count => x_msg_count,
3222 x_msg_data => x_msg_data);
3223
3224 IF (l_debug = 1) THEN
3225 mydebug('Return Status from manual_pick: ' || x_return_status,l_api_name);
3226 END IF;
3227
3228 IF x_return_status = 'U' THEN
3229 RAISE fnd_api.g_exc_unexpected_error;
3230 ELSIF x_return_status = 'E' THEN
3231 RAISE fnd_api.g_exc_error;
3232 END IF;
3233
3234 l_next_transaction_temp_id := x_grouping_document_number;
3235 x_num_of_tasks := 1;
3236
3237 g_max_group_sequence_number := 1;
3238 g_group_sequence_number := 1;
3239 END IF;
3240 END IF;
3241
3242 IF g_group_sequence_number > g_max_group_sequence_number THEN
3243 x_is_changed_group := 'Y';
3244 ELSE
3245 x_is_changed_group := 'N';
3246 END IF;
3247
3248 IF (l_debug = 1) THEN
3249 mydebug('Next Transaction Temp ID: ' || l_next_transaction_temp_id, l_api_name);
3250 END IF;
3251
3252 IF l_next_transaction_temp_id IS NULL and (p_dispatch_needed IS NULL
3253 OR p_dispatch_needed <>'N') THEN
3254
3255 x_is_changed_group := 'Y';
3256
3257 IF l_device_id IS NOT NULL THEN
3258 -- Get the subinventory and the assignment temp ID
3259 SELECT wdat.assignment_temp_id, wd.subinventory_code
3260 INTO l_assignment_temp_id, l_subinventory_code
3261 FROM wms_device_assignment_temp wdat, wms_devices_b wd
3262 WHERE wdat.device_id = l_device_id
3263 AND wdat.device_id = wd.device_id
3264 AND wdat.employee_id = p_employee_id;
3265
3266 IF p_number_of_devices <= 1 OR
3267 p_task_method IN ('DISCRETE', 'WAVE') THEN
3268 OPEN following_device_list(p_employee_id, l_assignment_temp_id);
3269 OPEN preceding_device_list(p_employee_id, l_assignment_temp_id);
3270 ELSE
3271 l_device_id := NULL;
3272 END IF;
3273 END IF;
3274
3275 IF (l_debug = 1) THEN
3276 mydebug('Device ID: ' || l_device_id, l_api_name);
3277 END IF;
3278 LOOP
3279 IF l_device_id IS NOT NULL THEN
3280
3281 <<search_device_loop>>
3282 -- loop to find the available task and check if we need to dispatch task to some devices
3283 LOOP
3284 FETCH following_device_list INTO l_loop_device_id, l_assignment_temp_id, l_subinventory_code;
3285
3286 IF (following_device_list%NOTFOUND) THEN
3287 FETCH preceding_device_list INTO l_loop_device_id, l_assignment_temp_id, l_subinventory_code;
3288
3289 IF (preceding_device_list%NOTFOUND) THEN
3290 CLOSE following_device_list;
3291 CLOSE preceding_device_list;
3292
3293 l_need_dispatch := FALSE;
3294 EXIT search_device_loop;
3295 END IF;
3296 END IF;
3297
3298 IF (l_debug = 1) THEN
3299 mydebug('Loop Device ID: ' || l_loop_device_id, l_api_name);
3300 mydebug('Subinventory: ' || l_subinventory_code, l_api_name);
3301 END IF;
3302
3303 BEGIN
3304 SELECT transaction_temp_id, task_type,
3305 device_invoked, device_request_id
3306 INTO l_transaction_temp_id, x_task_type_id,
3307 l_device_invoked, l_request_id
3308 FROM wms_dispatched_tasks
3309 WHERE person_id = p_employee_id
3310 AND organization_id = p_organization_id
3311 AND device_id = l_loop_device_id
3312 AND task_type IN(1, 3, 4, 5, 6)
3313 AND status = 3
3314 AND ROWNUM = 1
3315 ORDER BY 1;
3316
3317 IF (l_debug = 1) THEN
3318 mydebug('Transaction Temp ID: ' || l_transaction_temp_id, l_api_name);
3319 mydebug('Device Invoked: ' || l_device_invoked, l_api_name);
3320 END IF;
3321
3322 IF l_device_invoked = 'N' AND x_task_type_id <> 3 THEN
3323 wms_device_integration_pvt.device_request
3324 (p_bus_event => wms_device_integration_pvt.wms_be_pick_load,
3325 p_call_ctx => wms_device_integration_pvt.dev_req_auto,
3326 p_task_trx_id => l_transaction_temp_id,
3327 p_org_id => p_organization_id,
3328 x_request_msg => l_request_msg,
3329 x_return_status => x_return_status,
3330 x_msg_count => x_msg_count,
3331 x_msg_data => x_msg_data,
3332 p_request_id => l_request_id);
3333
3334 -- always dispatch the task whether invoking device successfully or not
3335 -- So update the table always
3336
3337 UPDATE wms_dispatched_tasks
3338 SET device_invoked = 'Y',
3339 device_request_id = l_request_id
3340 WHERE transaction_temp_id = l_transaction_temp_id;
3341
3342 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3343 IF (l_debug = 1) THEN
3344 mydebug('Failed to invoke device ' || TO_CHAR(l_loop_device_id), l_api_name);
3345 END IF;
3346 END IF;
3347 END IF;
3348
3349
3350 IF x_avail_device_id IS NULL THEN
3351 x_avail_device_id := l_loop_device_id;
3352 l_next_transaction_temp_id := l_transaction_temp_id;
3353 x_device_request_id := l_request_id;
3354 END IF;
3355
3356 EXCEPTION
3357 WHEN NO_DATA_FOUND THEN
3358 l_need_dispatch := TRUE;
3359 EXIT search_device_loop;
3360 END;
3361 END LOOP;
3362 END IF;
3363
3364 EXIT WHEN l_need_dispatch = FALSE;
3365
3366 LOOP --Bug#8322661
3367
3368 IF (l_debug = 1) THEN
3369 mydebug('Before Calling TD Engine', l_api_name);
3370 END IF;
3371
3372 -- Remove any rows from wms_ordered_tasks
3373 DELETE FROM wms_ordered_tasks;
3374
3375 IF (p_task_method = 'CLUSTER') THEN -- call for the overloaded dispatch_task
3376
3377 IF (l_debug = 1) THEN
3378 mydebug('calling task_dispatch for cluster picking',l_api_name);
3379 END IF;
3380
3381 wms_task_dispatch_engine.dispatch_task(
3382 p_api_version => 1.0
3383 , p_init_msg_list => 'F'
3384 , p_commit => NULL
3385 , p_sign_on_emp_id => p_employee_id
3386 , p_sign_on_org_id => p_organization_id
3387 , p_sign_on_zone => l_subinventory_code
3388 , p_sign_on_equipment_id => p_equipment_id
3389 , p_sign_on_equipment_srl => l_equipment_serial
3390 , p_task_type => 'ALL'
3391 , p_task_filter => p_task_filter
3392 , x_task_cur => l_task_cursor
3393 , x_return_status => x_return_status
3394 , x_msg_count => x_msg_count
3395 , x_msg_data => x_msg_data
3396 , p_cartonization_id => null
3397 , p_max_clusters => p_max_clusters
3398 , x_deliveries_list => l_deliveries_list
3399 , x_cartons_list => l_cartons_list
3400 );
3401
3402
3403 ELSE
3404
3405 IF (l_debug = 1) THEN
3406 mydebug('calling task_dispatch for non cluster picking',l_api_name);
3407 END IF;
3408
3409 wms_task_dispatch_engine.dispatch_task
3410 (p_api_version => 1.0,
3411 p_init_msg_list => 'F',
3412 p_commit => NULL,
3413 p_sign_on_emp_id => p_employee_id,
3414 p_sign_on_org_id => p_organization_id,
3415 p_sign_on_zone => l_subinventory_code,
3416 p_sign_on_equipment_id => p_equipment_id,
3417 p_sign_on_equipment_srl => l_equipment_serial,
3418 p_task_filter => p_task_filter,
3419 p_task_method => p_task_method,
3420 x_grouping_document_type => x_grouping_document_type,
3421 x_grouping_document_number => x_grouping_document_number,
3422 x_grouping_source_type_id => x_grouping_source_type_id,
3423 x_task_cur => l_task_cursor,
3424 x_return_status => x_return_status,
3425 x_msg_count => x_msg_count,
3426 x_msg_data => x_msg_data);
3427 END IF;
3428
3429 IF (l_debug = 1) THEN
3430 mydebug('Task Dispatching Engine returned: ' || x_return_status, l_api_name);
3431 END IF;
3432
3433 IF x_return_status = 'S' THEN
3434 IF (l_debug = 1) THEN
3435 mydebug('Grouping Document Type: ' || x_grouping_document_type, l_api_name);
3436 mydebug('Grouping Document Number: ' || x_grouping_document_number, l_api_name);
3437 END IF;
3438
3439 l_first_task := 'Y';
3440 l_group_sequence_number := 1;
3441 g_group_sequence_number := 1;
3442 l_dup_task := FALSE; --Bug#8322661
3443
3444 LOOP
3445 FETCH l_task_cursor INTO task_record;
3446
3447 EXIT WHEN l_task_cursor%NOTFOUND;
3448
3449 x_num_of_tasks := x_num_of_tasks + 1;
3450
3451 --Bug#5599049: We need to make sure that the task is not being performed by other users.
3452 SELECT count(1) INTO l_wdt_count
3453 FROM wms_dispatched_tasks WDT
3454 WHERE WDT.transaction_temp_id = task_record.transaction_temp_id
3455 AND WDT.person_id <> p_employee_id
3456 AND WDT.status in (3,9);
3457
3458 IF l_wdt_count > 0 then
3459 IF (l_debug = 1) THEN
3460 mydebug('ERROR...This task has been dispatched to some other user.', l_api_name);
3461 END IF;
3462
3463 fnd_message.set_name('WMS', 'WMS_TASK_LOCKED');
3464 fnd_msg_pub.ADD;
3465 RAISE fnd_api.g_exc_unexpected_error;
3466 END IF;
3467 --Bug#5599049 .Fix ends.
3468
3469 IF l_group_sequence_number = 1 AND
3470 l_next_transaction_temp_id IS NULL THEN
3471 -- bug 5368659
3472 IF (p_task_method <> 'PICKBYLABEL') THEN --9507152
3473 l_next_transaction_temp_id := task_record.transaction_temp_id;
3474 END IF;
3475
3476 --l_next_transaction_temp_id := task_record.transaction_temp_id;--9507152
3477 x_avail_device_id := l_loop_device_id;
3478 x_task_type_id := task_record.task_type;
3479
3480 IF x_task_type_id = 3 THEN
3481 x_grouping_document_number := task_record.transaction_temp_id;
3482 END IF;
3483 END IF;
3484
3485 IF (l_debug = 1) THEN
3486 mydebug('Transaction Temp ID: ' || task_record.transaction_temp_id, l_api_name);
3487 END IF;
3488
3489 l_request_id := NULL;
3490
3491 /* IF l_first_task = 'Y' AND x_task_type_id <> 3 THEN -- invoke the device --Bug#8322661 Moved below
3492 wms_device_integration_pvt.device_request
3493 (p_bus_event => wms_device_integration_pvt.wms_be_pick_load,
3494 p_call_ctx => wms_device_integration_pvt.dev_req_auto,
3495 p_task_trx_id => task_record.transaction_temp_id,
3496 p_org_id => p_organization_id,
3497 x_request_msg => l_request_msg,
3498 x_return_status => x_return_status,
3499 x_msg_count => x_msg_count,
3500 x_msg_data => x_msg_data,
3501 p_request_id => l_request_id);
3502
3503 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3504 IF (l_debug = 1) THEN
3505 mydebug('Failed to invoke device ' || TO_CHAR(l_loop_device_id), l_api_name);
3506 END IF;
3507 END IF;
3508
3509 IF x_device_request_id IS NULL THEN
3510 x_device_request_id := l_request_id;
3511 END IF;
3512
3513 END IF;
3514
3515 IF (l_debug = 1) THEN
3516 mydebug('Device Request ID: ' || l_request_id, l_api_name);
3517 END IF; */
3518
3519 UPDATE mtl_material_transactions_temp
3520 SET transaction_header_id = mtl_material_transactions_s.NEXTVAL
3521 , last_update_date = Sysdate
3522 , last_updated_by = l_user_id
3523 , posting_flag = 'Y' -- Bug4185621: this will change the parent posting flag to 'Y' for bulking picking
3524 -- If not bulking picking, this has not effect
3525 WHERE transaction_temp_id = task_record.transaction_temp_id;
3526
3527 BEGIN --bug 4310093
3528 -- Check if this record already exists in WDD
3529 -- and the task status
3530 SELECT STATUS
3531 INTO l_task_Status
3532 FROM wms_dispatched_tasks
3533 WHERE transaction_temp_id = task_record.transaction_temp_id;
3534
3535 -- Bug 4507435, if retain_dispatch_Task is null.
3536 IF ((l_task_status <> 3) OR ( l_task_status = 3 AND p_retain_dispatch_task = 'Y')) THEN
3537 mydebug('Updating Temp ID in WDT: ' || task_record.transaction_temp_id, l_api_name);
3538 UPDATE wms_dispatched_tasks
3539 SET status = 3, -- Dispatched
3540 task_group_id = l_group_sequence_number,
3541 device_id = l_loop_device_id,
3542 device_invoked = Decode(l_loop_device_id, NULL, To_char(NULL),
3543 Decode(l_first_task, 'Y', 'Y', 'N')),
3544 device_request_id = l_request_id,
3545 -- Bugfix 4101378
3546 equipment_id = p_equipment_id,
3547 equipment_instance = p_equipment_serial,
3548 machine_resource_id = task_record.machine_resource_id,
3549 -- End of code Bugfix 4101378
3550 last_update_date = Sysdate,
3551 last_updated_by = l_user_id,
3552 task_method = p_task_method,
3553 dispatched_time = Sysdate -- BUG# 9800410
3554 WHERE transaction_temp_id = task_record.transaction_temp_id;
3555
3556 g_previous_task_status(task_record.transaction_temp_id) := 2;
3557
3558 ELSE
3559 g_previous_task_status(task_record.transaction_temp_id):=1; -- dispatched to return back to pending
3560 END IF;
3561
3562 -- If the above update did not find any WDD record, insert a new record into WDD
3563 EXCEPTION --bug 4310093
3564 WHEN NO_DATA_FOUND THEN
3565
3566 g_previous_task_status(task_record.transaction_temp_id) := 1;
3567
3568 BEGIN --Bug#8322661 Kept the INSERT in a BEGIN-END block
3569
3570 -- Insert into WMS_DISPATCHED_TASKS for this user
3571 INSERT INTO wms_dispatched_tasks
3572 (task_id,
3573 transaction_temp_id,
3574 organization_id,
3575 user_task_type,
3576 person_id,
3577 effective_start_date,
3578 effective_end_date,
3579 equipment_id,
3580 equipment_instance,
3581 person_resource_id,
3582 machine_resource_id,
3583 status,
3584 dispatched_time,
3585 last_update_date,
3586 last_updated_by,
3587 creation_date,
3588 created_by,
3589 task_type,
3590 priority,
3591 operation_plan_id,
3592 move_order_line_id,
3593 device_id,
3594 device_invoked,
3595 device_request_id,
3596 task_group_id,
3597 task_method) -- add for cluster picking but others can use it too
3598 VALUES
3599 (wms_dispatched_tasks_s.NEXTVAL,
3600 task_record.transaction_temp_id,
3601 p_organization_id,
3602 NVL(task_record.standard_operation_id, 2),
3603 p_employee_id,
3604 sysdate , --task_record.effective_start_date, --bug#6409956
3605 sysdate , --task_record.effective_end_date, --bug#6409956
3606 p_equipment_id,
3607 p_equipment_serial,
3608 task_record.person_resource_id,
3609 task_record.machine_resource_id,
3610 3, -- Dispatched
3611 Sysdate,
3612 Sysdate,
3613 l_user_id,
3614 Sysdate,
3615 l_user_id,
3616 task_record.task_type,
3617 task_record.priority,
3618 task_record.operation_plan_id,
3619 task_record.move_order_line_id,
3620 l_loop_device_id,
3621 Decode(l_loop_device_id, NULL, To_char(NULL), Decode(l_first_task, 'Y', 'Y', 'N')),
3622 l_request_id,
3623 l_group_sequence_number,
3624 p_task_method); -- add for cluster picking
3625
3626 EXCEPTION
3627 WHEN DUP_VAL_ON_INDEX THEN
3628 l_dup_task := TRUE;
3629 END; --Bug#8322661
3630
3631 IF (l_debug = 1) THEN
3632 mydebug('Inserted into WDT', l_api_name);
3633 END IF;
3634 END; -- end the begin for updating and inserting to WDT4310093
3635
3636 -- Increment the group sequence number by 1
3637 l_group_sequence_number := l_group_sequence_number + 1;
3638
3639 IF x_avail_device_id IS NULL AND l_loop_device_id IS NOT NULL THEN
3640 x_avail_device_id := l_loop_device_id;
3641 x_device_request_id := l_request_id;
3642 END IF;
3643
3644 IF l_first_task = 'Y' THEN -- invoke the device
3645 l_first_task := 'N';
3646 l_first_task_id := task_record.transaction_temp_id; --Bug#8322661
3647 l_task_type_id := task_record.task_type; --Bug#8322661
3648 END IF;
3649
3650 END LOOP;
3651
3652 IF l_task_type_id <> 3 THEN -- invoke the device --Bug#8322661 Moved here from above
3653 wms_device_integration_pvt.device_request
3654 (p_bus_event => wms_device_integration_pvt.wms_be_pick_load,
3655 p_call_ctx => wms_device_integration_pvt.dev_req_auto,
3656 p_task_trx_id => l_first_task_id,
3657 p_org_id => p_organization_id,
3658 x_request_msg => l_request_msg,
3659 x_return_status => x_return_status,
3660 x_msg_count => x_msg_count,
3661 x_msg_data => x_msg_data,
3662 p_request_id => l_request_id);
3663
3664 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3665 IF (l_debug = 1) THEN
3666 mydebug('Failed to invoke device ' || TO_CHAR(l_loop_device_id), l_api_name);
3667 END IF;
3668 END IF;
3669
3670 IF x_device_request_id IS NULL THEN
3671 x_device_request_id := l_request_id;
3672 END IF;
3673
3674 END IF;
3675
3676 IF (l_debug = 1) THEN
3677 mydebug('Device Request ID: ' || l_request_id, l_api_name);
3678 END IF; --Bug#8322661
3679
3680 -- bug 5368659
3681 IF l_next_transaction_temp_id IS NULL and p_task_method='PICKBYLABEL' THEN --9507152
3682 get_next_task_in_group
3683 (p_employee_id => p_employee_id,
3684 p_organization_id => p_organization_id,
3685 p_subinventory_code => p_subinventory_code,
3686 p_device_id => l_device_id,
3687 p_grouping_document_type => x_grouping_document_type,
3688 p_grouping_document_number => x_grouping_document_number,
3689 p_grouping_source_type_id => x_grouping_source_type_id,
3690 x_task_id => l_task_id,
3691 x_transaction_temp_id => l_next_transaction_temp_id,
3692 x_task_type_id => x_task_type_id,
3693 x_return_status => x_return_status,
3694 x_msg_data => x_msg_data,
3695 x_msg_count => x_msg_count);
3696
3697 IF (l_debug = 1) THEN
3698 mydebug('Return Status from get_next_task_in_group: ' || x_return_status,l_api_name);
3699 END IF;
3700
3701 IF x_return_status = 'U' THEN
3702 RAISE fnd_api.g_exc_unexpected_error;
3703 ELSIF x_return_status = 'E' THEN
3704 RAISE fnd_api.g_exc_error;
3705 END IF;
3706
3707 IF l_next_transaction_temp_id IS NOT NULL THEN
3708 x_num_of_tasks := 1;
3709 l_task_from_group := TRUE;
3710 END IF;
3711
3712 END IF;
3713 -- end bug 5368659
3714
3715
3716 ELSIF x_return_status = 'U' THEN
3717 fnd_message.set_name('WMS', 'WMS_TD_TDENG_ERROR');
3718 fnd_msg_pub.ADD;
3719 RAISE fnd_api.g_exc_unexpected_error;
3720 ELSIF x_return_status = 'E' THEN
3721 IF (l_debug = 1) THEN
3722 mydebug('Setting status as S', l_api_name);
3723 END IF;
3724 x_return_status := fnd_api.g_ret_sts_success;
3725 END IF;
3726
3727 EXIT WHEN l_dup_task = FALSE; --Bug#8322661
3728 END LOOP; --Bug#8322661
3729
3730 -- If there are no devices then get out of the loop
3731 IF l_device_id IS NULL THEN
3732 l_need_dispatch := FALSE;
3733 END IF;
3734 END LOOP; -- end loop of the devices
3735
3736
3737 IF l_next_transaction_temp_id IS NOT NULL AND Nvl(x_num_of_tasks, 0) <= 0 THEN
3738 x_num_of_tasks := 1;
3739 END IF;
3740
3741 IF (l_debug = 1) THEN
3742 mydebug('Number of tasks: ' || x_num_of_tasks, l_api_name);
3743 END IF;
3744
3745 g_max_group_sequence_number := x_num_of_tasks;
3746 END IF; -- If there are more tasks in the group
3747
3748 IF l_next_transaction_temp_id IS NOT NULL THEN
3749
3750 IF x_task_type_id <> 3 THEN
3751 IF (p_task_method = 'CLUSTER') THEN
3752 l_cartons_list := ' '; -- make it not null to be used in the following API
3753 END IF;
3754
3755 --Added for Case Picking Project start
3756 IF (p_task_method = 'MANIFESTORDER' OR p_task_method = 'MANIFESTPICKSLIP') THEN
3757 l_is_manifest_pick := 'Y';
3758 ELSE
3759 l_is_manifest_pick := 'N';
3760 END IF;
3761 --Added for Case Picking Project end
3762
3763 x_num_of_tasks := g_max_group_sequence_number;
3764 x_task_number := g_group_sequence_number;
3765
3766 -- Need to get detailed information for the next task
3767 get_next_task_info
3768 (p_sign_on_emp_id => p_employee_id,
3769 p_sign_on_org_id => p_organization_id,
3770 p_transaction_temp_id => l_next_transaction_temp_id,
3771 p_cartonization_id => l_cartonization_id,
3772 p_device_id => x_avail_device_id,
3773 x_return_status => x_return_status,
3774 x_error_code => l_error_code,
3775 x_mesg_count => x_msg_count,
3776 x_error_mesg => x_msg_data,
3777 x_task_info => x_task_info,
3778 p_is_cluster_pick => 'N',
3779 p_cartons_list => l_cartons_list,
3780 p_is_manifest_pick => l_is_manifest_pick); --Added for Case Picking Project
3781
3782
3783 mydebug('get_next_task_info returned: ' || x_return_status, l_api_name);
3784
3785 IF x_return_status <> 'S' THEN
3786 fnd_message.set_name('WMS', 'WMS_TD_TDENG_ERROR');
3787 fnd_msg_pub.ADD;
3788 RAISE fnd_api.g_exc_unexpected_error;
3789 END IF;
3790
3791 l_request_id := NULL;
3792 -- viks For start over button pressed l_count >0 for multiple device
3793
3794 IF ((l_task_from_group OR p_task_method = 'MANUAL') OR l_count >0) THEN
3795
3796 IF x_avail_device_id IS NULL THEN
3797 IF p_device_id = 0 THEN
3798 x_avail_device_id := NULL;
3799 ELSE
3800 x_avail_device_id := p_device_id;
3801 END IF;
3802 END IF;
3803
3804 wms_device_integration_pvt.device_request
3805 (p_bus_event => wms_device_integration_pvt.wms_be_pick_load,
3806 p_call_ctx => wms_device_integration_pvt.dev_req_auto,
3807 --p_task_trx_id => l_transaction_temp_id,
3808 p_task_trx_id => l_next_transaction_temp_id,
3809 p_org_id => p_organization_id,
3810 x_request_msg => l_request_msg,
3811 x_return_status => x_return_status,
3812 x_msg_count => x_msg_count,
3813 x_msg_data => x_msg_data,
3814 p_request_id => l_request_id);
3815
3816 x_device_request_id := l_request_id;
3817
3818 -- always dispatch the task whether invoking device successfully or not
3819 -- So update the table always
3820
3821 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3822 IF (l_debug = 1) THEN
3823 mydebug('Failed to invoke device ' || TO_CHAR(l_loop_device_id), l_api_name);
3824 END IF;
3825 END IF;
3826 END IF;
3827
3828 UPDATE wms_dispatched_tasks
3829 SET status = 9, -- Active
3830 device_id = x_avail_device_id,
3831 device_invoked = Decode(x_avail_device_id, NULL, To_char(NULL), 'Y'),
3832 device_request_id = x_device_request_id,
3833 last_update_date = Sysdate,
3834 last_updated_by = l_user_id
3835 WHERE transaction_temp_id = l_next_transaction_temp_id;
3836 ELSE-- cycle count task
3837 mydebug('Updated Cycle count task to active: '||l_next_transaction_temp_id, l_api_name);
3838 UPDATE wms_dispatched_tasks
3839 SET status = 9, -- Active
3840 last_update_date = Sysdate,
3841 last_updated_by = l_user_id
3842 WHERE transaction_temp_id = l_next_transaction_temp_id;
3843 x_grouping_document_number := l_next_transaction_temp_id;
3844 END IF; -- Not a cycle count task
3845
3846 -- Delete from the skip tasks exceptions table
3847 DELETE FROM wms_skip_task_exceptions
3848 WHERE task_id = task_record.transaction_temp_id
3849 AND task_id IN (SELECT wste.task_id
3850 FROM wms_skip_task_exceptions wste, mtl_parameters mp
3851 WHERE ABS((SYSDATE - wste.creation_date) * 24 * 60) > mp.skip_task_waiting_minutes
3852 AND wste.task_id = task_record.transaction_temp_id
3853 AND wste.organization_id = mp.organization_id);
3854
3855 -- Committing these tasks to this user
3856 COMMIT;
3857
3858 IF (l_debug = 1) THEN
3859 mydebug('Committed tasks to the user', l_api_name);
3860
3861 mydebug('Current Group Sequence Number: ' || g_group_sequence_number, l_api_name);
3862 mydebug('Maximum Group Sequence Number: ' || g_max_group_sequence_number, l_api_name);
3863 END IF;
3864
3865 END IF;
3866
3867 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3868
3869 x_return_status := fnd_api.g_ret_sts_success;
3870
3871 IF x_return_status = 'S' AND l_return_status = 'W' THEN -- Bug 4507435
3872 mydebug('Good One','');
3873 x_return_status := 'W';
3874 END IF;
3875
3876
3877 EXCEPTION
3878 WHEN fnd_api.g_exc_error THEN
3879 IF (l_debug = 1) THEN
3880 mydebug('Error', l_api_name);
3881 END IF;
3882 x_return_status := 'E';
3883
3884 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3885
3886 ROLLBACK TO next_task_sp;
3887 WHEN OTHERS THEN
3888 IF (l_debug = 1) THEN
3889 mydebug('Unexpected Error: ' || Sqlerrm, l_api_name);
3890 END IF;
3891 x_return_status := 'U';
3892
3893 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3894
3895 ROLLBACK TO next_task_sp;
3896 END next_task;
3897
3898 --Start Bug 6682436
3899 PROCEDURE split_mmtt_lpn(
3900 p_transaction_temp_id IN NUMBER
3901 , p_line_quantity IN NUMBER
3902 , p_transaction_UOM IN VARCHAR2
3903 , p_lpn_id IN NUMBER
3904 , l_transaction_temp_id OUT NOCOPY NUMBER
3905 ,x_return_status OUT NOCOPY VARCHAR2
3906 ,x_msg_count OUT NOCOPY NUMBER
3907 ,x_msg_data OUT NOCOPY VARCHAR2
3908 )
3909 IS
3910 qty_tbl wms_Task_mgmt_pub.TASK_QTY_TBL_TYPE;
3911 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3912 l_resultant_task_details wms_Task_mgmt_pub.TASK_DETAIL_TBL_TYPE;
3913 l_resultant_tasks wms_Task_mgmt_pub.TASK_TAB_TYPE;
3914 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3915
3916 BEGIN
3917
3918 mydebug('inside split_mmtt_lpn p_transaction_temp_id' || p_transaction_temp_id, 'split_mmtt_lpn');
3919 mydebug('inside split_mmtt_lpn p_line_quantity' || p_line_quantity, 'split_mmtt_lpn');
3920 mydebug('inside split_mmtt_lpn p_transaction_UOM' || p_transaction_UOM, 'split_mmtt_lpn');
3921 mydebug('inside split_mmtt_lpn p_lpn_id' || p_lpn_id, 'split_mmtt_lpn');
3922
3923 qty_tbl(1).quantity := p_line_quantity;
3924 qty_tbl(1).uom := p_transaction_UOM;
3925
3926 IF WMS_CONTROL.G_CURRENT_RELEASE_LEVEL >= 120001 THEN
3927
3928 wms_picking_pkg.split_task(
3929 p_source_transaction_number => p_transaction_temp_id
3930 , p_split_quantities => qty_tbl
3931 , p_commit => FND_API.G_FALSE
3932 , x_resultant_tasks => l_resultant_tasks
3933 , x_resultant_task_details => l_resultant_task_details
3934 , x_return_status => l_return_status
3935 , x_msg_count => x_msg_count
3936 , x_msg_data => x_msg_data
3937 );
3938
3939 mydebug('l_resultant_tasks.COUNT' || l_resultant_tasks.COUNT , 'split_mmtt_lpn');
3940 if( l_resultant_tasks.COUNT > 0 ) THEN
3941 mydebug('l_resultant_tasks(1).task_id ' || l_resultant_tasks(1).task_id, 'split_mmtt_lpn');
3942 l_transaction_temp_id := l_resultant_tasks(1).task_id;
3943
3944 --Modified for bug 6717052
3945 update mtl_material_transactions_temp
3946 set transfer_lpn_id = p_lpn_id
3947 where transaction_temp_id = l_resultant_tasks(1).task_id;
3948
3949 x_return_status := fnd_api.g_ret_sts_success;
3950 ELSE
3951 l_transaction_temp_id := -9999;
3952 x_return_status := 'E';
3953 END IF;
3954 END IF;
3955
3956 EXCEPTION
3957 WHEN OTHERS THEN
3958 IF (l_debug = 1) THEN
3959 mydebug('sqlerrm' || SQLERRM, 'split_mmtt_lpn');
3960 mydebug('sqlcode ' || SQLCODE, 'split_mmtt_lpn');
3961 END IF;
3962 --fnd_message.set_name('WMS', 'WMS_PRINT_LABEL_FAIL');
3963 fnd_msg_pub.ADD;
3964 END split_mmtt_lpn;
3965
3966 PROCEDURE split_task( p_source_transaction_number IN NUMBER DEFAULT NULL ,
3967 p_split_quantities IN wms_Task_mgmt_pub.TASK_QTY_TBL_TYPE ,
3968 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
3969 x_resultant_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
3970 x_resultant_task_details OUT NOCOPY wms_Task_mgmt_pub.TASK_DETAIL_TBL_TYPE ,
3971 x_return_status OUT NOCOPY VARCHAR2 ,
3972 x_msg_count OUT NOCOPY NUMBER ,
3973 x_msg_data OUT NOCOPY VARCHAR2 ) IS
3974
3975 CURSOR mtlt_changed (p_ttemp_id IN NUMBER)
3976 IS
3977 SELECT *
3978 FROM mtl_transaction_lots_temp
3979 WHERE transaction_temp_id = p_ttemp_id
3980 ORDER BY lot_number;
3981 l_mtlt_row MTL_TRANSACTION_LOTS_TEMP%ROWTYPE;
3982 -- l_split_uom_quantities qty_changed_tbl_type;
3983 l_task_tbl_qty_count NUMBER := p_split_quantities.COUNT;
3984 l_decimal_precision CONSTANT NUMBER := 5;
3985 l_task_tbl_primary_qty NUMBER;
3986 l_task_tbl_transaction_qty NUMBER;
3987 l_sum_tbl_transaction_qty NUMBER := 0;
3988 l_sum_tbl_primary_qty NUMBER := 0;
3989 l_new_mol_id NUMBER;
3990 l_orig_mol_id NUMBER;
3991 l_new_transaction_temp_id NUMBER;
3992 l_new_transaction_header_id NUMBER;
3993 l_new_task_id NUMBER;
3994 l_remaining_primary_qty NUMBER := 0;
3995 l_remaining_transaction_qty NUMBER := 0;
3996 l_mol_num NUMBER;
3997 l_serial_control_code NUMBER;
3998 l_lot_control_code NUMBER;
3999 l_index NUMBER;
4000 l_new_tasks_output WMS_TASK_MGMT_PUB.task_tab_type;
4001 l_new_tasks_tbl WMS_TASK_MGMT_PUB.task_tab_type;
4002 x_task_table WMS_TASK_MGMT_PUB.task_tab_type;
4003 l_return_status VARCHAR2(10);
4004 l_msg_count NUMBER;
4005 l_msg_data VARCHAR(200);
4006 l_validation_status VARCHAR2(10);
4007 l_error_msg VARCHAR2(1000);
4008 l_task_return_status VARCHAR2(10);
4009 l_mmtt_return_status VARCHAR2(1);
4010 l_wdt_return_status VARCHAR2(1);
4011 l_lot_ser_return_status VARCHAR2(1);
4012 l_serial_return_status VARCHAR2(1);
4013 l_mtlt_transaction_qty NUMBER;
4014 l_msnt_transaction_qty NUMBER;
4015 l_val_task_ret_status VARCHAR2(1);
4016 l_mmtt_inventory_item_id NUMBER;
4017 l_mmtt_task_status NUMBER;
4018 l_mmtt_organization_id NUMBER;
4019 l_split_uom_quantities WMS_TASK_MGMT_PUB.QTY_CHANGED_TBL_TYPE;
4020 l_val_qty_ret_status VARCHAR2(1);
4021 l_invalid_task EXCEPTION;
4022 l_invalid_quantities EXCEPTION;
4023 l_unexpected_error EXCEPTION;
4024 l_query_task_exception EXCEPTION;
4025
4026 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
4027 BEGIN
4028 IF g_debug = 1 THEN
4029 mydebug( 'In Split Task ', 'split_task');
4030 END IF;
4031 x_return_status := 'S';
4032 new_task_table.delete;
4033 /*validate_task( p_transaction_temp_id => p_source_transaction_number , x_return_status => x_return_status , x_error_msg => l_error_msg , x_msg_data => x_msg_data , x_msg_count => x_msg_count );
4034 IF g_debug = 1 THEN
4035 mydebug( ' Validate task return status : '|| x_return_status);
4036 END IF;
4037 IF NVL(x_return_status,'E') <> 'S' THEN
4038 RAISE l_invalid_task;
4039 END IF;
4040 */
4041 validate_quantities(
4042 p_transaction_temp_id => p_source_transaction_number ,
4043 p_split_quantities => p_split_quantities ,
4044 x_lot_control_code => l_lot_control_code ,
4045 x_serial_control_code => l_serial_control_code ,
4046 x_split_uom_quantities => l_split_uom_quantities ,
4047 x_return_status => x_return_status ,
4048 x_msg_data => x_msg_data ,
4049 x_msg_count => x_msg_count );
4050 IF NVL(x_return_status,'E') <> 'S' THEN
4051 RAISE l_invalid_quantities;
4052 END IF;
4053 IF g_debug = 1 THEN
4054 FOR i in l_split_uom_quantities.FIRST .. l_split_uom_quantities.LAST
4055 LOOP
4056 mydebug(' l_split_uom_quantities('||i|| ').primary_quantity: '||l_split_uom_quantities(i).primary_quantity , 'split_task');
4057 mydebug(' l_split_uom_quantities('||i|| ').transaction_quantity: '||l_split_uom_quantities(i).transaction_quantity, 'split_task');
4058 END LOOP;
4059 END IF;
4060 SAVEPOINT wms_split_task;
4061 IF g_debug = 1 THEN
4062 mydebug(' SAVEPOINT wms_split_task established', 'split_task');
4063 END IF;
4064 FOR i IN l_split_uom_quantities.FIRST .. l_split_uom_quantities.LAST
4065 LOOP
4066 SELECT mtl_material_transactions_s.NEXTVAL
4067 INTO l_new_transaction_header_id
4068 FROM dual;
4069 SELECT mtl_material_transactions_s.NEXTVAL
4070 INTO l_new_transaction_temp_id
4071 FROM dual;
4072 SELECT wms_dispatched_tasks_s.NEXTVAL INTO l_new_task_id FROM dual;
4073 IF g_debug = 1 THEN
4074 mydebug( ' Calling split_mmtt for Txn. temp id : '||p_source_transaction_number,'split_task');
4075 END IF;
4076 split_mmtt(
4077 p_orig_transaction_temp_id => p_source_transaction_number ,
4078 p_new_transaction_temp_id => l_new_transaction_temp_id ,
4079 p_new_transaction_header_id => l_new_transaction_header_id ,
4080 p_new_mol_id => l_orig_mol_id ,
4081 p_transaction_qty_to_split => l_split_uom_quantities(i).transaction_quantity ,
4082 p_primary_qty_to_split => l_split_uom_quantities(i).primary_quantity ,
4083 x_return_status => x_return_status ,
4084 x_msg_data => x_msg_data ,
4085 x_msg_count => x_msg_count );
4086 IF g_debug = 1 THEN
4087 mydebug( ' x_return_status : ' || x_return_status, 'split_task');
4088 END IF;
4089 IF NVL(x_return_status, 'E') <> 'S' THEN
4090 IF g_debug = 1 THEN
4091 mydebug( ' Unable to split MMTT, unexpected error has occurred', 'split_task');
4092 END IF;
4093 RAISE l_unexpected_error;
4094 END IF;
4095 BEGIN
4096 SELECT status
4097 INTO l_mmtt_task_status
4098 FROM wms_dispatched_tasks
4099 WHERE transaction_temp_id = p_source_transaction_number;
4100 EXCEPTION
4101 WHEN NO_DATA_FOUND THEN
4102 l_mmtt_task_status := -9999;
4103 NULL;
4104 END;
4105 IF g_debug = 1 THEN
4106 mydebug( 'l_mmtt_task_status : '|| l_mmtt_task_status, 'split_task');
4107 END IF;
4108 --IF l_mmtt_task_status = 2 THEN
4109 split_wdt(
4110 p_new_task_id => l_new_task_id ,
4111 p_new_transaction_temp_id => l_new_transaction_temp_id ,
4112 p_new_mol_id => l_orig_mol_id ,
4113 p_orig_transaction_temp_id => p_source_transaction_number ,
4114 x_return_status => x_return_status ,
4115 x_msg_data => x_msg_data ,
4116 x_msg_count => x_msg_count );
4117 IF g_debug = 1 THEN
4118 mydebug( ' x_return_status : '||x_return_status, 'split_task');
4119 END IF;
4120 IF NVL(x_return_status, 'E') <> 'S' THEN
4121 IF g_debug = 1 THEN
4122 mydebug( ' Unable to split WDT, unexpected error has occurred', 'split_task');
4123 END IF;
4124 RAISE l_unexpected_error;
4125 END IF;
4126 --END IF;
4127 IF (l_lot_control_code = 2 AND l_serial_control_code IN (2,5)) OR (l_lot_control_code = 2 AND l_serial_control_code NOT IN (2,5)) THEN
4128 split_lot_serial(
4129 p_source_transaction_number ,
4130 l_new_transaction_temp_id ,
4131 l_split_uom_quantities(i).transaction_quantity ,
4132 l_split_uom_quantities(i).primary_quantity ,
4133 l_mmtt_inventory_item_id ,
4134 l_mmtt_organization_id ,
4135 x_return_status ,
4136 x_msg_data ,
4137 x_msg_count );
4138
4139 IF g_debug = 1 THEN
4140 mydebug( ' x_return_status : ' || x_return_status, 'split_task');
4141 END IF;
4142 IF NVL(x_return_status, 'E') <> 'S' THEN
4143 IF g_debug = 1 THEN
4144 mydebug( ' Was not able to split lot serial', 'split_task');
4145 END IF;
4146 RAISE l_unexpected_error;
4147 END IF;
4148 ELSIF l_lot_control_code = 1 AND l_serial_control_code IN (2,5) THEN
4149 split_serial(
4150 p_orig_transaction_temp_id => p_source_transaction_number ,
4151 p_new_transaction_temp_id => l_new_transaction_temp_id ,
4152 p_transaction_qty_to_split => l_split_uom_quantities(i).transaction_quantity ,
4153 p_primary_qty_to_split => l_split_uom_quantities(i).primary_quantity ,
4154 p_inventory_item_id => l_mmtt_inventory_item_id ,
4155 p_organization_id => l_mmtt_organization_id ,
4156 x_return_status => x_return_status ,
4157 x_msg_data => x_msg_data ,
4158 x_msg_count => x_msg_count );
4159
4160 IF g_debug = 1 THEN
4161 mydebug( ' x_return_status : '||x_return_status, 'split_task');
4162 END IF;
4163 IF NVL(x_return_status, 'E') <> 'S' THEN
4164 IF g_debug = 1 THEN
4165 mydebug( ' Was not able to split serials', 'split_task');
4166 END IF;
4167 RAISE l_unexpected_error;
4168 END IF;
4169 END IF;
4170 -- Update the original row
4171 BEGIN
4172 UPDATE mtl_material_transactions_temp
4173 SET primary_quantity = primary_quantity - l_split_uom_quantities(i).primary_quantity ,
4174 transaction_quantity = transaction_quantity - l_split_uom_quantities(i).transaction_quantity ,
4175 last_updated_by = FND_GLOBAL.USER_ID
4176 WHERE transaction_temp_id = p_source_transaction_number;
4177 EXCEPTION
4178 WHEN OTHERS THEN
4179 IF g_debug = 1 THEN
4180 mydebug( ' Error Code : '|| SQLCODE || ' Error Message :'||SUBSTR(SQLERRM,1,100), 'split_task');
4181 END IF;
4182 RAISE l_unexpected_error;
4183 END;
4184 IF g_debug = 1 THEN
4185 mydebug( ' Updated original txn. temp id :'||p_source_transaction_number, 'split_task');
4186 END IF;
4187 l_index := new_task_table.count + 1;
4188 new_task_table(l_index).transaction_temp_id := l_new_transaction_temp_id;
4189 END LOOP;
4190 l_index := new_task_table.count + 1;
4191 new_task_table(l_index).transaction_temp_id := p_source_transaction_number;
4192 IF g_debug = 1 THEN
4193 mydebug( ' Split done sucessfully for txn. temp id :'||p_source_transaction_number, 'split_task');
4194 END IF;
4195 IF g_debug = 1 THEN
4196 mydebug( ' lot control code :'||l_lot_control_code || ' serial control code : '|| l_serial_control_code, 'split_task');
4197 END IF;
4198 IF g_debug = 1 THEN
4199 mydebug( '***********New Task Table***********', 'split_task');
4200 mydebug( '*** Transaction temp id ***', 'split_task');
4201 FOR i IN new_task_table.FIRST .. new_task_table.LAST
4202 LOOP
4203 mydebug( ' '|| new_task_table(i).transaction_temp_id, 'split_task');
4204 END LOOP;
4205 END IF;
4206 IF g_debug = 1 THEN
4207 mydebug( 'Inserting Lot/Serial details of the new tasks in X_RESULTANT_TASK_DETAILS', 'split_task');
4208 END IF;
4209 IF l_lot_control_code = 2 THEN
4210 FOR i IN new_task_table.FIRST .. new_task_table.LAST
4211 LOOP
4212 OPEN mtlt_changed(new_task_table(i).transaction_temp_id);
4213 LOOP
4214 FETCH mtlt_changed INTO l_mtlt_row;
4215 EXIT
4216 WHEN mtlt_changed%NOTFOUND;
4217 l_index := x_resultant_task_details.count + 1;
4218 x_resultant_task_details(l_index).parent_task_id := l_mtlt_row.transaction_temp_id;
4219 x_resultant_task_details(l_index).lot_number := l_mtlt_row.lot_number;
4220 x_resultant_task_details(l_index).lot_expiration_date := l_mtlt_row.lot_expiration_date;
4221 x_resultant_task_details(l_index).lot_primary_quantity := l_mtlt_row.primary_quantity;
4222 x_resultant_task_details(l_index).lot_transaction_quantity := l_mtlt_row.transaction_quantity;
4223 IF l_mtlt_row.serial_transaction_temp_id IS NOT NULL THEN
4224 x_resultant_task_details(l_index).number_of_serials := l_mtlt_row.primary_quantity;
4225 SELECT MIN(FM_SERIAL_NUMBER) ,
4226 MAX(FM_SERIAL_NUMBER) ,
4227 MAX(status_id)
4228 INTO x_resultant_task_details(l_index).from_serial_number ,
4229 x_resultant_task_details(l_index).to_serial_number ,
4230 x_resultant_task_details(l_index).serial_status_id
4231 FROM mtl_serial_numbers_temp
4232 WHERE transaction_temp_id = l_mtlt_row.serial_transaction_temp_id;
4233 END IF;
4234 END LOOP;
4235 CLOSE mtlt_changed;
4236 END LOOP;
4237 ELSIF l_serial_control_code IN (2,5) THEN
4238 FOR i IN new_task_table.FIRST .. new_task_table.LAST
4239 LOOP
4240 l_index := x_resultant_task_details.count + 1;
4241 x_resultant_task_details(l_index).parent_task_id := new_task_table(i).transaction_temp_id;
4242 SELECT MIN(FM_SERIAL_NUMBER) ,
4243 MAX(FM_SERIAL_NUMBER) ,
4244 MAX(status_id) ,
4245 COUNT(*)
4246 INTO x_resultant_task_details(l_index).from_serial_number ,
4247 x_resultant_task_details(l_index).to_serial_number ,
4248 x_resultant_task_details(l_index).serial_status_id ,
4249 x_resultant_task_details(l_index).number_of_serials
4250 FROM mtl_serial_numbers_temp
4251 WHERE transaction_temp_id = new_task_table(i).transaction_temp_id;
4252 END LOOP;
4253 END IF;
4254 IF g_debug = 1 THEN
4255 IF x_resultant_task_details.COUNT > 0 THEN
4256 mydebug( 'Task Id Lot quantity fm_serial to_serial num_of_serials', 'split_task');
4257 FOR i IN x_resultant_task_details.FIRST .. x_resultant_task_details.LAST
4258 LOOP
4259 mydebug(x_resultant_task_details(i).parent_task_id ||' '|| x_resultant_task_details(i).lot_number ||' '||x_resultant_task_details(i).lot_primary_quantity, 'split_task');
4260 mydebug(x_resultant_task_details(i).from_serial_number||' '||x_resultant_task_details(i).to_serial_number||' '||x_resultant_task_details(i).number_of_serials, 'split_task');
4261 END LOOP;
4262 ELSE
4263 mydebug('Table x_resultant_task_details is empty, item is not serial or lot controlled', 'split_task');
4264 END IF;
4265 END IF;
4266 FOR i IN new_task_table.FIRST .. new_task_table.LAST
4267 LOOP
4268 x_resultant_tasks(i).task_id := new_task_table(i).transaction_temp_id;
4269 END LOOP;
4270
4271
4272 IF g_debug = 1 THEN
4273 IF x_resultant_tasks.COUNT > 0 THEN
4274 mydebug( 'Task Id item_id sub locator Qty', 'split_task');
4275 FOR i IN x_resultant_tasks.FIRST .. x_resultant_tasks.LAST
4276 LOOP
4277 mydebug( x_resultant_tasks(i).task_id ||' '||x_resultant_tasks(i).inventory_item_id||' '||x_resultant_tasks(i).subinventory||' '||x_resultant_tasks(i).locator||' '||x_resultant_tasks(i).transaction_quantity, 'split_task');
4278 END LOOP;
4279 ELSE
4280 mydebug( 'Table x_resultant_tasks is empty', 'split_task');
4281 END IF;
4282 END IF;
4283 IF p_commit = FND_API.G_TRUE THEN
4284 IF g_debug = 1 THEN
4285 mydebug( ' p_commit is TRUE, so COMMITING the transactions.', 'split_task');
4286 END IF;
4287 COMMIT;
4288 ELSE
4289 IF g_debug = 1 THEN
4290 mydebug( ' p_commit is FALSE, so not COMMITING the transactions.', 'split_task');
4291 END IF;
4292 END IF;
4293 x_return_status := 'S';
4294 EXCEPTION
4295 WHEN OTHERS THEN
4296 IF g_debug = 1 THEN
4297 mydebug('EXCEPTION BLOCK : Unexpected error has occured, ROLLING BACK THE TRANSACTIONS', 'split_task');
4298 END IF;
4299 x_return_status := 'E';
4300 ROLLBACK TO wms_split_task;
4301 fnd_message.set_name('WMS', 'WMS_UNEXPECTED_ERROR');
4302 fnd_msg_pub.ADD;
4303 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4304 x_return_status := 'E';
4305 IF g_debug = 1 THEN
4306 mydebug( 'EXCEPTION BLOCK : Error Code : '|| SQLCODE || 'EXCEPTION BLOCK : Error Message :'||SQLERRM, 'split_task');
4307 END IF;
4308 END split_task;
4309
4310 PROCEDURE validate_quantities( p_transaction_temp_id IN NUMBER ,
4311 p_split_quantities IN wms_Task_mgmt_pub.task_qty_tbl_type ,
4312 x_lot_control_code OUT NOCOPY NUMBER ,
4313 x_serial_control_code OUT NOCOPY NUMBER ,
4314 x_split_uom_quantities OUT NOCOPY wms_Task_mgmt_pub.qty_changed_tbl_type ,
4315 x_return_status OUT NOCOPY VARCHAR2 ,
4316 x_msg_data OUT NOCOPY VARCHAR2 ,
4317 x_msg_count OUT NOCOPY VARCHAR2 ) IS
4318
4319 l_mmtt_inventory_item_id NUMBER;
4320 l_mmtt_primary_quantity NUMBER;
4321 l_mmtt_transaction_quantity NUMBER;
4322 l_mmtt_transaction_uom_code NUMBER;
4323 l_mmtt_organization_id NUMBER;
4324 l_mmtt_transaction_uom VARCHAR2(3);
4325 l_mmtt_item_primary_uom_code VARCHAR2(3);
4326 l_lot_control_code NUMBER;
4327 l_serial_control_code NUMBER;
4328 l_decimal_precision CONSTANT NUMBER := 5;
4329 l_mtlt_transaction_qty NUMBER := 0;
4330 l_msnt_transaction_qty NUMBER := 0;
4331 l_task_tbl_transaction_qty NUMBER := 0;
4332 l_task_tbl_primary_qty NUMBER := 0;
4333 l_sum_tbl_transaction_qty NUMBER := 0;
4334 l_sum_tbl_primary_qty NUMBER := 0;
4335 l_remaining_primary_qty NUMBER := 0;
4336 l_remaining_transaction_qty NUMBER := 0;
4337 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
4338 BEGIN
4339 x_return_status := 'E';
4340 IF g_debug = 1 THEN
4341 mydebug( 'Entered','validate_quantities');
4342 END IF;
4343 IF p_split_quantities.COUNT = 0 THEN
4344 x_return_status := 'E';
4345 IF g_debug = 1 THEN
4346 mydebug( 'Quantities table is empty, exiting', 'validate_quantities');
4347 END IF;
4348 RETURN;
4349 END IF;
4350 SELECT transaction_uom ,
4351 inventory_item_id ,
4352 primary_quantity ,
4353 transaction_quantity ,
4354 item_primary_uom_code ,
4355 transaction_uom ,
4356 organization_id
4357 INTO l_mmtt_transaction_uom ,
4358 l_mmtt_inventory_item_id ,
4359 l_mmtt_primary_quantity ,
4360 l_mmtt_transaction_quantity ,
4361 l_mmtt_item_primary_uom_code ,
4362 l_mmtt_transaction_uom ,
4363 l_mmtt_organization_id
4364 FROM mtl_material_transactions_temp
4365 WHERE transaction_temp_id = p_transaction_temp_id;
4366 SELECT lot_control_code ,
4367 serial_number_control_code
4368 INTO l_lot_control_code ,
4369 l_serial_control_code
4370 FROM mtl_system_items_b
4371 WHERE inventory_item_id = l_mmtt_inventory_item_id
4372 AND organization_id = l_mmtt_organization_id;
4373 x_lot_control_code := l_lot_control_code;
4374 x_serial_control_code := l_serial_control_code;
4375 IF g_debug = 1 THEN
4376 FOR i IN p_split_quantities.FIRST .. p_split_quantities.LAST
4377 LOOP
4378 mydebug(' Inside For loop i = ' || i ||' Task :'||p_transaction_temp_id||' Quantity : '||p_split_quantities(i).quantity || ' Suggested UOM :' || p_split_quantities(i).uom, 'validate_quantities');
4379 mydebug(' MMTT transaction UOM :' ||l_mmtt_transaction_uom, 'validate_quantities');
4380 END LOOP;
4381 END IF;
4382 FOR i IN p_split_quantities.FIRST .. p_split_quantities.LAST
4383 LOOP
4384 IF p_split_quantities(i).uom IS NULL THEN
4385 IF g_debug = 1 THEN
4386 mydebug( 'UOM cannot be passed as NULL', 'validate_quantities');
4387 END IF;
4388 x_return_status := 'E';
4389 RETURN;
4390 END IF;
4391 --Bug 6924526
4392 /*IF RTRIM(LTRIM(p_split_quantities(i).uom)) NOT IN (l_mmtt_item_primary_uom_code,l_mmtt_transaction_uom) THEN
4393 x_return_status := 'E';
4394 IF g_debug = 1 THEN
4395 mydebug( 'UOM validation failed, only primary or transaction UOM allowed :', 'validate_quantities');
4396 END IF;
4397 RETURN;
4398 END IF;*/
4399 -- All UOMs are same
4400 IF l_mmtt_transaction_uom = l_mmtt_item_primary_uom_code THEN
4401 x_split_uom_quantities(i).primary_quantity := p_split_quantities(i).quantity;
4402 x_split_uom_quantities(i).transaction_quantity := p_split_quantities(i).quantity;
4403 ELSE
4404 IF l_mmtt_transaction_uom = p_split_quantities(i).uom THEN
4405 IF g_debug = 1 THEN
4406 mydebug( ' mmtt transaction UOM is same as UOM in quantity table', 'validate_quantities');
4407 END IF;
4408 l_task_tbl_transaction_qty := p_split_quantities(i).quantity;
4409 x_split_uom_quantities(i).transaction_quantity := p_split_quantities(i).quantity;
4410 ELSE
4411 IF g_debug = 1 THEN
4412 mydebug( ' mmtt transaction UOM quantity table UOM are not same, calling inv_convert.inv_um_convert with :', 'validate_quantities');
4413 mydebug( ' item_id : '||l_mmtt_inventory_item_id, 'validate_quantities');
4414 mydebug( ' PRECISION : '|| l_decimal_precision, 'validate_quantities');
4415 mydebug( ' from_quantity :'|| p_split_quantities(i).quantity, 'validate_quantities');
4416 mydebug( ' from_unit :'||p_split_quantities(i).uom, 'validate_quantities');
4417 mydebug( ' to_unit :'||l_mmtt_transaction_uom, 'validate_quantities');
4418 END IF;
4419 l_task_tbl_transaction_qty := inv_convert.inv_um_convert(
4420 item_id => l_mmtt_inventory_item_id ,
4421 PRECISION => l_decimal_precision ,
4422 from_quantity => p_split_quantities(i).quantity ,
4423 from_unit => p_split_quantities(i).uom ,
4424 to_unit => l_mmtt_transaction_uom ,
4425 from_name => NULL ,
4426 to_name => NULL );
4427
4428 IF l_task_tbl_transaction_qty = -9999 THEN
4429 IF g_debug = 1 THEN
4430 mydebug( ' No conversion defined from :'||p_split_quantities(i).uom|| ' to :'|| l_mmtt_transaction_uom || ' , or UOM does not exist.', 'validate_quantities');
4431 END IF;
4432 x_return_status := 'E';
4433 RETURN;
4434 END IF;
4435 x_split_uom_quantities(i).transaction_quantity := l_task_tbl_transaction_qty;
4436 END IF;
4437 IF l_mmtt_item_primary_uom_code = p_split_quantities(i).uom THEN
4438 IF g_debug = 1 THEN
4439 mydebug( ' primary UOM is same as UOM in quantity table', 'validate_quantities');
4440 END IF;
4441 l_task_tbl_primary_qty := p_split_quantities(i).quantity;
4442 x_split_uom_quantities(i).primary_quantity := p_split_quantities(i).quantity;
4443 ELSE
4444 IF g_debug = 1 THEN
4445 mydebug( ' primary UOM not same as UOM in quantity table', 'validate_quantities');
4446 mydebug( ' For primary quantity ', 'validate_quantities');
4447 mydebug( ' item_id : '||l_mmtt_inventory_item_id, 'validate_quantities');
4448 mydebug( ' PRECISION : '|| l_decimal_precision, 'validate_quantities');
4449 mydebug( ' from_quantity :'|| p_split_quantities(i).quantity, 'validate_quantities');
4450 mydebug( ' from_unit :'||p_split_quantities(i).uom, 'validate_quantities');
4451 mydebug( ' to_unit :'||l_mmtt_transaction_uom, 'validate_quantities');
4452 END IF;
4453 l_task_tbl_primary_qty := inv_convert.inv_um_convert(
4454 item_id => l_mmtt_inventory_item_id ,
4455 PRECISION => l_decimal_precision ,
4456 from_quantity => p_split_quantities(i).quantity ,
4457 from_unit => p_split_quantities(i).uom ,
4458 to_unit => l_mmtt_item_primary_uom_code ,
4459 from_name => NULL ,
4460 to_name => NULL);
4461
4462 IF l_task_tbl_transaction_qty = -9999 THEN
4463 IF g_debug = 1 THEN
4464 mydebug( ' No conversion defined from :'||p_split_quantities(i).uom|| ' to :'|| l_mmtt_transaction_uom || ' , or UOM does not exist.', 'validate_quantities');
4465 END IF;
4466 x_return_status := 'E';
4467 RETURN;
4468 END IF;
4469 x_split_uom_quantities(i).primary_quantity := l_task_tbl_primary_qty;
4470 END IF;
4471 END IF;
4472 IF x_split_uom_quantities(i).transaction_quantity <= 0 OR x_split_uom_quantities(i).primary_quantity <= 0 THEN
4473 IF g_debug = 1 THEN
4474 mydebug('Negative and zero quantities are not allowed in quantities table, exiting.', 'validate_quantities');
4475 END IF;
4476 x_return_status := 'E';
4477 RETURN;
4478 END IF;
4479 l_sum_tbl_transaction_qty := l_sum_tbl_transaction_qty + x_split_uom_quantities(i).transaction_quantity;
4480 l_sum_tbl_primary_qty := l_sum_tbl_primary_qty + x_split_uom_quantities(i).primary_quantity;
4481 END LOOP;
4482 IF g_debug = 1 THEN
4483 mydebug( 'l_sum_tbl_transaction_qty : '||l_sum_tbl_transaction_qty, 'validate_quantities');
4484 mydebug( 'l_sum_tbl_primary_qty : '||l_sum_tbl_primary_qty, 'validate_quantities');
4485 END IF;
4486 IF l_sum_tbl_transaction_qty >= l_mmtt_transaction_quantity THEN
4487 IF g_debug = 1 THEN
4488 mydebug('Sum of qty table :'|| l_sum_tbl_transaction_qty || 'should be less than the mmtt line quantity:'||l_mmtt_transaction_quantity , 'validate_quantities');
4489 END IF;
4490 x_return_status := 'E';
4491 RETURN;
4492 END IF;
4493 --Validate lot/serial quantity
4494 IF g_debug = 1 THEN
4495 mydebug('Validating lot/serial if allocations are present', 'validate_quantities');
4496 mydebug( 'lot_control_code : '|| l_lot_control_code, 'validate_quantities');
4497 mydebug( 'serial_control_code : '|| l_serial_control_code, 'validate_quantities');
4498 END IF;
4499 IF l_lot_control_code = 2 AND l_serial_control_code IN (2,5) THEN
4500 BEGIN
4501 --Lot quantity
4502 SELECT sum(transaction_quantity)
4503 INTO l_mtlt_transaction_qty
4504 FROM mtl_transaction_lots_temp
4505 WHERE transaction_temp_id = p_transaction_temp_id;
4506 IF g_debug = 1 THEN
4507 mydebug( 'l_mtlt_transaction_qty : '||l_mtlt_transaction_qty|| ' l_mmtt_transaction_quantity : '||l_mmtt_transaction_quantity, 'validate_quantities');
4508 END IF;
4509 IF l_mtlt_transaction_qty <> l_mmtt_transaction_quantity THEN
4510 x_return_status := 'E';
4511 IF g_debug = 1 THEN
4512 mydebug('Mismatch in MMTT and MTLT quantity', 'validate_quantities');
4513 END IF;
4514 RETURN;
4515 END IF;
4516 EXCEPTION
4517 WHEN NO_DATA_FOUND THEN
4518 x_return_status := 'E';
4519 IF g_debug = 1 THEN
4520 mydebug('No Data Found : Mismatch in MMTT and MTLT quantity', 'validate_quantities');
4521 END IF;
4522 RETURN;
4523 END;
4524 BEGIN
4525 --serial quantity
4526 SELECT sum(1)
4527 INTO l_msnt_transaction_qty
4528 FROM mtl_serial_numbers_temp
4529 WHERE transaction_temp_id IN
4530 (SELECT serial_transaction_temp_id
4531 FROM mtl_transaction_lots_temp
4532 WHERE transaction_temp_id = p_transaction_temp_id
4533 );
4534 IF l_msnt_transaction_qty <> l_mmtt_transaction_quantity THEN
4535 x_return_status := 'E';
4536 IF g_debug = 1 THEN
4537 mydebug('Mismatch in MMTT and MSNT quantity', 'validate_quantities');
4538 END IF;
4539 RETURN;
4540 END IF;
4541 EXCEPTION
4542 WHEN NO_DATA_FOUND THEN
4543 x_return_status := 'E';
4544 IF g_debug = 1 THEN
4545 mydebug('No Data Found :Mismatch in MMTT and MSNT quantity', 'validate_quantities');
4546 END IF;
4547 RETURN;
4548 END;
4549 ELSIF l_lot_control_code = 2 AND l_serial_control_code NOT IN (2,5) THEN
4550 BEGIN
4551 --Lot quantity
4552 SELECT sum(transaction_quantity)
4553 INTO l_mtlt_transaction_qty
4554 FROM mtl_transaction_lots_temp
4555 WHERE transaction_temp_id = p_transaction_temp_id;
4556 IF l_mtlt_transaction_qty <> l_mmtt_transaction_quantity THEN
4557 x_return_status := 'E';
4558 IF g_debug = 1 THEN
4559 mydebug('Mismatch in MMTT and MTLT quantity', 'validate_quantities');
4560 END IF;
4561 RETURN;
4562 END IF;
4563 EXCEPTION
4564 WHEN NO_DATA_FOUND THEN
4565 x_return_status := 'E';
4566 IF g_debug = 1 THEN
4567 mydebug('No Data Found :Mismatch in MMTT and MTLT quantity', 'validate_quantities');
4568 END IF;
4569 RETURN;
4570 END;
4571 ELSIF l_lot_control_code = 1 AND l_serial_control_code IN (2,5) THEN
4572 BEGIN
4573 IF g_debug = 1 THEN
4574 mydebug('Checking for MMTT and MSNT quantity', 'validate_quantities');
4575 END IF;
4576 --Serial quantity
4577 SELECT sum(1)
4578 INTO l_msnt_transaction_qty
4579 FROM mtl_serial_numbers_temp
4580 WHERE transaction_temp_id = p_transaction_temp_id;
4581 IF l_msnt_transaction_qty <> l_mmtt_transaction_quantity THEN
4582 x_return_status := 'E';
4583 IF g_debug = 1 THEN
4584 mydebug('Mismatch in MMTT and MSNT quantity', 'validate_quantities');
4585 END IF;
4586 RETURN;
4587 END IF;
4588 EXCEPTION
4589 WHEN NO_DATA_FOUND THEN
4590 x_return_status := 'E';
4591 IF g_debug = 1 THEN
4592 mydebug('No Data Found :Mismatch in MMTT and MSNT quantity', 'validate_quantities');
4593 END IF;
4594 RETURN;
4595 END;
4596 END IF;
4597 IF g_debug = 1 THEN
4598 mydebug( 'l_mmtt_primary_quantity - l_sum_tbl_primary_qty '||l_mmtt_primary_quantity || ' - '||l_sum_tbl_transaction_qty, 'validate_quantities');
4599 END IF;
4600 x_return_status := 'S';
4601 EXCEPTION
4602 WHEN OTHERS THEN
4603 x_return_status := 'E';
4604 END validate_quantities;
4605
4606 PROCEDURE split_mmtt( p_orig_transaction_temp_id IN NUMBER ,
4607 p_new_transaction_temp_id IN NUMBER ,
4608 p_new_transaction_header_id IN NUMBER ,
4609 p_new_mol_id IN NUMBER ,
4610 p_transaction_qty_to_split IN NUMBER ,
4611 p_primary_qty_to_split IN NUMBER ,
4612 x_return_status OUT NOCOPY VARCHAR2 ,
4613 x_msg_data OUT NOCOPY VARCHAR2 ,
4614 x_msg_count OUT NOCOPY VARCHAR2 ) IS
4615
4616 l_sysdate DATE := SYSDATE;
4617 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
4618 BEGIN
4619 x_return_status := 'E';
4620 IF g_debug = 1 THEN
4621 mydebug( ' Entered ', 'split_mmtt');
4622 END IF;
4623 INSERT
4624 INTO mtl_material_transactions_temp
4625 (
4626 currency_conversion_date ,
4627 shipment_number ,
4628 org_cost_group_id ,
4629 cost_type_id ,
4630 transaction_status ,
4631 standard_operation_id ,
4632 task_priority ,
4633 wms_task_type ,
4634 parent_line_id ,
4635 source_lot_number ,
4636 transfer_cost_group_id ,
4637 lpn_id ,
4638 transfer_lpn_id ,
4639 wms_task_status ,
4640 content_lpn_id ,
4641 container_item_id ,
4642 cartonization_id ,
4643 pick_slip_date ,
4644 rebuild_item_id ,
4645 rebuild_serial_number ,
4646 rebuild_activity_id ,
4647 rebuild_job_name ,
4648 organization_type ,
4649 transfer_organization_type ,
4650 owning_organization_id ,
4651 owning_tp_type ,
4652 xfr_owning_organization_id ,
4653 transfer_owning_tp_type ,
4654 planning_organization_id ,
4655 planning_tp_type ,
4656 xfr_planning_organization_id ,
4657 transfer_planning_tp_type ,
4658 secondary_uom_code ,
4659 secondary_transaction_quantity ,
4660 allocated_lpn_id ,
4661 schedule_number ,
4662 scheduled_flag ,
4663 class_code ,
4664 schedule_group ,
4665 build_sequence ,
4666 bom_revision ,
4667 routing_revision ,
4668 bom_revision_date ,
4669 routing_revision_date ,
4670 alternate_bom_designator ,
4671 alternate_routing_designator ,
4672 transaction_batch_id ,
4673 transaction_batch_seq ,
4674 operation_plan_id ,
4675 intransit_account ,
4676 fob_point ,
4677 transaction_header_id ,
4678 transaction_temp_id ,
4679 source_code ,
4680 source_line_id ,
4681 transaction_mode ,
4682 lock_flag ,
4683 last_update_date ,
4684 last_updated_by ,
4685 creation_date ,
4686 created_by ,
4687 last_update_login ,
4688 request_id ,
4689 program_application_id ,
4690 program_id ,
4691 program_update_date ,
4692 inventory_item_id ,
4693 revision ,
4694 organization_id ,
4695 subinventory_code ,
4696 locator_id ,
4697 transaction_quantity ,
4698 primary_quantity ,
4699 transaction_uom ,
4700 transaction_cost ,
4701 transaction_type_id ,
4702 transaction_action_id ,
4703 transaction_source_type_id ,
4704 transaction_source_id ,
4705 transaction_source_name ,
4706 transaction_date ,
4707 acct_period_id ,
4708 distribution_account_id ,
4709 transaction_reference ,
4710 requisition_line_id ,
4711 requisition_distribution_id ,
4712 reason_id ,
4713 lot_number ,
4714 lot_expiration_date ,
4715 serial_number ,
4716 receiving_document ,
4717 demand_id ,
4718 rcv_transaction_id ,
4719 move_transaction_id ,
4720 completion_transaction_id ,
4721 wip_entity_type ,
4722 schedule_id ,
4723 repetitive_line_id ,
4724 employee_code ,
4725 primary_switch ,
4726 schedule_update_code ,
4727 setup_teardown_code ,
4728 item_ordering ,
4729 negative_req_flag ,
4730 operation_seq_num ,
4731 picking_line_id ,
4732 trx_source_line_id ,
4733 trx_source_delivery_id ,
4734 physical_adjustment_id ,
4735 cycle_count_id ,
4736 rma_line_id ,
4737 customer_ship_id ,
4738 currency_code ,
4739 currency_conversion_rate ,
4740 currency_conversion_type ,
4741 ship_to_location ,
4742 move_order_header_id ,
4743 serial_allocated_flag ,
4744 trx_flow_header_id ,
4745 logical_trx_type_code ,
4746 original_transaction_temp_id ,
4747 vendor_lot_number ,
4748 encumbrance_account ,
4749 encumbrance_amount ,
4750 transfer_cost ,
4751 transportation_cost ,
4752 transportation_account ,
4753 freight_code ,
4754 containers ,
4755 waybill_airbill ,
4756 expected_arrival_date ,
4757 transfer_subinventory ,
4758 transfer_organization ,
4759 transfer_to_location ,
4760 new_average_cost ,
4761 value_change ,
4762 percentage_change ,
4763 material_allocation_temp_id ,
4764 demand_source_header_id ,
4765 demand_source_line ,
4766 demand_source_delivery ,
4767 item_segments ,
4768 item_description ,
4769 item_trx_enabled_flag ,
4770 item_location_control_code ,
4771 item_restrict_subinv_code ,
4772 item_restrict_locators_code ,
4773 item_revision_qty_control_code ,
4774 item_primary_uom_code ,
4775 item_uom_class ,
4776 item_shelf_life_code ,
4777 item_shelf_life_days ,
4778 item_lot_control_code ,
4779 item_serial_control_code ,
4780 item_inventory_asset_flag ,
4781 allowed_units_lookup_code ,
4782 department_id ,
4783 department_code ,
4784 wip_supply_type ,
4785 supply_subinventory ,
4786 supply_locator_id ,
4787 valid_subinventory_flag ,
4788 valid_locator_flag ,
4789 locator_segments ,
4790 current_locator_control_code ,
4791 number_of_lots_entered ,
4792 wip_commit_flag ,
4793 next_lot_number ,
4794 lot_alpha_prefix ,
4795 next_serial_number ,
4796 serial_alpha_prefix ,
4797 shippable_flag ,
4798 posting_flag ,
4799 required_flag ,
4800 process_flag ,
4801 ERROR_CODE ,
4802 error_explanation ,
4803 attribute_category ,
4804 attribute1 ,
4805 attribute2 ,
4806 attribute3 ,
4807 attribute4 ,
4808 attribute5 ,
4809 attribute6 ,
4810 attribute7 ,
4811 attribute8 ,
4812 attribute9 ,
4813 attribute10 ,
4814 attribute11 ,
4815 attribute12 ,
4816 attribute13 ,
4817 attribute14 ,
4818 attribute15 ,
4819 movement_id ,
4820 reservation_quantity ,
4821 shipped_quantity ,
4822 transaction_line_number ,
4823 task_id ,
4824 to_task_id ,
4825 source_task_id ,
4826 project_id ,
4827 source_project_id ,
4828 pa_expenditure_org_id ,
4829 to_project_id ,
4830 expenditure_type ,
4831 final_completion_flag ,
4832 transfer_percentage ,
4833 transaction_sequence_id ,
4834 material_account ,
4835 material_overhead_account ,
4836 resource_account ,
4837 outside_processing_account ,
4838 overhead_account ,
4839 flow_schedule ,
4840 cost_group_id ,
4841 demand_class ,
4842 qa_collection_id ,
4843 kanban_card_id ,
4844 overcompletion_transaction_qty ,
4845 overcompletion_primary_qty ,
4846 overcompletion_transaction_id ,
4847 end_item_unit_number ,
4848 scheduled_payback_date ,
4849 line_type_code ,
4850 parent_transaction_temp_id ,
4851 put_away_strategy_id ,
4852 put_away_rule_id ,
4853 pick_strategy_id ,
4854 pick_rule_id ,
4855 move_order_line_id ,
4856 task_group_id ,
4857 pick_slip_number ,
4858 reservation_id ,
4859 common_bom_seq_id ,
4860 common_routing_seq_id ,
4861 ussgl_transaction_code ,
4862 fulfillment_base --16574325
4863 )
4864 SELECT currency_conversion_date ,
4865 shipment_number ,
4866 org_cost_group_id ,
4867 cost_type_id ,
4868 transaction_status ,
4869 standard_operation_id ,
4870 task_priority ,
4871 wms_task_type ,
4872 parent_line_id ,
4873 source_lot_number ,
4874 transfer_cost_group_id ,
4875 lpn_id ,
4876 transfer_lpn_id ,
4877 wms_task_status ,
4878 content_lpn_id ,
4879 container_item_id ,
4880 cartonization_id ,
4881 pick_slip_date ,
4882 rebuild_item_id ,
4883 rebuild_serial_number ,
4884 rebuild_activity_id ,
4885 rebuild_job_name ,
4886 organization_type ,
4887 transfer_organization_type ,
4888 owning_organization_id ,
4889 owning_tp_type ,
4890 xfr_owning_organization_id ,
4891 transfer_owning_tp_type ,
4892 planning_organization_id ,
4893 planning_tp_type ,
4894 xfr_planning_organization_id ,
4895 transfer_planning_tp_type ,
4896 secondary_uom_code ,
4897 secondary_transaction_quantity ,
4898 allocated_lpn_id ,
4899 schedule_number ,
4900 scheduled_flag ,
4901 class_code ,
4902 schedule_group ,
4903 build_sequence ,
4904 bom_revision ,
4905 routing_revision ,
4906 bom_revision_date ,
4907 routing_revision_date ,
4908 alternate_bom_designator ,
4909 alternate_routing_designator ,
4910 transaction_batch_id ,
4911 transaction_batch_seq ,
4912 operation_plan_id ,
4913 intransit_account ,
4914 fob_point ,
4915 p_new_transaction_header_id --TRANSACTION_HEADER_ID
4916 ,
4917 p_new_transaction_temp_id --TRANSACTION_TEMP_ID
4918 ,
4919 source_code ,
4920 source_line_id ,
4921 transaction_mode ,
4922 lock_flag ,
4923 l_sysdate --LAST_UPDATE_DATE
4924 ,
4925 FND_GLOBAL.USER_ID ,
4926 l_sysdate --CREATION_DATE
4927 ,
4928 FND_GLOBAL.USER_ID ,
4929 last_update_login ,
4930 request_id ,
4931 program_application_id ,
4932 program_id ,
4933 program_update_date ,
4934 inventory_item_id ,
4935 revision ,
4936 organization_id ,
4937 subinventory_code ,
4938 locator_id ,
4939 p_transaction_qty_to_split --TRANSACTION_QUANTITY
4940 ,
4941 p_primary_qty_to_split --PRIMARY_QUANTITY
4942 ,
4943 transaction_uom ,
4944 transaction_cost ,
4945 transaction_type_id ,
4946 transaction_action_id ,
4947 transaction_source_type_id ,
4948 transaction_source_id ,
4949 transaction_source_name ,
4950 transaction_date ,
4951 acct_period_id ,
4952 distribution_account_id ,
4953 transaction_reference ,
4954 requisition_line_id ,
4955 requisition_distribution_id ,
4956 reason_id ,
4957 lot_number ,
4958 lot_expiration_date ,
4959 serial_number ,
4960 receiving_document ,
4961 demand_id ,
4962 rcv_transaction_id ,
4963 move_transaction_id ,
4964 completion_transaction_id ,
4965 wip_entity_type ,
4966 schedule_id ,
4967 repetitive_line_id ,
4968 employee_code ,
4969 primary_switch ,
4970 schedule_update_code ,
4971 setup_teardown_code ,
4972 item_ordering ,
4973 negative_req_flag ,
4974 operation_seq_num ,
4975 picking_line_id ,
4976 trx_source_line_id ,
4977 trx_source_delivery_id ,
4978 physical_adjustment_id ,
4979 cycle_count_id ,
4980 rma_line_id ,
4981 customer_ship_id ,
4982 currency_code ,
4983 currency_conversion_rate ,
4984 currency_conversion_type ,
4985 ship_to_location ,
4986 move_order_header_id ,
4987 serial_allocated_flag ,
4988 trx_flow_header_id ,
4989 logical_trx_type_code ,
4990 original_transaction_temp_id ,
4991 vendor_lot_number ,
4992 encumbrance_account ,
4993 encumbrance_amount ,
4994 transfer_cost ,
4995 transportation_cost ,
4996 transportation_account ,
4997 freight_code ,
4998 containers ,
4999 waybill_airbill ,
5000 expected_arrival_date ,
5001 transfer_subinventory ,
5002 transfer_organization ,
5003 transfer_to_location ,
5004 new_average_cost ,
5005 value_change ,
5006 percentage_change ,
5007 material_allocation_temp_id ,
5008 demand_source_header_id ,
5009 demand_source_line ,
5010 demand_source_delivery ,
5011 item_segments ,
5012 item_description ,
5013 item_trx_enabled_flag ,
5014 item_location_control_code ,
5015 item_restrict_subinv_code ,
5016 item_restrict_locators_code ,
5017 item_revision_qty_control_code ,
5018 item_primary_uom_code ,
5019 item_uom_class ,
5020 item_shelf_life_code ,
5021 item_shelf_life_days ,
5022 item_lot_control_code ,
5023 item_serial_control_code ,
5024 item_inventory_asset_flag ,
5025 allowed_units_lookup_code ,
5026 department_id ,
5027 department_code ,
5028 wip_supply_type ,
5029 supply_subinventory ,
5030 supply_locator_id ,
5031 valid_subinventory_flag ,
5032 valid_locator_flag ,
5033 locator_segments ,
5034 current_locator_control_code ,
5035 number_of_lots_entered ,
5036 wip_commit_flag ,
5037 next_lot_number ,
5038 lot_alpha_prefix ,
5039 next_serial_number ,
5040 serial_alpha_prefix ,
5041 shippable_flag ,
5042 posting_flag ,
5043 required_flag ,
5044 process_flag ,
5045 ERROR_CODE ,
5046 error_explanation ,
5047 attribute_category ,
5048 attribute1 ,
5049 attribute2 ,
5050 attribute3 ,
5051 attribute4 ,
5052 attribute5 ,
5053 attribute6 ,
5054 attribute7 ,
5055 attribute8 ,
5056 attribute9 ,
5057 attribute10 ,
5058 attribute11 ,
5059 attribute12 ,
5060 attribute13 ,
5061 attribute14 ,
5062 attribute15 ,
5063 movement_id ,
5064 reservation_quantity ,
5065 shipped_quantity ,
5066 transaction_line_number ,
5067 task_id ,
5068 to_task_id ,
5069 source_task_id ,
5070 project_id ,
5071 source_project_id ,
5072 pa_expenditure_org_id ,
5073 to_project_id ,
5074 expenditure_type ,
5075 final_completion_flag ,
5076 transfer_percentage ,
5077 transaction_sequence_id ,
5078 material_account ,
5079 material_overhead_account ,
5080 resource_account ,
5081 outside_processing_account ,
5082 overhead_account ,
5083 flow_schedule ,
5084 cost_group_id ,
5085 demand_class ,
5086 qa_collection_id ,
5087 kanban_card_id ,
5088 overcompletion_transaction_qty ,
5089 overcompletion_primary_qty ,
5090 overcompletion_transaction_id ,
5091 end_item_unit_number ,
5092 scheduled_payback_date ,
5093 line_type_code ,
5094 parent_transaction_temp_id ,
5095 put_away_strategy_id ,
5096 put_away_rule_id ,
5097 pick_strategy_id ,
5098 pick_rule_id ,
5099 move_order_line_id ,
5100 task_group_id ,
5101 pick_slip_number ,
5102 reservation_id ,
5103 common_bom_seq_id ,
5104 common_routing_seq_id ,
5105 ussgl_transaction_code ,
5106 fulfillment_base --16574325
5107 FROM mtl_material_transactions_temp
5108 WHERE transaction_temp_id = p_orig_transaction_temp_id;
5109 x_return_status := 'S';
5110 EXCEPTION
5111 WHEN OTHERS THEN
5112 x_return_status := 'E';
5113 IF g_debug = 1 THEN
5114 mydebug( ' Error Code : '|| SQLCODE || ' Error Message :'||SQLERRM , 'split_mmtt');
5115 END IF;
5116 RETURN;
5117 END split_mmtt;
5118
5119 PROCEDURE split_wdt( p_new_task_id IN NUMBER ,
5120 p_new_transaction_temp_id IN NUMBER ,
5121 p_new_mol_id IN NUMBER ,
5122 p_orig_transaction_temp_id IN NUMBER ,
5123 x_return_status OUT NOCOPY VARCHAR2 ,
5124 x_msg_data OUT NOCOPY VARCHAR2 ,
5125 x_msg_count OUT NOCOPY VARCHAR2 ) IS
5126
5127 l_sysdate DATE := SYSDATE;
5128 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5129 BEGIN
5130 x_return_status := 'E';
5131 IF g_debug = 1 THEN
5132 mydebug( ' Entered ','SPLIT_WDT');
5133 END IF;
5134 INSERT
5135 INTO wms_dispatched_tasks
5136 (
5137 op_plan_instance_id ,
5138 task_method ,
5139 task_id ,
5140 transaction_temp_id ,
5141 organization_id ,
5142 user_task_type ,
5143 person_id ,
5144 effective_start_date ,
5145 effective_end_date ,
5146 equipment_id ,
5147 equipment_instance ,
5148 person_resource_id ,
5149 machine_resource_id ,
5150 status ,
5151 dispatched_time ,
5152 loaded_time ,
5153 drop_off_time ,
5154 last_update_date ,
5155 last_updated_by ,
5156 creation_date ,
5157 created_by ,
5158 last_update_login ,
5159 attribute_category ,
5160 attribute1 ,
5161 attribute2 ,
5162 attribute3 ,
5163 attribute4 ,
5164 attribute5 ,
5165 attribute6 ,
5166 attribute7 ,
5167 attribute8 ,
5168 attribute9 ,
5169 attribute10 ,
5170 attribute11 ,
5171 attribute12 ,
5172 attribute13 ,
5173 attribute14 ,
5174 attribute15 ,
5175 task_type ,
5176 priority ,
5177 task_group_id ,
5178 device_id ,
5179 device_invoked ,
5180 device_request_id ,
5181 suggested_dest_subinventory ,
5182 suggested_dest_locator_id ,
5183 operation_plan_id ,
5184 move_order_line_id ,
5185 transfer_lpn_id
5186 )
5187 SELECT op_plan_instance_id ,
5188 task_method ,
5189 p_new_task_id --task_id
5190 ,
5191 p_new_transaction_temp_id --transaction_temp_id
5192 ,
5193 organization_id ,
5194 user_task_type ,
5195 person_id ,
5196 effective_start_date ,
5197 effective_end_date ,
5198 equipment_id ,
5199 equipment_instance ,
5200 person_resource_id ,
5201 machine_resource_id ,
5202 status ,
5203 dispatched_time ,
5204 loaded_time ,
5205 drop_off_time ,
5206 l_sysdate --last_update_date
5207 ,
5208 FND_GLOBAL.USER_ID ,
5209 l_sysdate --creation_date
5210 ,
5211 FND_GLOBAL.USER_ID ,
5212 last_update_login ,
5213 attribute_category ,
5214 attribute1 ,
5215 attribute2 ,
5216 attribute3 ,
5217 attribute4 ,
5218 attribute5 ,
5219 attribute6 ,
5220 attribute7 ,
5221 attribute8 ,
5222 attribute9 ,
5223 attribute10 ,
5224 attribute11 ,
5225 attribute12 ,
5226 attribute13 ,
5227 attribute14 ,
5228 attribute15 ,
5229 task_type ,
5230 priority ,
5231 task_group_id ,
5232 device_id ,
5233 device_invoked ,
5234 device_request_id ,
5235 suggested_dest_subinventory ,
5236 suggested_dest_locator_id ,
5237 operation_plan_id ,
5238 p_new_mol_id ,
5239 transfer_lpn_id
5240 FROM wms_dispatched_tasks
5241 WHERE transaction_temp_id = p_orig_transaction_temp_id;
5242 x_return_status := 'S';
5243 EXCEPTION
5244 WHEN OTHERS THEN
5245 x_return_status := 'E';
5246 IF g_debug = 1 THEN
5247 mydebug( ' Error Code : '|| SQLCODE || ' Error Message :'||SQLERRM,'SPLIT_WDT');
5248 END IF;
5249 RETURN;
5250 END split_wdt;
5251
5252 PROCEDURE split_lot_serial( p_orig_transaction_temp_id IN NUMBER ,
5253 p_new_transaction_temp_id IN NUMBER ,
5254 p_transaction_qty_to_split IN NUMBER ,
5255 p_primary_qty_to_split IN NUMBER ,
5256 p_inventory_item_id IN NUMBER ,
5257 p_organization_id IN NUMBER ,
5258 x_return_status OUT NOCOPY VARCHAR2 ,
5259 x_msg_data OUT NOCOPY VARCHAR2 ,
5260 x_msg_count OUT NOCOPY VARCHAR2 ) IS
5261
5262 CURSOR C_MTLT
5263 IS
5264 SELECT rowid,
5265 mtlt.*
5266 FROM mtl_transaction_lots_temp mtlt
5267 WHERE transaction_temp_id = p_orig_transaction_temp_id
5268 ORDER BY lot_number;
5269 l_transaction_remaining_qty NUMBER;
5270 l_primary_remaining_qty NUMBER;
5271 l_txn_remaining_qty_mtlt NUMBER;
5272 l_prim_remaining_qty_mtlt NUMBER;
5273 l_lot_control_code NUMBER;
5274 l_serial_control_code NUMBER;
5275 l_new_serial_txn_temp_id NUMBER;
5276 l_lot_control_code NUMBER;
5277 l_serial_control_code NUMBER;
5278 x_lot_return_status VARCHAR2(1);
5279
5280 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5281 BEGIN
5282 x_return_status := 'E';
5283 IF g_debug = 1 THEN
5284 mydebug( 'Entered.', 'SPLIT_LOT_SERIAL');
5285 END IF;
5286 l_transaction_remaining_qty := p_transaction_qty_to_split;
5287 l_primary_remaining_qty := p_primary_qty_to_split;
5288 FOR mtlt IN C_MTLT
5289 LOOP
5290 IF g_debug = 1 THEN
5291 mydebug('In for loop(cursor mtlt) for transaction_temp_id : '||p_orig_transaction_temp_id||'l_transaction_remaining_qty : '||l_transaction_remaining_qty|| 'l_primary_remaining_qty : '||l_primary_remaining_qty, 'SPLIT_LOT_SERIAL');
5292 END IF;
5293 IF l_transaction_remaining_qty >= mtlt.transaction_quantity THEN
5294 -- Then this whole row can be consumed there is not need to split.
5295 -- Update the row with the new ttemp_id and transaction_quantity.
5296 -- Calculate remaining quantity.
5297 -- Update mtl_lot_number
5298 l_transaction_remaining_qty := l_transaction_remaining_qty - mtlt.transaction_quantity;
5299 l_primary_remaining_qty := l_primary_remaining_qty - mtlt.primary_quantity;
5300 UPDATE mtl_transaction_lots_temp
5301 SET transaction_temp_id = p_new_transaction_temp_id ,
5302 last_updated_by = FND_GLOBAL.USER_ID
5303 WHERE rowid = mtlt.rowid;
5304 IF l_transaction_remaining_qty = 0 THEN
5305 EXIT;
5306 END IF;
5307 ELSE
5308 -- Oops the mtlt quantity is bigger gotta split the row.
5309 -- Insert a new row with the transaction_quantity.
5310 -- Update the old row with the remaining quantity.
5311 -- Update mtl_lot_number
5312 split_mtlt (
5313 p_new_transaction_temp_id ,
5314 l_transaction_remaining_qty ,
5315 l_primary_remaining_qty ,
5316 mtlt.rowid ,
5317 x_lot_return_status ,
5318 x_msg_data ,
5319 x_msg_count );
5320
5321 IF mtlt.serial_transaction_temp_id IS NOT NULL THEN
5322 SELECT mtl_material_transactions_s.NEXTVAL
5323 INTO l_new_serial_txn_temp_id
5324 FROM dual;
5325 UPDATE mtl_transaction_lots_temp
5326 SET serial_transaction_temp_id = l_new_serial_txn_temp_id ,
5327 last_updated_by = FND_GLOBAL.USER_ID
5328 WHERE transaction_temp_id = p_new_transaction_temp_id
5329 AND lot_number = mtlt.lot_number;
5330 split_serial(
5331 p_orig_transaction_temp_id => mtlt.serial_transaction_temp_id ,
5332 p_new_transaction_temp_id => l_new_serial_txn_temp_id ,
5333 p_transaction_qty_to_split => l_transaction_remaining_qty ,
5334 p_primary_qty_to_split => l_primary_remaining_qty ,
5335 p_inventory_item_id => p_inventory_item_id ,
5336 p_organization_id => p_organization_id ,
5337 x_return_status => x_return_status ,
5338 x_msg_data => x_msg_data ,
5339 x_msg_count => x_msg_count );
5340 END IF;
5341 l_txn_remaining_qty_mtlt := mtlt.transaction_quantity - l_transaction_remaining_qty;
5342 l_prim_remaining_qty_mtlt := mtlt.primary_quantity - l_primary_remaining_qty;
5343 -- Update the remaining qty in the mtlt after insert.
5344 UPDATE mtl_transaction_lots_temp
5345 SET transaction_quantity = l_txn_remaining_qty_mtlt ,
5346 primary_quantity = l_prim_remaining_qty_mtlt ,
5347 last_updated_by = FND_GLOBAL.USER_ID
5348 WHERE rowid = mtlt.rowid;
5349 -- As the remaining quantity is already consumed we can safely exit
5350 EXIT ;
5351 END IF;
5352 END LOOP;
5353 x_return_status := 'S';
5354 EXCEPTION
5355 WHEN OTHERS THEN
5356 IF g_debug = 1 THEN
5357 mydebug( 'Error occurred : '|| SQLERRM, 'SPLIT_LOT_SERIAL');
5358 END IF;
5359 x_return_status := 'E';
5360 RETURN;
5361 END split_lot_serial;
5362
5363 PROCEDURE split_serial( p_orig_transaction_temp_id IN NUMBER ,
5364 p_new_transaction_temp_id IN NUMBER ,
5365 p_transaction_qty_to_split IN NUMBER ,
5366 p_primary_qty_to_split IN NUMBER ,
5367 p_inventory_item_id IN NUMBER ,
5368 p_organization_id IN NUMBER ,
5369 x_return_status OUT NOCOPY VARCHAR2 ,
5370 x_msg_data OUT NOCOPY VARCHAR2 ,
5371 x_msg_count OUT NOCOPY VARCHAR2 ) IS
5372
5373 CURSOR C_MSNT
5374 IS
5375 SELECT rowid,
5376 msnt.*
5377 FROM mtl_serial_numbers_temp msnt
5378 WHERE transaction_temp_id = p_orig_transaction_temp_id
5379 ORDER BY fm_serial_number;
5380
5381 l_transaction_remaining_qty NUMBER;
5382 l_primary_remaining_qty NUMBER;
5383 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5384 BEGIN
5385 x_return_status := 'E';
5386 l_transaction_remaining_qty := p_transaction_qty_to_split;
5387 l_primary_remaining_qty := p_primary_qty_to_split;
5388 IF g_debug = 1 THEN
5389 mydebug( 'In for loop(cursor msnt) for transaction_temp_id : '||p_orig_transaction_temp_id || 'l_transaction_remaining_qty : '||l_transaction_remaining_qty|| 'l_primary_remaining_qty : '||l_primary_remaining_qty, 'SPLIT_SERIAL');
5390 END IF;
5391 FOR msnt IN C_MSNT
5392 LOOP
5393 l_transaction_remaining_qty := l_transaction_remaining_qty - 1;
5394 UPDATE mtl_serial_numbers_temp
5395 SET transaction_temp_id = p_new_transaction_temp_id ,
5396 last_updated_by = FND_GLOBAL.USER_ID
5397 WHERE rowid = msnt.rowid;
5398 UPDATE mtl_serial_numbers msn
5399 SET msn.group_mark_id = p_new_transaction_temp_id ,
5400 last_updated_by = FND_GLOBAL.USER_ID
5401 WHERE msn.inventory_item_id = p_inventory_item_id
5402 AND serial_number = msnt.fm_serial_number
5403 AND current_organization_id = p_organization_id;
5404 IF l_transaction_remaining_qty = 0 THEN
5405 mydebug('All the quantity has been consumed, going back', 'SPLIT_SERIAL');
5406 EXIT;
5407 END IF;
5408 END LOOP;
5409 x_return_status := 'S';
5410 EXCEPTION
5411 WHEN OTHERS THEN
5412 x_return_status := 'E';
5413 RETURN;
5414 END split_serial;
5415
5416 PROCEDURE split_mtlt ( p_new_transaction_temp_id IN NUMBER ,
5417 p_transaction_qty_to_split IN NUMBER ,
5418 p_primary_qty_to_split IN NUMBER ,
5419 p_row_id IN ROWID ,
5420 x_return_status OUT NOCOPY VARCHAR2 ,
5421 x_msg_data OUT NOCOPY VARCHAR2 ,
5422 x_msg_count OUT NOCOPY VARCHAR2 ) IS
5423
5424 BEGIN
5425 x_return_status := 'E';
5426 INSERT
5427 INTO mtl_transaction_lots_temp
5428 (
5429 TRANSACTION_TEMP_ID ,
5430 LAST_UPDATE_DATE ,
5431 LAST_UPDATED_BY ,
5432 CREATION_DATE ,
5433 CREATED_BY ,
5434 LAST_UPDATE_LOGIN ,
5435 REQUEST_ID ,
5436 PROGRAM_APPLICATION_ID ,
5437 PROGRAM_ID ,
5438 PROGRAM_UPDATE_DATE ,
5439 TRANSACTION_QUANTITY ,
5440 PRIMARY_QUANTITY ,
5441 LOT_NUMBER ,
5442 LOT_EXPIRATION_DATE ,
5443 ERROR_CODE ,
5444 SERIAL_TRANSACTION_TEMP_ID ,
5445 GROUP_HEADER_ID ,
5446 PUT_AWAY_RULE_ID ,
5447 PICK_RULE_ID ,
5448 DESCRIPTION ,
5449 VENDOR_NAME ,
5450 SUPPLIER_LOT_NUMBER ,
5451 ORIGINATION_DATE ,
5452 DATE_CODE ,
5453 GRADE_CODE ,
5454 CHANGE_DATE ,
5455 MATURITY_DATE ,
5456 STATUS_ID ,
5457 RETEST_DATE ,
5458 AGE ,
5459 ITEM_SIZE ,
5460 COLOR ,
5461 VOLUME ,
5462 VOLUME_UOM ,
5463 PLACE_OF_ORIGIN ,
5464 BEST_BY_DATE ,
5465 LENGTH ,
5466 LENGTH_UOM ,
5467 RECYCLED_CONTENT ,
5468 THICKNESS ,
5469 THICKNESS_UOM ,
5470 WIDTH ,
5471 WIDTH_UOM ,
5472 CURL_WRINKLE_FOLD ,
5473 LOT_ATTRIBUTE_CATEGORY ,
5474 C_ATTRIBUTE1 ,
5475 C_ATTRIBUTE2 ,
5476 C_ATTRIBUTE3 ,
5477 C_ATTRIBUTE4 ,
5478 C_ATTRIBUTE5 ,
5479 C_ATTRIBUTE6 ,
5480 C_ATTRIBUTE7 ,
5481 C_ATTRIBUTE8 ,
5482 C_ATTRIBUTE9 ,
5483 C_ATTRIBUTE10 ,
5484 C_ATTRIBUTE11 ,
5485 C_ATTRIBUTE12 ,
5486 C_ATTRIBUTE13 ,
5487 C_ATTRIBUTE14 ,
5488 C_ATTRIBUTE15 ,
5489 C_ATTRIBUTE16 ,
5490 C_ATTRIBUTE17 ,
5491 C_ATTRIBUTE18 ,
5492 C_ATTRIBUTE19 ,
5493 C_ATTRIBUTE20 ,
5494 D_ATTRIBUTE1 ,
5495 D_ATTRIBUTE2 ,
5496 D_ATTRIBUTE3 ,
5497 D_ATTRIBUTE4 ,
5498 D_ATTRIBUTE5 ,
5499 D_ATTRIBUTE6 ,
5500 D_ATTRIBUTE7 ,
5501 D_ATTRIBUTE8 ,
5502 D_ATTRIBUTE9 ,
5503 D_ATTRIBUTE10 ,
5504 N_ATTRIBUTE1 ,
5505 N_ATTRIBUTE2 ,
5506 N_ATTRIBUTE3 ,
5507 N_ATTRIBUTE4 ,
5508 N_ATTRIBUTE5 ,
5509 N_ATTRIBUTE6 ,
5510 N_ATTRIBUTE7 ,
5511 N_ATTRIBUTE8 ,
5512 N_ATTRIBUTE9 ,
5513 N_ATTRIBUTE10 ,
5514 VENDOR_ID ,
5515 TERRITORY_CODE ,
5516 SUBLOT_NUM ,
5517 SECONDARY_QUANTITY ,
5518 SECONDARY_UNIT_OF_MEASURE ,
5519 QC_GRADE ,
5520 REASON_CODE ,
5521 PRODUCT_CODE ,
5522 PRODUCT_TRANSACTION_ID ,
5523 ATTRIBUTE_CATEGORY ,
5524 ATTRIBUTE1 ,
5525 ATTRIBUTE2 ,
5526 ATTRIBUTE3 ,
5527 ATTRIBUTE4 ,
5528 ATTRIBUTE5 ,
5529 ATTRIBUTE6 ,
5530 ATTRIBUTE7 ,
5531 ATTRIBUTE8 ,
5532 ATTRIBUTE9 ,
5533 ATTRIBUTE10 ,
5534 ATTRIBUTE11 ,
5535 ATTRIBUTE12 ,
5536 ATTRIBUTE13 ,
5537 ATTRIBUTE14 ,
5538 ATTRIBUTE15
5539 )
5540 SELECT p_new_transaction_temp_id --TRANSACTION_TEMP_ID
5541 ,
5542 sysdate --LAST_UPDATE_DATE
5543 ,
5544 FND_GLOBAL.USER_ID ,
5545 sysdate --CREATION_DATE
5546 ,
5547 FND_GLOBAL.USER_ID ,
5548 LAST_UPDATE_LOGIN ,
5549 REQUEST_ID ,
5550 PROGRAM_APPLICATION_ID ,
5551 PROGRAM_ID ,
5552 PROGRAM_UPDATE_DATE ,
5553 p_transaction_qty_to_split --TRANSACTION_QUANTITY
5554 ,
5555 p_primary_qty_to_split --PRIMARY_QUANTITY
5556 ,
5557 LOT_NUMBER ,
5558 LOT_EXPIRATION_DATE ,
5559 ERROR_CODE ,
5560 SERIAL_TRANSACTION_TEMP_ID ,
5561 GROUP_HEADER_ID ,
5562 PUT_AWAY_RULE_ID ,
5563 PICK_RULE_ID ,
5564 DESCRIPTION ,
5565 VENDOR_NAME ,
5566 SUPPLIER_LOT_NUMBER ,
5567 ORIGINATION_DATE ,
5568 DATE_CODE ,
5569 GRADE_CODE ,
5570 CHANGE_DATE ,
5571 MATURITY_DATE ,
5572 STATUS_ID ,
5573 RETEST_DATE ,
5574 AGE ,
5575 ITEM_SIZE ,
5576 COLOR ,
5577 VOLUME ,
5578 VOLUME_UOM ,
5579 PLACE_OF_ORIGIN ,
5580 BEST_BY_DATE ,
5581 LENGTH ,
5582 LENGTH_UOM ,
5583 RECYCLED_CONTENT ,
5584 THICKNESS ,
5585 THICKNESS_UOM ,
5586 WIDTH ,
5587 WIDTH_UOM ,
5588 CURL_WRINKLE_FOLD ,
5589 LOT_ATTRIBUTE_CATEGORY ,
5590 C_ATTRIBUTE1 ,
5591 C_ATTRIBUTE2 ,
5592 C_ATTRIBUTE3 ,
5593 C_ATTRIBUTE4 ,
5594 C_ATTRIBUTE5 ,
5595 C_ATTRIBUTE6 ,
5596 C_ATTRIBUTE7 ,
5597 C_ATTRIBUTE8 ,
5598 C_ATTRIBUTE9 ,
5599 C_ATTRIBUTE10 ,
5600 C_ATTRIBUTE11 ,
5601 C_ATTRIBUTE12 ,
5602 C_ATTRIBUTE13 ,
5603 C_ATTRIBUTE14 ,
5604 C_ATTRIBUTE15 ,
5605 C_ATTRIBUTE16 ,
5606 C_ATTRIBUTE17 ,
5607 C_ATTRIBUTE18 ,
5608 C_ATTRIBUTE19 ,
5609 C_ATTRIBUTE20 ,
5610 D_ATTRIBUTE1 ,
5611 D_ATTRIBUTE2 ,
5612 D_ATTRIBUTE3 ,
5613 D_ATTRIBUTE4 ,
5614 D_ATTRIBUTE5 ,
5615 D_ATTRIBUTE6 ,
5616 D_ATTRIBUTE7 ,
5617 D_ATTRIBUTE8 ,
5618 D_ATTRIBUTE9 ,
5619 D_ATTRIBUTE10 ,
5620 N_ATTRIBUTE1 ,
5621 N_ATTRIBUTE2 ,
5622 N_ATTRIBUTE3 ,
5623 N_ATTRIBUTE4 ,
5624 N_ATTRIBUTE5 ,
5625 N_ATTRIBUTE6 ,
5626 N_ATTRIBUTE7 ,
5627 N_ATTRIBUTE8 ,
5628 N_ATTRIBUTE9 ,
5629 N_ATTRIBUTE10 ,
5630 VENDOR_ID ,
5631 TERRITORY_CODE ,
5632 SUBLOT_NUM ,
5633 SECONDARY_QUANTITY ,
5634 SECONDARY_UNIT_OF_MEASURE ,
5635 QC_GRADE ,
5636 REASON_CODE ,
5637 PRODUCT_CODE ,
5638 PRODUCT_TRANSACTION_ID ,
5639 ATTRIBUTE_CATEGORY ,
5640 ATTRIBUTE1 ,
5641 ATTRIBUTE2 ,
5642 ATTRIBUTE3 ,
5643 ATTRIBUTE4 ,
5644 ATTRIBUTE5 ,
5645 ATTRIBUTE6 ,
5646 ATTRIBUTE7 ,
5647 ATTRIBUTE8 ,
5648 ATTRIBUTE9 ,
5649 ATTRIBUTE10 ,
5650 ATTRIBUTE11 ,
5651 ATTRIBUTE12 ,
5652 ATTRIBUTE13 ,
5653 ATTRIBUTE14 ,
5654 ATTRIBUTE15
5655 FROM mtl_transaction_lots_temp
5656 WHERE rowid = p_row_id;
5657 x_return_status := 'S';
5658 EXCEPTION
5659 WHEN OTHERS THEN
5660 x_return_status := 'E';
5661 RETURN;
5662 END split_mtlt;
5663
5664
5665 --End Bug 6682436
5666 END wms_picking_pkg;