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