[Home] [Help]
PACKAGE BODY: APPS.WMS_TXNRSN_ACTIONS_PUB
Source
1 PACKAGE BODY wms_txnrsn_actions_pub AS
2 /* $Header: WMSTRSAB.pls 120.29.12020000.3 2013/02/14 19:29:35 sahmahes ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'wms_txnrsn_actions_pub';
7 l_g_ret_sts_error CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_error;
8 l_g_ret_sts_unexp_error CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_unexp_error;
9 l_g_ret_sts_success CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_success;
10 g_trace_on CONSTANT NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11
12 g_debug NUMBER := 1;
13 g_module_name VARCHAR2(30) := NULL;
14
15 /*
16 -- Moved to spec for Opp Cyc Counting bug#9248808
17 PROCEDURE cleanup_task(
18 p_temp_id IN NUMBER
19 , p_qty_rsn_id IN NUMBER
20 , p_user_id IN NUMBER
21 , p_employee_id IN NUMBER
22 , p_envoke_workflow IN VARCHAR2
23 , x_return_status OUT NOCOPY VARCHAR2
24 , x_msg_count OUT NOCOPY NUMBER
25 , x_msg_data OUT NOCOPY VARCHAR2);
26
27 */
28
29 -- to turn off debugger, comment out the line 'dbms_output.put_line(msg);'
30 PROCEDURE mdebug(msg in VARCHAR2, module IN VARCHAR2 DEFAULT NULL)
31 IS
32 BEGIN
33 IF (g_debug = 1) THEN
34 inv_mobile_helper_functions.tracelog
35 (p_err_msg => msg,
36 p_module => g_pkg_name ||':'|| g_module_name || '.' || module,
37 p_level => 9);
38 END IF;
39 END;
40
41
42
43 PROCEDURE Inadequate_Qty
44 (
45 p_api_version_number IN NUMBER
46 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
47 , p_commit IN VARCHAR2 := FND_API.G_FALSE
48 , x_return_status OUT NOCOPY VARCHAR2
49 , x_msg_count OUT NOCOPY NUMBER
50 , x_msg_data OUT NOCOPY VARCHAR2
51 , p_organization_id IN NUMBER
52 , p_task_id IN NUMBER
53 , p_qty_picked IN NUMBER:=0
54 , p_qty_uom IN VARCHAR2
55 , p_carton_id IN VARCHAR2:= NULL
56 , p_user_id IN VARCHAR2
57 , p_reason_id IN NUMBER
58 )IS
59
60 l_msg_count NUMBER;
61 l_return_status VARCHAR2(10);
62 l_msg_data VARCHAR2(230);
63 l_carton_id VARCHAR2(60);
64 l_user_id VARCHAR2(60);
65 l_qty_picked NUMBER;
66 l_picked_uom VARCHAR2(3);
67 l_trans_uom VARCHAR2(3);
68 l_converted_qty NUMBER;
69 l_qty_diff_txn NUMBER;
70 l_qty_diff_prim NUMBER;
71 l_organization_id NUMBER;
72 l_mmtt_id NUMBER;
73 l_task_id NUMBER;
74 l_reason_id NUMBER;
75 l_item_id NUMBER;
76 l_revision VARCHAR2(3);
77 l_locator_id NUMBER;
78 l_sub_code VARCHAR2(10);
79 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
80 l_lot VARCHAR2(80);
81 l_discrepancy NUMBER;
82
83 l_mso_header_id NUMBER;
84 l_mso_line_id NUMBER;
85 l_reservation_id NUMBER;
86 l_missing_quantity NUMBER;
87 l_transaction_quantity NUMBER;
88 l_line_num NUMBER;
89 l_oe_header_id NUMBER;
90
91 l_mmtt_header_id NUMBER;
92
93
94 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
95 l_proc_name VARCHAR2(30) := 'Inadequate_Qty';
96 BEGIN
97
98 g_debug := l_debug;
99 g_module_name := l_proc_name;
100 l_carton_id:=p_carton_id;
101 l_user_id:=p_user_id;
102 l_qty_picked:=p_qty_picked;
103 l_picked_uom:=p_qty_uom;
104 l_organization_id:=p_organization_id;
105 l_task_id:=p_task_id;
106 l_reason_id:=p_reason_id;
107 l_discrepancy:=1;
108
109 -- Initialize API return status to success
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111
112
113 IF (l_debug = 1) THEN
114 mdebug('Inside wms_txnrsn_actions_pub.Inadequate Quantity: Before update quantity ');
115 END IF;
116
117 --Get MMTT id from WMS_Dispatched_tasks
118 SELECT transaction_temp_id
119 INTO l_mmtt_id
120 FROM wms_dispatched_tasks
121 WHERE task_id=p_task_id;
122 IF (l_debug = 1) THEN
123 mdebug('l_mmtt_id: '|| l_mmtt_id);
124 END IF;
125
126 SELECT inventory_item_id, locator_id,subinventory_code,revision,lot_number,
127 move_order_line_id, reservation_id, transaction_quantity,transaction_header_id
128 INTO l_item_id,l_locator_id, l_sub_code,l_revision,l_lot,
129 l_line_num, l_reservation_id,l_transaction_quantity,l_mmtt_header_id
130 FROM mtl_material_transactions_temp
131 WHERE transaction_temp_id=l_mmtt_id;
132 IF (l_debug = 1) THEN
133 mdebug('l_transaction_quantity: '|| l_transaction_quantity);
134 END IF;
135
136 -- Get UOM from MO Line which is the same as transaction_uom in MMTT
137 SELECT uom_code
138 INTO l_trans_uom
139 FROM mtl_txn_request_lines
140 WHERE line_id = l_line_num;
141 IF (l_debug = 1) THEN
142 mdebug('uom_code '|| l_trans_uom);
143 END IF;
144
145 l_qty_picked := INV_Convert.INV_UM_Convert(
146 item_id => l_item_id,
147 precision => null,
148 from_quantity => l_qty_picked,
149 from_unit => l_picked_uom,
150 to_unit => l_trans_uom,
151 from_name => null,
152 to_name => null);
153 l_qty_diff_txn := l_transaction_quantity - l_qty_picked;
154
155 IF (l_debug = 1) THEN
156 mdebug('before update mo line');
157 END IF;
158 UPDATE mtl_txn_request_lines
159 SET quantity_detailed = quantity_detailed-l_qty_diff_txn
160 WHERE line_id = l_line_num;
161
162 IF (l_debug = 1) THEN
163 mdebug('after update mo line');
164 END IF;
165 -- update the primary quantity and transaction quantity in MMTT
166
167
168 IF (l_debug = 1) THEN
169 mdebug('l_qty_picked'||l_qty_picked);
170 mdebug('l_qty_diff_txn'||l_qty_diff_txn);
174 p_item_id => l_item_id,
171 END IF;
172
173 l_qty_diff_prim := wms_task_dispatch_gen.get_primary_quantity(
175 p_organization_id => l_organization_id,
176 p_from_quantity => l_qty_diff_txn,
177 p_from_unit => l_trans_uom);
178
179 IF (l_debug = 1) THEN
180 mdebug('l_qty_diff_prim: '||l_qty_diff_prim);
181 mdebug('before update mmtt');
182 END IF;
183 UPDATE mtl_material_transactions_temp
184 SET primary_quantity = primary_quantity - l_qty_diff_txn,
185 transaction_quantity = l_transaction_quantity - l_qty_diff_prim
186 where transaction_temp_id=l_mmtt_id;
187
188 IF (l_debug = 1) THEN
189 mdebug('after update mmtt');
190 END IF;
191
192 SELECT oe_header_id
193 INTO l_oe_header_id
194 FROM wsh_inv_delivery_details_v
195 WHERE move_order_line_id=l_line_num
196 AND ROWNUM = 1; -- bug fix 1837592, if the same mol being detailed to
197 --multiple records, this query will return multiple rows.
198
199
200 -- Convert the demand source header id given
201 -- (the OE header id) to the MTL_SALES_ORDERS id to be used.
202 IF (l_debug = 1) THEN
203 mdebug('l_oe_header_id: '||l_oe_header_id);
204 END IF;
205
206 l_mso_header_id := inv_salesorder.get_salesorder_for_oeheader(l_oe_header_id);
207
208 IF l_mso_header_id IS NULL THEN
209 FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_GET_MSO_HEADER');
210 FND_MSG_PUB.Add;
211 RAISE fnd_api.g_exc_unexpected_error;
212 END IF;
213
214 -- get data for p_missing_quantity
215 l_missing_quantity := l_transaction_quantity - p_qty_picked;
216 IF (l_debug = 1) THEN
217 mdebug('l_missing_quantity: '||l_missing_quantity);
218 END IF;
219
220 -- for debugging
221 IF (l_debug = 1) THEN
222 mdebug('Before calling: inv_pick_release_pub.reserve_Unconfirmed_Quantity');
223 END IF;
224
225 -- Calling Reserve Unconfirmed Quantity API (from INVPPCIB.pls)
226 inv_pick_release_pub.reserve_Unconfirmed_Quantity
227 (
228 p_api_version => 1.0
229 ,p_init_msg_list => fnd_api.g_false
230 ,p_commit => fnd_api.g_false
231 ,x_return_status => l_return_status
232 ,x_msg_count => l_msg_count
233 ,x_msg_data => l_msg_data
234 ,p_missing_quantity => l_missing_quantity
235 ,p_reservation_id => l_reservation_id
236 ,p_demand_source_header_id => l_mso_header_id
237 ,p_demand_source_line_id => NULL
238 ,p_organization_id => l_organization_id
239 ,p_inventory_item_id => l_item_id
240 ,p_subinventory_code => l_sub_code
241 ,p_locator_id => l_locator_id
242 ,p_revision => l_revision
243 ,p_lot_number => l_lot
244 );
245 IF (l_debug = 1) THEN
246 mdebug ('x_return_status : '|| x_return_status );
247 END IF;
248
249 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
250 IF (l_debug = 1) THEN
251 mdebug(' inv_pick_release_pub.reserve_Unconfirmed_Quantity failed');
252 END IF;
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255
256 -- for debugging
257 IF (l_debug = 1) THEN
261 mdebug('Before calling: log_exception');
258 mdebug('After calling: inv_pick_release_pub.reserve_Unconfirmed_Quantity');
259 END IF;
260 IF (l_debug = 1) THEN
262 END IF;
263
264
265
266
267
268 -- Log Exception
269
270 Log_exception
271 ( 1.0
272 , fnd_api.g_false
273 , FND_API.G_false
274 , l_return_status
275 , l_msg_count
276 , l_msg_data
277 , l_organization_id
278 , l_mmtt_header_id
279 , l_task_id
280 , l_reason_id
281 , l_sub_code
282 , l_locator_id
283 , l_discrepancy
284 , l_user_id
285 , l_item_id
286 , l_revision
287 , l_lot
288 );
289 fnd_msg_pub.count_and_get
290 ( p_count => l_msg_count
291 , p_data => l_msg_data
292 );
293
294 IF (l_msg_count = 0) THEN
295 IF (l_debug = 1) THEN
296 mdebug('Successful');
297 END IF;
298 ELSIF (l_msg_count = 1) THEN
299 IF (l_debug = 1) THEN
300 mdebug('Not Successful');
301 mdebug(replace(l_msg_data,chr(0),' '));
302 END IF;
303 ELSE
304 IF (l_debug = 1) THEN
305 mdebug('Not Successful2');
306 END IF;
307 For I in 1..l_msg_count LOOP
308 l_msg_data := fnd_msg_pub.get(I,'F');
309 IF (l_debug = 1) THEN
310 mdebug(replace(l_msg_data,chr(0),' '));
311 END IF;
312 END LOOP;
313 END IF;
314
315
316 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
317 -- mdebug('FE');
318 FND_MSG_PUB.Add_Exc_Msg
319 ( 'Inadequate Qty'
320 , 'Calling Log Exception'
321 );
322 RAISE FND_API.G_EXC_ERROR;
323 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
324 IF (l_debug = 1) THEN
325 mdebug('SE');
326 END IF;
327 FND_MSG_PUB.Add_Exc_Msg
328 ( 'Inadequate Qty'
329 , 'Calling Log Exception'
330 );
331 RAISE FND_API.G_EXC_ERROR;
332 END IF;
333 IF (l_debug = 1) THEN
334 mdebug('end of amins api');
335 END IF;
336
337 EXCEPTION
338 WHEN FND_API.G_EXC_ERROR THEN
339 x_return_status:=FND_API.G_RET_STS_ERROR;
340 fnd_msg_pub.count_and_get
341 ( p_count => x_msg_count
342 , p_data => x_msg_data
343 );
344
345
346 WHEN OTHERS THEN
347
348 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
349 fnd_msg_pub.count_and_get
350 ( p_count => x_msg_count
351 , p_data => x_msg_data
352 );
353
354
355 END inadequate_qty;
356
357
358
359 PROCEDURE Suggest_alternate_location
360 (
361 p_api_version_number IN NUMBER
362 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
363 , p_commit IN VARCHAR2 := FND_API.G_FALSE
364 , x_return_status OUT NOCOPY VARCHAR2
365 , x_msg_count OUT NOCOPY NUMBER
366 , x_msg_data OUT NOCOPY VARCHAR2
367 , p_organization_id IN NUMBER
368 , p_mmtt_id IN NUMBER
369 , p_task_id IN NUMBER
370 , p_subinventory_code IN VARCHAR2
371 , p_locator_id IN NUMBER
372 , p_carton_id IN VARCHAR2:= NULL
373 , p_user_id IN VARCHAR2
374 , p_qty_picked IN NUMBER
375 , p_line_num IN NUMBER
376 ) IS
377
378
379 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
380 l_return_status VARCHAR2(10):= FND_API.G_RET_STS_SUCCESS;
381 l_msg_count NUMBER;
382 l_msg_data VARCHAR2(230);
383 l_trohdr_val_rec INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
384 l_commit VARCHAR2(1) := FND_API.G_FALSE;
385 l_order_count NUMBER := 1; /* total number of lines */
386
387 l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
388 l_trolin_rec INV_Move_Order_PUB.trolin_rec_type;
389 l_test mtl_txn_request_lines%ROWTYPE;
390
391 l_det_cnt NUMBER;
392 l_next_task_id NUMBER;
393 l_print_mode VARCHAR2(1):='E';
394 l_user_id VARCHAR2(60);
395
396
397 l_person_id NUMBER;
398 l_eqp_id NUMBER;
399 l_eqp_ins VARCHAR2(30);
400 l_per_res_id NUMBER;
401 l_mac_res_id NUMBER;
402 l_priority NUMBER;
403 l_mmtt_id NUMBER;
404 l_task_id NUMBER;
405 l_line_num NUMBER;
406
407 l_organization_id NUMBER;
408 l_standard_operation_id NUMBER;
409 l_transaction_temp_id NUMBER;
410 -- WF Fix Start
411 l_missing_quantity NUMBER;
412 l_oe_header_id NUMBER;
413 l_reservation_id NUMBER;
414 l_mso_header_id NUMBER;
415
416 l_item_id NUMBER;
417 l_sub_code VARCHAR2(10);
418 l_locator_id NUMBER;
419 l_revision VARCHAR2(3);
420 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
421 l_lot VARCHAR2(80);
422
423
424 l_serial_control_code NUMBER;
425 l_lot_control_code NUMBER;
426 l_num_of_rows NUMBER;
427 l_detailed_qty NUMBER;
428 l_sec_detailed_qty NUMBER := 0; -- Bug 8312574
429 l_rev VARCHAR2(3);
430 l_from_loc_id NUMBER;
431 l_to_loc_id NUMBER;
432 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
433 l_lot_number VARCHAR2(80);
434 l_expiration_date DATE;
435 v_transaction_temp_id NUMBER;
436 l_header_id NUMBER;
437 l_move_order_type NUMBER;
438 l_serial_flag VARCHAR2(1):='F';
439
440 l_mmtt_transaction_uom VARCHAR2(3);
441 l_mol_delta_qty NUMBER;
442 l_primary_qty NUMBER;
443 l_transaction_qty NUMBER;
444
445 l_old_quantity_detailed NUMBER;
446 l_old_sec_qty_detailed NUMBER;
447 l_new_quantity_detailed NUMBER;
448 l_new_sec_qty_detailed NUMBER;
449
450 l_primary_reservation_quantity NUMBER;
451 v_header_id NUMBER;
452 l_new_mmtt_cnt NUMBER;
453 l_old_mmtt_cnt NUMBER;
454 l_quantity_delivered NUMBER;
455 l_sec_qty_delivered NUMBER := 0; -- Bug 8312574
456
457 l_rsv_rec INV_Reservation_GLOBAL.MTL_RESERVATION_REC_TYPE;
458 l_dummy_sn INV_Reservation_Global.Serial_Number_Tbl_Type;
459 l_qty_succ_reserved NUMBER;
460 l_sec_qty_succ_reserved NUMBER;
461 l_cc_res_id NUMBER;
462 l_line_status NUMBER;
463 l_cc_transfer_flag VARCHAR2(1):='Y';
464 l_lot_qty NUMBER :=0;
465 l_detailed_quantity NUMBER;
466 l_to_account_id NUMBER; --BUG#3048061
467 l_new_mmtt_qty NUMBER :=0; --BUG#3278170
468
469 l_fm_serial_number VARCHAR2(30);
470 l_to_serial_number VARCHAR2(30);
471
472 l_msnt_cnt NUMBER;
473 l_serial_allocated_flag VARCHAR2(1);
474 l_mtlt_trans_qty NUMBER;
475
476 b_is_revision_control BOOLEAN;
477 b_is_lot_control BOOLEAN;
478 b_is_serial_control BOOLEAN;
479
480 l_qoh NUMBER;
481 l_rqoh NUMBER;
482 l_qr NUMBER;
483 l_qs NUMBER;
484 l_att NUMBER;
485 l_atr NUMBER;
486 l_cc_insert_flag VARCHAR2(1):='Y';
487 l_sqoh NUMBER;
488 l_srqoh NUMBER;
489 l_sqr NUMBER;
490 l_sqs NUMBER;
491 l_satt NUMBER;
492 l_satr NUMBER;
493 l_do_update_mmtt VARCHAR2(1); -- Bug : 6034090
494 l_allocated_lpn_id NUMBER; --Bug 7504490 - Added this variable to fetch the allocated_lpn_id from MMTT
495 -- Bug 8197499
496 l_secondary_quantity NUMBER;
497 l_sec_uom_code VARCHAR2(3);
498 l_sec_missing_qty NUMBER;
499 l_lot_sec_qty NUMBER;
500 l_mol_sec_delta_qty NUMBER;
501 l_sec_lot_qty NUMBER := 0;
502 l_fulfillment_base VARCHAR2(1) := 'P';
503 l_primary_uom VARCHAR2(3);
504
505
506 CURSOR get_mmtt_rows IS
507 SELECT organization_id,
508 standard_operation_id,
509 transaction_temp_id,
510 operation_plan_id,
511 move_order_line_id
512 FROM mtl_material_transactions_temp
513 WHERE move_order_line_id=l_line_num
514 AND transaction_temp_id <> l_mmtt_id;
515
516 CURSOR get_mtlt_c(p_temp_id NUMBER) IS
517 SELECT primary_quantity
518 , lot_number
519 , secondary_quantity -- bug 8197499
520 FROM mtl_transaction_lots_temp
521 WHERE transaction_temp_id = p_temp_id;
522
523 CURSOR c_fm_to_serial_number IS
524 SELECT
525 msnt.fm_serial_number,
526 msnt.to_serial_number
527 FROM mtl_serial_numbers_temp msnt
528 WHERE msnt.transaction_temp_id = p_mmtt_id;
529
530 CURSOR c_fm_to_lot_serial_number IS
531 SELECT
532 msnt.fm_serial_number,
533 msnt.to_serial_number
534 FROM
535 mtl_serial_numbers_temp msnt,
536 mtl_transaction_lots_temp mtlt
537 WHERE mtlt.transaction_temp_id = p_mmtt_id
538 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
539
540 -- WF Fix End
541
542 --8267628 Cursor c_distinct_lpn added as part of this bug
543 --8870624 Added NVL and ORDER BY below
544 CURSOR c_distinct_lpn IS
545 SELECT DISTINCT(NVL(lpn_id,-999))
546 FROM mtl_onhand_quantities_detail moqd
547 WHERE moqd.organization_id = l_organization_id
548 AND moqd.subinventory_code = p_subinventory_code
549 AND moqd.locator_id = p_locator_id
550 AND moqd.inventory_item_id = l_item_id
551 ORDER BY 1 DESC;
552
553 /*9301174*/
554 CURSOR C_DISTINCT_LOT_LPN_CUR (l_lot VARCHAR2) IS
555 SELECT DISTINCT(NVL(moqd.lpn_id,-999))
556 FROM mtl_onhand_quantities_detail moqd
557 WHERE moqd.organization_id = l_organization_id
558 AND moqd.subinventory_code = p_subinventory_code
559 AND moqd.locator_id = p_locator_id
560 AND moqd.inventory_item_id = l_item_id
561 AND moqd.lot_number = l_lot
562 ORDER BY 1 DESC;
563
564
565 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
566 l_proc_name VARCHAR2(30) := 'Suggest_alternate_location';
567 l_original_serial_number inv_reservation_global.serial_number_tbl_type; --Bug#8267628
568 l_new_rsv_id NUMBER ; --8557758
569 l_parent_line_id NUMBER := NULL ; --Bug8460179
570
571 BEGIN
572
573 g_debug := l_debug;
574 g_module_name := l_proc_name;
575 l_user_id:=p_user_id;
576 l_mmtt_id:=p_mmtt_id;
577 l_task_id:=p_task_id;
578 l_line_num:=p_line_num;
579 l_organization_id := p_organization_id;
580
581 IF (l_debug = 1) THEN
582 mdebug('Line Num: '|| l_line_num);
583 END IF;
584
585 l_trolin_rec:= inv_trolin_util.query_row(p_line_id => l_line_num);
586
587 l_item_id := l_trolin_rec.inventory_item_id;
588 l_header_id := l_trolin_rec.header_id;
589
590 mdebug('item'||l_item_id);
591 mdebug('l_header_id:'||l_header_id);
592 mdebug('mol uom code: '|| l_trolin_rec.uom_code);
593 mdebug('Inside Suggest Alternate Location');
594
595
596 IF l_header_id IS NOT NULL THEN
597
598 BEGIN
599 SELECT move_order_type
600 INTO l_move_order_type
601 FROM mtl_txn_request_headers
602 WHERE header_id=l_header_id;
603
604 mdebug('MO_Line: l_move_order_type: '||l_move_order_type);
605 EXCEPTION
606 WHEN OTHERS THEN
607 l_move_order_type := null;
608 mdebug('others exception in selecting move order type');
609 END;
610 ELSE
611 mdebug('l_header_id is null');
612 END IF;
613
614 BEGIN
615 SELECT revision
616 ,lot_number
617 ,reservation_id
618 ,primary_quantity
619 ,item_primary_uom_code
620 ,transaction_uom
621 ,transaction_quantity
622 ,transaction_source_id
623 ,allocated_lpn_id
624 ,secondary_transaction_quantity --bug 8197499
625 ,secondary_uom_code --bug 8197499
626 ,parent_line_id --bug 8460179
627 ,fulfillment_base
628 INTO l_revision
629 ,l_lot
630 ,l_reservation_id
631 ,l_primary_qty
632 ,l_primary_uom
633 ,l_mmtt_transaction_uom
634 ,l_transaction_qty
635 ,l_mso_header_id
636 ,l_allocated_lpn_id --bug 7504490 - Fetching allocated_lpn_id from MMTT
637 ,l_secondary_quantity --bug 8197499
638 ,l_sec_uom_code --bug 8197499
639 ,l_parent_line_id --bug 8460179
640 ,l_fulfillment_base
641 FROM mtl_material_transactions_temp
642 WHERE transaction_temp_id = l_mmtt_id;
643
644 mdebug('l_primary_uom :'|| l_primary_uom);
645 mdebug('primary_qty :'|| l_primary_qty);
646 mdebug('transaction_uom :'|| l_mmtt_transaction_uom);
647 mdebug('transaction_quantity: '|| l_transaction_qty);
648 mdebug('reservation_id : '|| l_reservation_id);
649 mdebug('secondary_uom_code : '|| l_sec_uom_code);
650 mdebug('secondary_transaction_quantity: '|| l_secondary_quantity);
651 mdebug('l_fulfillment_base : '|| l_fulfillment_base);
652
653
654 EXCEPTION
655 WHEN OTHERS THEN
656 mdebug('other exception encounted AA');
657 l_reservation_id := null;
658 l_mmtt_transaction_uom := null;
659 END;
660
661 IF l_revision IS NOT NULL THEN
662 b_is_revision_control := TRUE;
663 ELSE
664 b_is_revision_control := FALSE;
665 END IF;
666
667 SELECT serial_number_control_code, lot_control_code
668 INTO l_serial_control_code
669 ,l_lot_control_code
670 FROM mtl_system_items
671 WHERE inventory_item_id = l_item_id
672 AND organization_id = l_organization_id;
673
674 mdebug('l_serial_control_code: '|| l_serial_control_code);
675 mdebug('l_serial_flag: '|| l_serial_flag);
676 mdebug('l_header_id: '|| l_header_id);
677 mdebug('lot control code: '||l_lot_control_code);
678
679 IF l_serial_control_code NOT IN (1,6) THEN --?? should only be not equal 1
680 l_serial_flag := 'T';
681 b_is_serial_control := TRUE;
682 ELSE
683 b_is_serial_control := FALSE;
684 END IF;
685
686 IF l_lot_control_code = 2 THEN
687 b_is_lot_control := TRUE;
688 ELSE
689 b_is_lot_control := FALSE;
690 END IF;
691
692
693 /*Bug#9268209 including the move orders of type 5 that are generated from ingredient picking of OPM */
694 IF l_move_order_type IN (3,5) THEN--{
695 IF l_reservation_id IS NOT NULL THEN--{
696 -- get data for p_missing_quantity
697 IF l_lot_control_code = 2 THEN --{
698 mdebug('lot controlled item');
699 l_lot_qty := 0;
700 l_sec_lot_qty := 0; --bug 8197499
701 OPEN get_mtlt_c(l_mmtt_id);
702 LOOP
703 FETCH get_mtlt_c INTO l_missing_quantity, l_lot, l_sec_missing_qty; --bug 8197499
704 EXIT WHEN get_mtlt_c%NOTFOUND;
705 mdebug('l_missing_quantity:'||l_missing_quantity);
706 mdebug('l_lot:'||l_lot);
707 mdebug('l_sec_missing_qty:'||l_sec_missing_qty);
708 mdebug('Before calling: inv_pick_release_pub.reserve_Unconfirmed_lpn');
709
710 /* Bug 7504490 - Modified the call to the new API Reserve_Unconfqty_lpn for
711 both lpn and loose. Passing allocated_lpn_id to handle CC reservation for lpn*/
712
713 inv_pick_release_pub.Reserve_Unconfqty_lpn
714 (
715 p_api_version => 1.0
716 ,p_init_msg_list => fnd_api.g_false
717 ,p_commit => fnd_api.g_false
718 ,x_return_status => l_return_status
719 ,x_msg_count => l_msg_count
720 ,x_msg_data => l_msg_data
721 ,x_new_rsv_id => l_new_rsv_id -- bug8557788
722 ,p_missing_quantity => l_missing_quantity
723 ,p_secondary_missing_quantity => l_sec_missing_qty --bug9251210
724 ,p_reservation_id => l_reservation_id
725 ,p_demand_source_header_id => l_mso_header_id
726 ,p_demand_source_line_id => NULL
727 ,p_organization_id => l_organization_id
728 ,p_inventory_item_id => l_item_id
729 ,p_subinventory_code => p_subinventory_code
730 ,p_locator_id => p_locator_id
731 ,p_revision => l_revision
732 ,p_lot_number => l_lot
733 ,p_lpn_id => l_allocated_lpn_id
734 );
735
736 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
737 mdebug(' inv_pick_release_pub.Reserve_Unconfqty_lpn failed');
738 l_cc_transfer_flag := 'F';
739 l_lot_qty := l_lot_qty + l_missing_quantity;
740 l_sec_lot_qty := l_sec_lot_qty + l_sec_missing_qty; --bug 8197499
741 END IF;
742
743 IF l_allocated_lpn_id IS NULL THEN /*9251210-delete the remaing reservation.*/
744
745 mdebug('Before calling:INV_RESERVATION_PVT.delete_reservation(),with res_id :'||l_new_rsv_id);
746 l_rsv_rec.reservation_id := l_new_rsv_id; --l_reservation_id;
747 INV_RESERVATION_PVT.delete_reservation
748 (
749 p_api_version_number => 1.0
750 , p_init_msg_lst => fnd_api.g_false
751 , x_return_status => l_return_status
752 , x_msg_count => l_msg_count
753 , x_msg_data => l_msg_data
754 , p_rsv_rec => l_rsv_rec
755 , p_original_serial_number=> l_original_serial_number
756 , p_validation_flag => fnd_api.g_true
757 );
761 mdebug(' INV_RESERVATION_PVT.delete_reservation failed !!!');
758 mdebug ('l_return_status : '|| l_return_status );
759
760 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
762 l_cc_transfer_flag := 'F';
763 l_lot_qty := l_missing_quantity;
764 l_sec_lot_qty := l_sec_missing_qty;
765 END IF;
766 END IF; -- END of l_allocated_lpn_id
767
768 END LOOP;
769 CLOSE get_mtlt_c;
770
771 ELSE
772 mdebug('not lot controlled item');
773 l_missing_quantity := l_primary_qty;
774 l_sec_missing_qty := l_secondary_quantity; -- bug 8197499
775 mdebug('l_missing_quantity: '||l_missing_quantity);
776 mdebug('l_sec_missing_qty: ' ||l_sec_missing_qty);
777 l_lot_qty := 0;
778 l_lot_sec_qty := 0; --bug 8197499
779
780 --Incase of allocated LPN -> not null , reserve_Unconfirmed_lpn() is called as per Bug 7504490
781 --Incase of allocated LPN -> null, delete_reservation() is called as per Bug#8267628.
782 --Bug 7504490 - Modified the call to the new API Reserve_Unconfqty_lpn for
783 --both lpn and loose. Passing allocated_lpn_id to handle CC reservation for lpn
784
785
786 mdebug('Before calling: inv_pick_release_pub.reserve_Unconfirmed_lpn');
787
788 inv_pick_release_pub.Reserve_Unconfqty_lpn (
789 p_api_version => 1.0
790 ,p_init_msg_list => fnd_api.g_false
791 ,p_commit => fnd_api.g_false
792 ,x_return_status => l_return_status
793 ,x_msg_count => l_msg_count
794 ,x_msg_data => l_msg_data
795 ,x_new_rsv_id => l_new_rsv_id --bug8557758
796 ,p_secondary_missing_quantity => l_sec_missing_qty --bug9251210
797 ,p_missing_quantity => l_missing_quantity
798 ,p_reservation_id => l_reservation_id
799 ,p_demand_source_header_id => l_mso_header_id
800 ,p_demand_source_line_id => NULL
801 ,p_organization_id => l_organization_id
802 ,p_inventory_item_id => l_item_id
803 ,p_subinventory_code => p_subinventory_code
804 ,p_locator_id => p_locator_id
805 ,p_revision => l_revision
806 ,p_lot_number => NULL
807 ,p_lpn_id => l_allocated_lpn_id
808 );
809
810 mdebug ('l_return_status : '|| l_return_status );
811
812 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
813 mdebug(' INV_RESERVATION_PVT.Reserve_Unconfqty_lpn failed');
814 l_cc_transfer_flag := 'F';
815 l_lot_qty := l_missing_quantity;
816 END IF;
817
818 IF l_allocated_lpn_id IS NULL THEN
819 mdebug('Before calling:INV_RESERVATION_PVT.delete_reservation(),with res_id :'||l_new_rsv_id);
820 l_rsv_rec.reservation_id := l_new_rsv_id; --l_reservation_id;
821
822 INV_RESERVATION_PVT.delete_reservation (
823 p_api_version_number => 1.0
824 ,p_init_msg_lst => fnd_api.g_false
825 ,x_return_status => l_return_status
826 ,x_msg_count => l_msg_count
827 ,x_msg_data => l_msg_data
828 ,p_rsv_rec => l_rsv_rec
829 ,p_original_serial_number=> l_original_serial_number
830 ,p_validation_flag => fnd_api.g_true);
831
832 mdebug ('l_return_status : '|| l_return_status );
833
834 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
835 mdebug(' INV_RESERVATION_PVT.delete_reservation failed');
836 l_cc_transfer_flag := 'F';
837 l_lot_qty := l_missing_quantity;
838 l_sec_lot_qty := l_sec_missing_qty; --bug 8197499
839 END IF;
840 END IF; -- End of l_allocated_lpn_id
841 END IF; --} End of Lot controlled.
842 mdebug('After calling: inv_pick_release_pub.reserve_Unconfirmed_Quantity');
843 ELSE
844 mdebug('l_reservation_id is null');
845 END IF;--}
846 END IF;--}
847
848 -- zero out the quantity of mmtt in order to do pick release again
849 -- delete task
850 DELETE FROM wms_dispatched_tasks
851 WHERE transaction_temp_id = l_mmtt_id;
852
853 -- transfer reservation is taking care of detailed_quantity in mtl_reservations
854 -- zero the quantity for original mmtt line so pick release is going to work
855
856 mdebug('l_parent_line_id:'||l_parent_line_id);
857
858 /* Bug 7504490 - Commented out the check for l_do_update_mmtt. The MMTT has to be
859 updated to 0 quantity for the quantity tree to fetch correct quantity to reserve */
860
861 UPDATE mtl_material_transactions_temp
862 SET primary_quantity = 0
863 ,transaction_quantity = 0
864 ,secondary_transaction_quantity = DECODE(secondary_uom_code,NULL,NULL,0)
865 WHERE transaction_temp_id IN (l_mmtt_id ,l_parent_line_id) --8460179 , added l_parent_line_id
866 AND organization_id = l_organization_id;
867
868 --for lot controled item delete the mtlt record, in order to call pick release again
869 IF l_lot_control_code = 2 THEN --{lot controlled item
870 IF l_serial_control_code NOT IN (1,6) THEN --{serial controlled
874 FROM mtl_transaction_lots_temp mtlt
871 BEGIN
872 SELECT COUNT(*)
873 INTO l_msnt_cnt
875 ,mtl_serial_numbers_temp msnt
876 WHERE mtlt.transaction_temp_id = l_mmtt_id
877 AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id;
878
879 mdebug('l_msnt_cnt:'||l_msnt_cnt);
880 EXCEPTION
881 WHEN OTHERS THEN
882 mdebug('other exception encounted. set l_msnt_cnt to 0');
883 l_msnt_cnt := 0;
884 END;
885 END IF;--}
886
887 ELSE --non lot controlled item
888 IF l_serial_control_code NOT IN (1,6) THEN --{serial controlled
889 BEGIN
890 SELECT COUNT(*)
891 INTO l_msnt_cnt
892 FROM mtl_serial_numbers_temp msnt
893 WHERE transaction_temp_id = l_mmtt_id;
894
895 mdebug('l_msnt_cnt:'||l_msnt_cnt);
896 EXCEPTION
897 WHEN OTHERS THEN
898 mdebug('other exception encounted. set l_msnt_cnt to 0');
899 l_msnt_cnt := 0;
900 END;
901 END IF;--}
902 END IF; --}
903
904 IF l_msnt_cnt > 0 THEN
905 l_serial_allocated_flag := 'Y';
906 ELSE
907 l_serial_allocated_flag := 'N';
908 END IF;
909
910 mdebug('l_serial_allocated_flag:'||l_serial_allocated_flag);
911
912 IF l_lot_control_code > 1 THEN--{ Lot controlled item
913
914 IF l_serial_control_code NOT IN (1,6) AND l_serial_allocated_flag = 'Y' THEN --{
915 BEGIN -- Lot and Serial controlled item
916 mdebug('lot + serial controlled item');
917 mdebug('p_mmtt_id:'||p_mmtt_id);
918 mdebug('l_mmtt_id:'||l_mmtt_id);
919 OPEN c_fm_to_lot_serial_number;
920 LOOP
921 mdebug('inside the loop');
922 FETCH c_fm_to_lot_serial_number
923 INTO l_fm_serial_number,l_to_serial_number;
924 mdebug('after fetch');
925 EXIT WHEN c_fm_to_lot_serial_number%NOTFOUND;
926
927 mdebug('within loop before update msn for from serial_number:'||l_fm_serial_number);
928
929 UPDATE mtl_serial_numbers
930 SET group_mark_id = NULL
931 WHERE inventory_item_id = l_item_id
932 AND current_organization_id = l_organization_id
933 AND serial_number BETWEEN l_fm_serial_number AND l_to_serial_number;
934
935 mdebug('within loop after update msn for to serial_number :' || l_to_serial_number);
936 END LOOP;
937 CLOSE c_fm_to_lot_serial_number;
938
939 mdebug('before delete msnt');
940
941 DELETE FROM mtl_serial_numbers_temp msnt
942 WHERE msnt.transaction_temp_id IN
943 (SELECT mtlt.serial_transaction_temp_id
944 FROM mtl_transaction_lots_temp mtlt
945 WHERE mtlt.transaction_temp_id = l_mmtt_id);
946 mdebug('after delete msnt');
947
948 EXCEPTION
949 WHEN no_data_found THEN
950 mdebug(' cursor returns no data');
951 WHEN OTHERS THEN
952 mdebug('other exception occurs');
953 END;
954
955 ELSE
956 mdebug('only lot controlled item');
957 END IF;--}
958
959 --DELETE FROM mtl_transaction_lots_temp mtlt
960 --WHERE mtlt.transaction_temp_id = l_mmtt_id;
961 -- we need to use mtlt later and so far just zero out mtlt, later will delete them
962 mdebug('zero out quantity of the mtlt for lot controlled item');
963 UPDATE mtl_transaction_lots_temp
964 SET primary_quantity = 0
965 , transaction_quantity = 0
966 , secondary_quantity = DECODE(secondary_quantity,NULL,NULL,0) --bug 8197499
967 WHERE transaction_temp_id IN ( l_mmtt_id ,l_parent_line_id) ; /*9251210*/
968
969 ELSIF l_serial_control_code NOT IN (1,6) AND l_serial_allocated_flag = 'Y' THEN
970
971 mdebug('serial controlled item');
972 OPEN c_fm_to_serial_number;
973 LOOP
974 FETCH c_fm_to_serial_number
975 INTO l_fm_serial_number,l_to_serial_number;
976 EXIT WHEN c_fm_to_serial_number%NOTFOUND;
977
978 UPDATE mtl_serial_numbers
979 SET group_mark_id = NULL
980 WHERE inventory_item_id = l_item_id
981 AND current_organization_id = l_organization_id
982 AND serial_number BETWEEN l_fm_serial_number AND l_to_serial_number;
983
984 END LOOP;
985 CLOSE c_fm_to_serial_number;
986
987 DELETE FROM mtl_serial_numbers_temp msnt
988 WHERE msnt.transaction_temp_id = l_mmtt_id;
989
990 END IF; --}
991
992 l_return_status := FND_API.G_RET_STS_SUCCESS;
993
994 -- l_move_order_type <> 3 need to create a cycle count reservation
995 -- need to create cycle count reservation for remaining qty in the sub/loc
996 mdebug('before create cycle count reservation');
997 l_rsv_rec.reservation_id := NULL; --cannot know
998 l_rsv_rec.requirement_date := SYSDATE;
999 l_rsv_rec.organization_id := l_organization_id;
1000 l_rsv_rec.inventory_item_id := l_item_id;
1001 l_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_cycle_count;
1002 l_rsv_rec.demand_source_name := NULL;
1003 l_rsv_rec.demand_source_header_id := -1; --l_header_id;
1004 l_rsv_rec.demand_source_line_id := -1; --l_line_num;
1005 l_rsv_rec.demand_source_delivery := NULL;
1006 l_rsv_rec.primary_uom_code := NULL;
1007 l_rsv_rec.primary_uom_id := NULL;
1008 l_rsv_rec.secondary_uom_code := NULL;
1009 l_rsv_rec.secondary_uom_id := NULL;
1010 l_rsv_rec.reservation_uom_code := NULL;
1011 l_rsv_rec.reservation_uom_id := NULL;
1012 l_rsv_rec.reservation_quantity := NULL; --l_transaction_qty;
1013 l_rsv_rec.primary_reservation_quantity := l_primary_qty;
1014 l_rsv_rec.secondary_reservation_quantity:= l_secondary_quantity; --bug 8197499
1015 l_rsv_rec.autodetail_group_id := NULL;
1016 l_rsv_rec.external_source_code := NULL;
1017 l_rsv_rec.external_source_line_id := NULL;
1018 l_rsv_rec.supply_source_type_id := INV_Reservation_GLOBAL.g_source_type_inv;
1019 l_rsv_rec.supply_source_header_id := NULL;
1020 l_rsv_rec.supply_source_line_id := NULL;
1021 l_rsv_rec.supply_source_name := NULL;
1022 l_rsv_rec.supply_source_line_detail := NULL;
1023 l_rsv_rec.revision := l_revision;
1024 l_rsv_rec.subinventory_code := p_subinventory_code;
1025 l_rsv_rec.subinventory_id := NULL;
1026 l_rsv_rec.locator_id := p_locator_id;
1027 l_rsv_rec.lot_number := NULL;
1028 l_rsv_rec.lot_number_id := NULL;
1029 l_rsv_rec.pick_slip_number := NULL;
1030 /* Bug 7504490 - Passing the allocated_lpn_id to the cycle count reservation record */
1031 l_rsv_rec.lpn_id := l_allocated_lpn_id;
1032 l_rsv_rec.attribute_category := NULL;
1033 l_rsv_rec.attribute1 := NULL;
1034 l_rsv_rec.attribute2 := NULL;
1035 l_rsv_rec.attribute3 := NULL;
1036 l_rsv_rec.attribute4 := NULL;
1037 l_rsv_rec.attribute5 := NULL;
1038 l_rsv_rec.attribute6 := NULL;
1039 l_rsv_rec.attribute7 := NULL;
1040 l_rsv_rec.attribute8 := NULL;
1041 l_rsv_rec.attribute9 := NULL;
1042 l_rsv_rec.attribute10 := NULL;
1043 l_rsv_rec.attribute11 := NULL;
1044 l_rsv_rec.attribute12 := NULL;
1045 l_rsv_rec.attribute13 := NULL;
1046 l_rsv_rec.attribute14 := NULL;
1047 l_rsv_rec.attribute15 := NULL;
1048 l_rsv_rec.ship_ready_flag := NULL;
1049 l_rsv_rec.detailed_quantity := NULL;
1050
1051 mdebug('create new reservation');
1052
1053 IF l_lot_control_code = 2 THEN
1054 mdebug('lot controlled item');
1055 l_lot_qty := 0;
1056 l_sec_lot_qty := 0; --bug 8197499
1057 OPEN get_mtlt_c(l_mmtt_id);
1058 LOOP
1059 l_qty_succ_reserved := 0;
1060 l_cc_res_id := 0;
1061 FETCH get_mtlt_c INTO l_rsv_rec.primary_reservation_quantity
1062 , l_rsv_rec.lot_number
1063 , l_rsv_rec.secondary_reservation_quantity; --bug 8197499
1064 EXIT WHEN get_mtlt_c%NOTFOUND;
1065 mdebug('l_missing_quantity:'|| l_rsv_rec.primary_reservation_quantity);
1066 mdebug('l_lot:'||l_rsv_rec.lot_number);
1067 mdebug('sec qty :'||l_rsv_rec.secondary_reservation_quantity);
1068
1069 IF (l_allocated_lpn_id IS NOT NULL ) THEN --9301174 added IF
1070 mdebug('l_allocated_lpn_id => '||l_allocated_lpn_id);
1071 -- calling query quantity tree API to get l_atr
1072 mdebug('calling quantity tree API');
1073 inv_quantity_tree_pub.clear_quantity_cache;
1074 /* Bug 7504490- Passing allocated_lpn_id to query_quantities to fetch the quantity to reserve
1075 for the LPN when the MMTT is for an allocated lpn */
1076 inv_quantity_tree_pub.query_quantities
1077 ( p_api_version_number => 1.0
1078 , p_init_msg_lst => fnd_api.g_false
1079 , x_return_status => l_return_status
1080 , x_msg_count => l_msg_count
1081 , x_msg_data => l_msg_data
1082 , p_organization_id => l_organization_id
1083 , p_inventory_item_id => l_item_id
1084 , p_tree_mode => INV_Quantity_Tree_PUB.g_reservation_mode
1085 , p_is_revision_control => b_is_revision_control
1086 , p_is_lot_control => b_is_lot_control
1087 , p_is_serial_control => b_is_serial_control
1088 , p_grade_code => null
1089 , p_demand_source_type_id => -9999
1090 , p_revision => l_revision
1091 , p_lot_number => l_rsv_rec.lot_number
1092 , p_subinventory_code => p_subinventory_code
1093 , p_locator_id => p_locator_id
1094 , p_lpn_id => l_allocated_lpn_id
1095 , x_qoh => l_qoh
1096 , x_rqoh => l_rqoh
1097 , x_qr => l_qr
1098 , x_qs => l_qs
1099 , x_att => l_att
1100 , x_atr => l_atr
1101 , x_sqoh => l_sqoh
1102 , x_srqoh => l_srqoh
1103 , x_sqr => l_sqr
1104 , x_sqs => l_sqs
1105 , x_satt => l_satt
1106 , x_satr => l_satr
1107 );
1108 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1109 mdebug('after calling qty tree l_atr:' || l_atr||' l_att: '||l_att);
1110 mdebug('after calling qty tree l_atr:' || l_satr);
1111 mdebug('after calling qty tree l_qoh:' || l_qoh ||' l_rqoh: '||l_rqoh||' l_qr:'||l_qr||' l_qs:'||l_qs);
1112 ELSE
1113 mdebug('calling qty tree API failed ');
1114 IF l_move_order_type = 3 THEN
1115 l_atr := 0;
1116 l_satr := 0;
1117 ELSE
1118 l_atr := l_rsv_rec.primary_reservation_quantity;
1119 l_satr :=l_rsv_rec.secondary_reservation_quantity;
1120 END IF;
1121 END IF;
1122
1123 mdebug('after calling quantity tree ARI, l_atr:'||l_atr);
1124 mdebug('after calling quantity tree ARI, l_satr:'||l_satr);
1125
1126
1127 l_rsv_rec.primary_reservation_quantity := l_atr;
1128 l_rsv_rec.secondary_reservation_quantity := l_satr;
1129
1130 IF l_atr <> 0 THEN
1131
1132 mdebug('Before calling: inv_reservation_pvt.create_reservation');
1133
1134 l_return_status := FND_API.G_RET_STS_SUCCESS;
1135
1136 INV_Reservation_pvt.Create_Reservation(
1137 p_api_version_number => 1.0
1138 , p_init_msg_lst => fnd_api.g_false
1139 , x_return_status => l_return_status
1140 , x_msg_count => l_msg_count
1141 , x_msg_data => l_msg_data
1142 , p_rsv_rec => l_rsv_rec
1143 , p_serial_number => l_dummy_sn
1144 , x_serial_number => l_dummy_sn
1145 , p_partial_reservation_flag => fnd_api.g_false
1146 , p_force_reservation_flag => fnd_api.g_false
1147 , p_validation_flag => fnd_api.g_false
1148 , x_quantity_reserved => l_qty_succ_reserved
1149 , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1150 , x_reservation_id => l_cc_res_id
1151 );
1152 -- Return an error if the create reservation call failed
1153 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1154 mdebug('error in create reservation');
1155 l_cc_insert_flag := 'F';
1156 ELSE
1157 mdebug('l_qty_succ_reserved :'|| l_qty_succ_reserved);
1158 mdebug('l_cc_res_id: '|| l_cc_res_id);
1159 END IF;
1160 ELSE
1161 mdebug(' l_atr is 0, no need to call create reservation API');
1162 END IF;
1163 ELSE --l_allocated_lpn_id is NULL --9301174 starts
1164 mdebug('l_allocated_lpn_id is null ');
1165 inv_quantity_tree_pub.clear_quantity_cache;
1166
1167 OPEN C_DISTINCT_LOT_LPN_CUR(l_rsv_rec.lot_number);
1168 LOOP
1169 FETCH C_DISTINCT_LOT_LPN_CUR INTO l_rsv_rec.lpn_id;
1170 EXIT WHEN C_DISTINCT_LOT_LPN_CUR%NOTFOUND;
1171 IF (l_rsv_rec.lpn_id = -999 ) THEN
1172 l_rsv_rec.lpn_id :=NULL;
1173 END IF;
1174
1175 mdebug('lpn_id :'||l_rsv_rec.lpn_id );
1176 inv_quantity_tree_pub.query_quantities
1177 ( p_api_version_number => 1.0
1178 , p_init_msg_lst => fnd_api.g_false
1179 , x_return_status => l_return_status
1180 , x_msg_count => l_msg_count
1181 , x_msg_data => l_msg_data
1182 , p_organization_id => l_organization_id
1183 , p_inventory_item_id => l_item_id
1184 , p_tree_mode => INV_Quantity_Tree_PUB.g_reservation_mode
1185 , p_is_revision_control => b_is_revision_control
1186 , p_is_lot_control => b_is_lot_control
1187 , p_is_serial_control => b_is_serial_control
1188 , p_grade_code => null
1189 , p_demand_source_type_id => -9999
1190 , p_revision => l_revision
1191 , p_lot_number => l_rsv_rec.lot_number
1192 , p_subinventory_code => p_subinventory_code
1193 , p_locator_id => p_locator_id
1194 , p_lpn_id => l_rsv_rec.lpn_id
1195 , x_qoh => l_qoh
1196 , x_rqoh => l_rqoh
1197 , x_qr => l_qr
1198 , x_qs => l_qs
1199 , x_att => l_att
1200 , x_atr => l_atr
1201 , x_sqoh => l_sqoh
1202 , x_srqoh => l_srqoh
1203 , x_sqr => l_sqr
1204 , x_sqs => l_sqs
1205 , x_satt => l_satt
1206 , x_satr => l_satr
1207 );
1208 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1209 mdebug('after calling qty tree l_atr:' || l_atr||' l_att: '||l_att);
1210 mdebug('after calling qty tree l_atr:' || l_satr);
1211 mdebug('after calling qty tree l_qoh:' || l_qoh ||' l_rqoh: '||l_rqoh||' l_qr:'||l_qr||' l_qs:'||l_qs);
1212 ELSE
1213 mdebug('calling qty tree API failed ');
1214 if l_move_order_type = 3 then
1215 l_atr := 0;
1216 l_satr:= 0;
1217 else
1218 l_atr := l_rsv_rec.primary_reservation_quantity;
1219 l_satr:= l_rsv_rec.secondary_reservation_quantity;
1220 end if;
1221 END IF;
1222
1223 mdebug('after calling quantity tree ARI, l_atr:'||l_atr);
1224 mdebug('after calling quantity tree ARI, l_satr:'||l_satr);
1225
1226
1227 l_rsv_rec.primary_reservation_quantity := l_atr;
1228 l_rsv_rec.secondary_reservation_quantity := l_satr;
1229
1230 IF l_atr <> 0 THEN
1231
1232 mdebug('Before calling: inv_reservation_pvt.create_reservation');
1233 l_return_status := FND_API.G_RET_STS_SUCCESS;
1234
1235 INV_Reservation_pvt.Create_Reservation(
1236 p_api_version_number => 1.0
1237 , p_init_msg_lst => fnd_api.g_false
1238 , x_return_status => l_return_status
1239 , x_msg_count => l_msg_count
1240 , x_msg_data => l_msg_data
1241 , p_rsv_rec => l_rsv_rec
1242 , p_serial_number => l_dummy_sn
1243 , x_serial_number => l_dummy_sn
1244 , p_partial_reservation_flag => fnd_api.g_false
1245 , p_force_reservation_flag => fnd_api.g_false
1246 , p_validation_flag => fnd_api.g_false
1247 , x_quantity_reserved => l_qty_succ_reserved
1248 , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1249 , x_reservation_id => l_cc_res_id
1250 );
1251 -- Return an error if the create reservation call failed
1252 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1253 mdebug('error in create reservation');
1254 l_cc_insert_flag := 'F';
1255 ELSE
1256 mdebug('l_qty_succ_reserved :'|| l_qty_succ_reserved);
1257 mdebug('l_cc_res_id: '|| l_cc_res_id);
1258 END IF;
1262 inv_quantity_tree_pub.clear_quantity_cache;
1259 ELSE
1260 mdebug(' l_atr is 0, no need to call create reservation API');
1261 END IF;
1263 END LOOP;
1264 CLOSE C_DISTINCT_LOT_LPN_CUR;
1265 END IF; --allcoated_lpn --9301174 Ends
1266
1267 END LOOP;
1268 CLOSE get_mtlt_c;
1269 ELSE
1270 mdebug('not lot controlled item');
1271
1272 IF l_allocated_lpn_id IS NOT NULL THEN
1273
1274 mdebug('calling quantity tree API with lpn_id :'||l_allocated_lpn_id);
1275
1276 inv_quantity_tree_pub.clear_quantity_cache;
1277
1278 /* Bug 7504490- Passing allocated_lpn_id to query_quantities to fetch the quantity to reserve
1279 for the LPN when the MMTT is for an allocated lpn */
1280
1281 inv_quantity_tree_pub.query_quantities
1282 ( p_api_version_number => 1.0
1283 , p_init_msg_lst => fnd_api.g_false
1284 , x_return_status => l_return_status
1285 , x_msg_count => l_msg_count
1286 , x_msg_data => l_msg_data
1287 , p_organization_id => l_organization_id
1288 , p_inventory_item_id => l_item_id
1289 , p_tree_mode => INV_Quantity_Tree_PUB.g_reservation_mode
1290 , p_is_revision_control => b_is_revision_control
1291 , p_is_lot_control => b_is_lot_control
1292 , p_is_serial_control => b_is_serial_control
1293 , p_grade_code => null
1294 , p_demand_source_type_id => -9999
1295 , p_revision => l_revision
1296 , p_lot_number => null
1297 , p_subinventory_code => p_subinventory_code
1298 , p_locator_id => p_locator_id
1299 , p_lpn_id => l_allocated_lpn_id --Bug#7504490
1300 , x_qoh => l_qoh
1301 , x_rqoh => l_rqoh
1302 , x_qr => l_qr
1303 , x_qs => l_qs
1304 , x_att => l_att
1305 , x_atr => l_atr
1306 , x_sqoh => l_sqoh
1307 , x_srqoh => l_srqoh
1308 , x_sqr => l_sqr
1309 , x_sqs => l_sqs
1310 , x_satt => l_satt
1311 , x_satr => l_satr
1312 );
1313 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1314 mdebug('after calling qty tree l_atr:' || l_atr||' l_att:'||l_att);
1315 mdebug('after calling qty tree l_qoh:' || l_qoh ||' l_rqoh: '||l_rqoh||' l_qr:'||l_qr||' l_qs:'||l_qs);
1316 ELSE
1317 mdebug('calling qty tree API failed ');
1318 l_atr := 0;
1319 IF l_move_order_type = 3 THEN
1320 l_atr := 0;
1321 l_satr:= 0;
1322 ELSE
1323 l_atr := l_rsv_rec.primary_reservation_quantity;
1324 l_satr:= l_rsv_rec.secondary_reservation_quantity;
1325 END IF;
1326
1327 END IF;
1328 mdebug('after calling quantity tree ARI, l_atr:'||l_atr);
1329 mdebug('after calling quantity tree ARI, l_satr:'||l_satr);
1330
1331 l_rsv_rec.primary_reservation_quantity := l_atr;
1332 l_rsv_rec.secondary_reservation_quantity := l_satr;
1333
1334 mdebug(' primary_reservation_quantity :'||l_rsv_rec.primary_reservation_quantity);
1335
1336 l_return_status := FND_API.G_RET_STS_SUCCESS;
1337
1338 mdebug('Before calling: inv_reservation_pvt.create_reservation');
1339
1340 IF l_atr <> 0 THEN
1341 INV_Reservation_pvt.Create_Reservation(
1342 p_api_version_number => 1.0
1343 , p_init_msg_lst => fnd_api.g_false
1344 , x_return_status => l_return_status
1345 , x_msg_count => l_msg_count
1346 , x_msg_data => l_msg_data
1347 , p_rsv_rec => l_rsv_rec
1348 , p_serial_number => l_dummy_sn
1349 , x_serial_number => l_dummy_sn
1350 , p_partial_reservation_flag => fnd_api.g_false
1351 , p_force_reservation_flag => fnd_api.g_false
1352 , p_validation_flag => fnd_api.g_false
1353 , x_quantity_reserved => l_qty_succ_reserved
1354 , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1355 , x_reservation_id => l_cc_res_id
1356 );
1357 -- Return an error if the create reservation call failed
1358 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1359 mdebug('error in create reservation');
1360 l_cc_insert_flag := 'F';
1361 ELSE
1362 mdebug('l_qty_succ_reserved :'|| l_qty_succ_reserved);
1363 mdebug('l_cc_res_id: '|| l_cc_res_id);
1364 END IF;
1365 ELSE
1366 mdebug('l_atr is o, no need to call create reservation API');
1367 END IF;
1371 OPEN c_distinct_lpn ;
1368
1369 ELSE --allocated lpn: null
1370 -- Start 8267628
1372 LOOP --This Loop ends after create reservations for Non Lot controlled items .
1373 FETCH c_distinct_lpn INTO l_rsv_rec.lpn_id;
1374 EXIT WHEN c_distinct_lpn%NOTFOUND;
1375 -- End 8267628
1376
1377 IF (l_rsv_rec.lpn_id = -999 ) THEN --8870624
1378 l_rsv_rec.lpn_id :=NULL;
1379 END IF;
1380
1381 mdebug('calling quantity tree API with lpn_id :'||l_rsv_rec.lpn_id);
1382
1383 inv_quantity_tree_pub.clear_quantity_cache;
1384
1385 inv_quantity_tree_pub.query_quantities
1386 ( p_api_version_number => 1.0
1387 , p_init_msg_lst => fnd_api.g_false
1388 , x_return_status => l_return_status
1389 , x_msg_count => l_msg_count
1390 , x_msg_data => l_msg_data
1391 , p_organization_id => l_organization_id
1392 , p_inventory_item_id => l_item_id
1393 , p_tree_mode => INV_Quantity_Tree_PUB.g_reservation_mode
1394 , p_is_revision_control => b_is_revision_control
1395 , p_is_lot_control => b_is_lot_control
1396 , p_is_serial_control => b_is_serial_control
1397 , p_grade_code => null
1398 , p_demand_source_type_id => -9999
1399 , p_revision => l_revision
1400 , p_lot_number => null
1401 , p_subinventory_code => p_subinventory_code
1402 , p_locator_id => p_locator_id
1403 , p_lpn_id => l_rsv_rec.lpn_id --Bug#8267628
1404 , x_qoh => l_qoh
1405 , x_rqoh => l_rqoh
1406 , x_qr => l_qr
1407 , x_qs => l_qs
1408 , x_att => l_att
1409 , x_atr => l_atr
1410 , x_sqoh => l_sqoh
1411 , x_srqoh => l_srqoh
1412 , x_sqr => l_sqr
1413 , x_sqs => l_sqs
1414 , x_satt => l_satt
1415 , x_satr => l_satr
1416 );
1417 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1418 mdebug('after calling qty tree l_atr:' || l_atr||' l_att:'||l_att);
1419 mdebug('after calling qty tree l_qoh:' || l_qoh ||' l_rqoh: '||l_rqoh||' l_qr:'||l_qr||' l_qs:'||l_qs);
1420 ELSE
1421 mdebug('calling qty tree API failed ');
1422 l_atr := 0;
1423 IF l_move_order_type = 3 THEN
1424 l_atr := 0;
1425 l_satr:= 0;
1426 ELSE
1427 l_atr := l_rsv_rec.primary_reservation_quantity;
1428 l_satr:= l_rsv_rec.secondary_reservation_quantity;
1429 END IF;
1430
1431 END IF;
1432 mdebug('after calling quantity tree ARI, l_atr:'||l_atr);
1433 mdebug('after calling quantity tree ARI, l_satr:'||l_satr);
1434
1435
1436 l_rsv_rec.primary_reservation_quantity := l_atr;
1437 l_rsv_rec.secondary_reservation_quantity := l_satr;
1438
1439 mdebug(' primary_reservation_quantity :'||l_rsv_rec.primary_reservation_quantity);
1440
1441 l_return_status := FND_API.G_RET_STS_SUCCESS;
1442
1443 mdebug('Before calling: inv_reservation_pvt.create_reservation');
1444
1445 IF l_atr <> 0 THEN
1446
1447 INV_Reservation_pvt.Create_Reservation(
1448 p_api_version_number => 1.0
1449 , p_init_msg_lst => fnd_api.g_false
1450 , x_return_status => l_return_status
1451 , x_msg_count => l_msg_count
1452 , x_msg_data => l_msg_data
1453 , p_rsv_rec => l_rsv_rec
1454 , p_serial_number => l_dummy_sn
1455 , x_serial_number => l_dummy_sn
1456 , p_partial_reservation_flag => fnd_api.g_false
1457 , p_force_reservation_flag => fnd_api.g_false
1458 , p_validation_flag => fnd_api.g_false
1459 , x_quantity_reserved => l_qty_succ_reserved
1460 , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1461 , x_reservation_id => l_cc_res_id
1462 );
1463 -- Return an error if the create reservation call failed
1464 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1465 mdebug('error in create reservation');
1466 l_cc_insert_flag := 'F';
1467 ELSE
1468 mdebug('l_qty_succ_reserved :'|| l_qty_succ_reserved);
1469 mdebug('l_cc_res_id: '|| l_cc_res_id);
1470 END IF;
1471 ELSE
1472 mdebug('l_atr is o, no need to call create reservation API');
1473 END IF;
1474 END LOOP; --8267628 Looping for distinct LPNs
1475 CLOSE c_distinct_lpn;
1476 END IF; --allocated lpn
1477 END IF; --Lot control
1478 inv_quantity_tree_pub.clear_quantity_cache; --16070349 raminoch
1479
1480 --Bug3633573 following deletion not required for mtlt
1481
1482 --if l_lot_control_code = 2 then
1483 -- DELETE FROM mtl_transaction_lots_temp mtlt
1487 l_return_status := FND_API.G_RET_STS_SUCCESS;
1484 -- WHERE mtlt.transaction_temp_id = l_mmtt_id;
1485 --end if;
1486
1488
1489 SELECT quantity_detailed
1490 , secondary_quantity_detailed
1491 , line_status
1492 , NVL(quantity_delivered,0)
1493 , NVL(secondary_quantity_delivered,0)
1494 INTO l_old_quantity_detailed
1495 , l_old_sec_qty_detailed
1496 , l_line_status
1497 , l_quantity_delivered
1498 , l_sec_qty_delivered
1499 FROM mtl_txn_request_lines
1500 WHERE line_id = l_line_num;
1501
1502 mdebug('l_quantity_delivered:'||l_quantity_delivered);
1503 mdebug('l_sec_qty_delivered:'||l_sec_qty_delivered);
1504 mdebug('l_line_status:'||l_line_status);
1505 mdebug('l_quantity_detailed:'||l_old_quantity_detailed);
1506
1507 SELECT COUNT(*)
1508 INTO l_old_mmtt_cnt
1509 FROM mtl_material_transactions_temp
1510 WHERE move_order_line_id = l_line_num;
1511
1512 --mdebug('before update mol, the quantity_detailed :' || l_old_quantity_detailed);
1513 mdebug('before update mol, the number of mmtt rows :' || l_old_mmtt_cnt);
1514
1515 --update quantity_detailed at mol to refelect change at mmtt.
1516 --SAVEPOINT before_allocation;
1517
1518 mdebug('update move order line before calling allocation APIs');
1519
1520 IF l_line_num IS NOT NULL THEN
1521
1522 /*The below logic does the following:
1523 If MTRL UOM = MMTT txn UOM then
1524 MTRL new qty = MTRL old qty - MMTT txn qty
1525 If its dual UOM Item then MTRL new sec qty = MTRL old sec qty - MMTT sec qty
1526 If any of these lead to qty less than 0 then compute using standard conversion.
1527 Else
1528 for Primary based fulfillment AND If MTRL UOM is primary UOM then
1529 MTRL new qty = MTRL old qty - MMTT primary qty
1530 MTRL new sec qty = MTRL old sec qty - MMTT secondary qty, If it goes below 0 then derive MTRL new qty using conversion
1531 for Secondary based fulfillment AND If MTRL UOM is secondary UOM then
1532 MTRL new qty = MTRL old qty - MMTT secondary qty
1533 MTRL new sec qty = MTRL old sec qty - MMTT sec qty
1534 for third/diff uom on MTRL then
1535 If fulfillment base is 'S' then
1536 MTRL new sec qty = MTRL old sec qty - MMTT sec qty
1537 MTRL new qty = MTRL old qty - (MMTT sec qty converted to txn UOM)
1538 If MTRL new qty goes -ve or 0 then recalculate using UOM convert from calculated MTRL new sec qty
1539 else
1540 MTRL new qty = MTRL old qty - (MMTT txn qty from txn UOM to MTRL UOM)
1541 For dual UOM item calculate the corresponding MTRL new sec qty
1542 */
1543 mdebug('l_trolin_rec.uom_code '||l_trolin_rec.uom_code);
1544 mdebug('l_mmtt_transaction_uom '||l_mmtt_transaction_uom);
1545 mdebug('l_sec_uom_code '||l_sec_uom_code);
1546 mdebug('l_secondary_quantity '||l_secondary_quantity);
1547
1548 IF (l_trolin_rec.uom_code = l_mmtt_transaction_uom) THEN--{
1549 l_mol_delta_qty := l_transaction_qty;
1550 l_new_quantity_detailed := l_old_quantity_detailed - l_mol_delta_qty;
1551 mdebug('MOL UoM is same as MMTT Txn UoM so set MTRL delta qty as MMTT txn qty');
1552 mdebug('The new qty detailed for MTRL will be old qty detailed - MMTT txn qty');
1553 mdebug('l_mol_delta_qty '||l_mol_delta_qty);
1554 mdebug('l_new_quantity_detailed '||l_new_quantity_detailed);
1555
1556 IF (l_sec_uom_code IS NOT NULL) THEN --{dual uom item
1557 l_new_sec_qty_detailed := l_old_sec_qty_detailed - l_secondary_quantity;
1558 mdebug('l_new_sec_qty_detailed '||l_new_sec_qty_detailed);
1559 mdebug('l_old_sec_qty_detailed '||l_old_sec_qty_detailed);
1560 mdebug('The new qty detailed for MTRL will be old qty detailed - MMTT txn qty');
1561 IF(l_new_sec_qty_detailed <= 0 AND NVL(l_fulfillment_base, 'P') = 'P') THEN --Can happen for items with deviations
1562 l_new_sec_qty_detailed := INV_Convert.inv_um_convert
1563 (item_id => l_item_id,
1564 precision => null,
1565 from_quantity => l_new_quantity_detailed,
1566 from_unit => l_trolin_rec.uom_code,
1567 to_unit => l_sec_uom_code,
1568 from_name => null,
1569 to_name => null);
1570 ELSIF(l_new_quantity_detailed <= 0 AND NVL(l_fulfillment_base, 'P') = 'S') THEN --Can happen for items with deviations
1571 l_new_quantity_detailed := INV_Convert.inv_um_convert
1572 (item_id => l_item_id,
1573 precision => null,
1574 from_quantity => l_new_sec_qty_detailed,
1575 from_unit => l_trolin_rec.uom_code,
1576 to_unit => l_primary_uom,
1577 from_name => null,
1578 to_name => null);
1579 ELSE
1580 mdebug('MMTT txn UOM is same as MTRL UOM and quantitues were computed without use of UOM convert');
1581 END IF;
1582 END IF; --}
1583
1584 ELSE
1585 IF(l_trolin_rec.uom_code = l_primary_uom AND NVL(l_fulfillment_base, 'P') = 'P') THEN--{
1589 mdebug('MOL UoM is same as MMTT Primary UoM so set MTRL delta qty as MMTT primary qty');
1586 l_mol_delta_qty := l_primary_qty;
1587 l_new_quantity_detailed := l_old_quantity_detailed - l_mol_delta_qty;
1588 mdebug('Fulfillment base is P');
1590 mdebug('The new qty detailed for MTRL will be old qty detailed - MMTT pri qty');
1591
1592 IF (l_sec_uom_code IS NOT NULL) THEN --{dual uom item
1593 l_new_sec_qty_detailed := l_old_sec_qty_detailed - l_secondary_quantity;
1594 IF(l_new_sec_qty_detailed <= 0) THEN --Can happen for items with deviations
1595 l_new_sec_qty_detailed := INV_Convert.inv_um_convert
1596 (item_id => l_item_id,
1597 precision => null,
1598 from_quantity => l_new_quantity_detailed,
1599 from_unit => l_primary_uom,
1600 to_unit => l_sec_uom_code,
1601 from_name => null,
1602 to_name => null);
1603 END IF;
1604 END IF; --}
1605
1606 ELSIF(l_trolin_rec.uom_code = l_sec_uom_code AND NVL(l_fulfillment_base, 'P') = 'S') THEN
1607
1608 mdebug('MOL UoM is same as MMTT secondary UoM and fulfillment base is S so set MTRL delta qty as MMTT sec qty');
1609 mdebug('MOL UoM is same as MMTT secondary UoM and fulfillment base is S MTRL new qty and new sec qty = orginal MTRL qty - MMTT sec qty');
1610 l_mol_delta_qty := l_secondary_quantity;
1611 l_new_quantity_detailed := l_old_quantity_detailed - l_mol_delta_qty;
1612 l_new_sec_qty_detailed := l_old_sec_qty_detailed - l_mol_delta_qty;
1613
1614 ELSE
1615 IF (NVL(l_fulfillment_base, 'P') = 'S') THEN--{
1616 l_new_sec_qty_detailed := l_old_sec_qty_detailed - l_secondary_quantity;
1617
1618 l_new_quantity_detailed := l_old_quantity_detailed - INV_Convert.inv_um_convert
1619 (item_id => l_item_id,
1620 precision => null,
1621 from_quantity => l_secondary_quantity,
1622 from_unit => l_sec_uom_code,
1623 to_unit => l_trolin_rec.uom_code,
1624 from_name => null,
1625 to_name => null);
1626
1627 IF(l_new_quantity_detailed <= 0) THEN
1628 l_new_quantity_detailed := INV_Convert.inv_um_convert
1629 (item_id => l_item_id,
1630 precision => null,
1631 from_quantity => l_new_sec_qty_detailed,
1632 from_unit => l_sec_uom_code,
1633 to_unit => l_trolin_rec.uom_code,
1634 from_name => null,
1635 to_name => null);
1636 END IF;
1637
1638 ELSE
1639 l_mol_delta_qty := INV_Convert.inv_um_convert
1640 (item_id => l_item_id,
1641 precision => null,
1642 from_quantity => l_transaction_qty,
1643 from_unit => l_mmtt_transaction_uom,
1644 to_unit => l_trolin_rec.uom_code,
1645 from_name => null,
1646 to_name => null);
1647
1648 l_new_quantity_detailed := l_old_quantity_detailed - l_mol_delta_qty;
1649
1650 IF (l_sec_uom_code IS NOT NULL) THEN --{dual uom item
1651 l_new_sec_qty_detailed := l_old_sec_qty_detailed - l_secondary_quantity;
1652 IF(l_new_sec_qty_detailed <= 0) THEN--{
1653 l_new_sec_qty_detailed := INV_Convert.inv_um_convert
1654 (item_id => l_item_id,
1655 precision => null,
1656 from_quantity => l_new_quantity_detailed,
1657 from_unit => l_trolin_rec.uom_code,
1658 to_unit => l_sec_uom_code,
1659 from_name => null,
1660 to_name => null);
1661 END IF;--}
1662 END IF;--}
1663 END IF;--}
1664 END IF;--}
1665 END IF;--}
1666 mdebug('l_mol_delta_qty = ' || l_mol_delta_qty);
1667 mdebug('l_new_quantity_detailed = ' || l_new_quantity_detailed);
1668 mdebug('l_new_sec_qty_detailed = ' || l_new_sec_qty_detailed);
1669
1670 /* BUG3278170 when the move order line has multiple tasks and
1671 one of the taks is already delivered and the short pick is done on
1672 the second task then pick release pub would not consider the delivered qty
1673 and the pick release would behave erratically. Hence modifying the
1674 quantity, quantity_delivered, quantity_detailed as if the delivered task
1675 is not there on the MTRL. This is just hack on pick_release_pub as
1676 pick release being public api and is not designed to allocate a move order line with
1677 partial delivered qty on it. */
1678 -- Bug 3278170 fix is below patchset 'J' level
1679
1680 IF (inv_control.g_current_release_level >= inv_release.g_j_release_level) THEN
1681 -- For patchset 'J'
1682 mdebug('In J patchset update move order line');
1683 -- bug 8197499 starts
1684 IF (l_sec_uom_code IS NOT NULL) THEN
1685 mdebug('l_sec_uom_code IS NOT NULL ' );
1686 UPDATE mtl_txn_request_lines
1687 SET quantity_detailed = l_new_quantity_detailed
1688 , secondary_quantity_detailed = l_new_sec_qty_detailed
1689 , last_update_date = SYSDATE
1690 , last_updated_by = l_user_id
1691 WHERE organization_id = l_organization_id
1692 AND line_id = l_line_num;
1693 ELSE --bug 8197499 ends
1694 UPDATE mtl_txn_request_lines
1695 SET quantity_detailed = quantity_detailed - l_mol_delta_qty
1696 , last_update_date = SYSDATE
1697 , last_updated_by = l_user_id
1698 WHERE organization_id = l_organization_id
1699 AND line_id = l_line_num;
1700 END IF;
1701 ELSE
1702 -- 11.5.9 or lower, so no secondary qty update required
1703 -- fix for bug 3278170
1704 UPDATE mtl_txn_request_lines
1705 SET quantity_detailed = (NVL(quantity_detailed,0) - NVL(l_quantity_delivered,0)) - l_mol_delta_qty --bug3278170
1706 , last_update_date = SYSDATE
1707 , last_updated_by = l_user_id
1708 , quantity_delivered = 0 --bug3278170
1709 , quantity = quantity - NVL(l_quantity_delivered,0) --bug3278170
1710 WHERE organization_id = l_organization_id
1711 AND line_id = l_line_num;
1712 END IF;
1713
1714 IF (l_reservation_id IS NOT NULL) THEN
1715 IF l_cc_transfer_flag = 'F' THEN
1716 mdebug(' cycle count reservation transfer failed');
1717 UPDATE mtl_reservations
1718 SET detailed_quantity = detailed_quantity - l_lot_qty
1719 , secondary_detailed_quantity = secondary_detailed_quantity - l_sec_lot_qty --bug 8197499
1720 , last_update_date = SYSDATE
1721 , last_updated_by = l_user_id
1722 WHERE organization_id = l_organization_id
1723 AND reservation_id = l_reservation_id;
1724 ELSE
1725 mdebug(' cycle count reservation transfer successed');
1726 BEGIN --Bug 3633573 added exception block to continue flow if no
1727 --data is found out of select clause
1728 SELECT primary_reservation_quantity
1729 ,detailed_quantity
1730 INTO l_primary_reservation_quantity
1731 ,l_detailed_quantity
1732 FROM mtl_reservations
1733 WHERE organization_id = l_organization_id
1734 AND reservation_id = l_reservation_id;
1735 EXCEPTION
1736 WHEN NO_DATA_FOUND THEN
1737 mdebug('No data found in mtl_reservation ');
1738 mdebug('Reservation id :'||l_reservation_id);
1739 WHEN OTHERS THEN
1740 mdebug('In Others in reservations');
1741 IF (l_debug = 1) THEN
1742 mdebug('Log Exception2');
1743 END IF;
1744 END;
1745
1746 END IF;
1747 mdebug('after update mol, the detailed_quantity at reservation :'|| l_detailed_quantity);
1748 mdebug('after update mol, the primary_quantity at reservation :' || l_primary_reservation_quantity);
1749 ELSE
1750 mdebug('l_reservation_id is null ');
1751 END IF;
1752
1753 select quantity_detailed,
1754 to_account_id --BUG#3048061
1755 into l_new_quantity_detailed, -- added for bug 9895550
1756 l_to_account_id --BUG#3048061
1757 from mtl_txn_request_lines
1758 where line_id = l_line_num;
1759
1760 mdebug('after update mol, the quantity_detailed :' || l_new_quantity_detailed); -- added for bug 9895550
1761
1762 end if;
1763
1764
1765 SELECT mtl_material_transactions_s.nextval
1766 INTO v_header_id
1767 FROM dual;
1768
1769 mdebug('v_header_id: '|| v_header_id);
1770
1771 mdebug('Before calling: INV_Replenish_Detail_PUB.Line_Details_PUB ');
1772
1773 l_detailed_qty := 0;
1774
1775 INV_PICK_RELEASE_PUB.g_pick_release_caller := 'WMS-PICK-EXCEPTION'; --9556463
1776
1777 inv_wip_picking_pvt.g_pick_release_caller := 'WMS-PICK-EXCEPTION'; -- added for bug 9895550
1778 inv_wip_picking_pvt.g_old_detailed_qty := l_old_quantity_detailed; -- added for bug 9895550
1779
1780 INV_Replenish_Detail_PUB.Line_Details_PUB
1781 (
1782 p_line_id => l_line_num
1783 , x_number_of_rows => l_num_of_rows
1784 , x_detailed_qty => l_detailed_qty
1785 , x_detailed_qty2 => l_sec_detailed_qty -- 16070349 raminoch
1786 , x_return_status => l_return_status
1787 , x_msg_count => l_msg_count
1788 , x_msg_data => l_msg_data
1789 , x_revision => l_rev
1790 , x_locator_id => l_from_loc_id
1791 , x_transfer_to_location => l_to_loc_id
1792 , x_lot_number => l_lot_number
1793 , x_expiration_date => l_expiration_date
1794 , x_transaction_temp_id => v_transaction_temp_id
1795 , p_transaction_header_id => v_header_id
1796 , p_transaction_mode => NULL
1797 , p_move_order_type => l_move_order_type
1798 , p_serial_flag => l_serial_flag
1799 );
1800
1801 inv_wip_picking_pvt.g_pick_release_caller := NULL; -- added for bug 9895550
1802 inv_wip_picking_pvt.g_old_detailed_qty := -999; -- added for bug 9895550
1803
1804 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1805 mdebug(' inv_replenish_detail_pub.line_details_pub failed');
1806 --ROLLBACK TO SAVEPOINT before_allocation;
1807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1808 END IF;
1809
1810 --mdebug('After calling: INV_Replenish_Detail_PUB.Line_Details_PUB ');
1811
1812 INV_PICK_RELEASE_PUB.g_pick_release_caller := NULL; --9556463
1813
1814 mdebug('After calling: INV_Replenish_Detail_PUB.Line_Details_PUB: l_detailed_qty:' || l_detailed_qty);
1815 mdebug('After calling pick release: number_of_rows:'|| l_num_of_rows);
1816 mdebug(' v_transaction_temp_id : '|| v_transaction_temp_id);
1817 mdebug(' l_sec_detailed_qty : '|| l_sec_detailed_qty);
1818
1819 if (l_move_order_type <> 3 ) then
1820 -- Bug 8312574
1821 IF ( l_trolin_rec.secondary_uom IS NOT NULL AND l_sec_detailed_qty <=0) THEN --16070349 raminoch
1822 l_sec_detailed_qty := inv_convert.inv_um_convert
1823 ( item_id => l_trolin_rec.inventory_item_id,
1824 precision => null,
1825 from_quantity => l_detailed_qty,
1826 from_unit => l_trolin_rec.uom_code,
1827 to_unit => l_trolin_rec.secondary_uom,
1828 from_name => null,
1829 to_name => null);
1830 END IF;
1831
1832 UPDATE mtl_txn_request_lines
1833 SET quantity_detailed = l_detailed_qty + l_quantity_delivered
1834 , secondary_quantity_detailed = DECODE(secondary_uom_code,NULL,NULL,
1835 l_sec_detailed_qty + l_sec_qty_delivered) -- Bug 8312574
1836 WHERE line_id = l_line_num
1837 AND organization_id = l_organization_id;
1838
1839 -- Bug#3048061
1840 -- Update the distribution_account_id of MMTT
1841 -- from to_account_id of mtl_txn_request_lines
1842 -- Since, MOs allocated using MO Pick Slip Report too,
1843 -- along with manually allocated MO will populate
1844 -- the distribution_account_id of MMTT.
1845
1846 IF l_to_account_id is not null THEN
1847 UPDATE mtl_material_transactions_temp
1848 SET distribution_account_id = l_to_account_id
1849 WHERE move_order_line_id = l_line_num;
1850 END IF;
1851
1852 end if;
1853 /* BUG3278170 values quantity, quantity_delivered, quantity_detailed
1854 set on MTRL before calling pick relase api are reset.*/
1855 -- this fix is for below J patchset, no sec UOM update required
1856 IF (inv_control.g_current_release_level < inv_release.g_j_release_level)
1857 THEN
1858 SELECT sum(transaction_quantity)
1859 INTO l_new_mmtt_qty
1860 FROM mtl_material_transactions_temp
1861 WHERE move_order_line_id = l_line_num;
1862
1863 UPDATE mtl_txn_request_lines
1864 SET quantity = nvl(l_new_mmtt_qty,0) + l_quantity_delivered ,
1865 quantity_detailed = nvl(l_new_mmtt_qty,0) + l_quantity_delivered ,
1866 quantity_delivered = l_quantity_delivered
1867 WHERE organization_id = l_organization_id
1868 AND line_id = l_line_num;
1869 --bug3278170
1870 END IF;
1871
1872 fnd_msg_pub.count_and_get
1873 ( p_count => l_msg_count
1874 , p_data => l_msg_data
1875 );
1876
1877 IF (l_msg_count = 0) THEN
1878 mdebug('Successful');
1879 ELSIF (l_msg_count = 1) THEN
1880 mdebug('Not Successful');
1881 mdebug(replace(l_msg_data,chr(0),' '));
1882 ELSE
1883 mdebug('Not Successful2');
1884 For I in 1..l_msg_count LOOP
1885 l_msg_data := fnd_msg_pub.get(I,'F');
1886 mdebug(replace(l_msg_data,chr(0),' '));
1887 END LOOP;
1888 END IF;
1889
1890
1891 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1892 FND_MSG_PUB.Add_Exc_Msg
1893 ( 'Suggest Alt Loc'
1894 , 'Call Pick Release'
1895 );
1896 RAISE FND_API.G_EXC_ERROR;
1897 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1898 FND_MSG_PUB.Add_Exc_Msg
1899 ( 'Suggest Alt Loc'
1900 , 'Call Pick Release'
1901 );
1902 RAISE FND_API.G_EXC_ERROR;
1903 END IF;
1904
1905 mdebug('before exception section');
1906
1907 EXCEPTION
1908
1909 WHEN FND_API.G_EXC_ERROR THEN
1910
1911 Raise FND_API.G_EXC_ERROR;
1912
1913 WHEN OTHERS THEN
1914 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1915 THEN
1916 FND_MSG_PUB.Add_Exc_Msg
1917 ( 'INV_Move_Order_PUB'
1918 , 'Create_Move_Orders'
1919 );
1920 END IF;
1921 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1922
1923 END Suggest_alternate_location ;
1924
1925
1926
1927
1928
1929 PROCEDURE Log_exception
1930 (
1931 p_api_version_number IN NUMBER
1932 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1936 , x_msg_data OUT NOCOPY VARCHAR2
1933 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1934 , x_return_status OUT NOCOPY VARCHAR2
1935 , x_msg_count OUT NOCOPY NUMBER
1937 , p_organization_id IN NUMBER
1938 , p_mmtt_id IN NUMBER
1939 , p_task_id IN NUMBER
1940 , p_reason_id IN NUMBER
1941 , p_subinventory_code IN VARCHAR2
1942 , p_locator_id IN NUMBER
1943 , p_discrepancy_type IN NUMBER
1944 , p_user_id IN VARCHAR2
1945 , p_item_id IN NUMBER:=NULL
1946 , p_revision IN VARCHAR2:=NULL
1947 , p_lot_number IN VARCHAR2:=NULL
1948 , p_lpn_id IN NUMBER:=NULL
1949 , p_is_loc_desc IN BOOLEAN := FALSE --Added bug 3989684
1950 )IS
1951
1952 l_sequence NUMBER;
1953 l_return_err VARCHAR2(230);
1954 l_txn_typ_id number; -- 11858770
1955 l_txn_action_id number; -- 11858770
1956 l_txn_src_type_id number; -- 11858770
1957 l_sub_code VARCHAR2(50); -- 11858770
1958 l_loc_id number; -- 11858770
1959 l_insert_sub VARCHAR2(50); -- 11858770
1960 l_insert_loc number; -- 11858770
1961
1962 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1963 l_proc_name VARCHAR2(30) := 'Log_exception';
1964 BEGIN
1965
1966 --Calculate Sequence Number
1967 select wms_exceptions_s.NEXTVAL INTO l_sequence from dual;
1968 g_debug := l_debug;
1969 g_module_name := l_proc_name;
1970 IF (l_debug = 1) THEN
1971 mdebug('Inserting into exceptions');
1972 mdebug(l_sequence);
1973 END IF;
1974 -- adding code for 11858770
1975 begin
1976
1977 select transaction_type_id, transaction_action_id, transaction_source_type_id,
1978 subinventory_code, locator_id
1979 into l_txn_typ_id, l_txn_action_id, l_txn_src_type_id,
1980 l_sub_code, l_loc_id
1981 from mtl_material_transactions_temp
1982 where transaction_temp_id = p_mmtt_id;
1983
1984 mdebug('l_txn_typ_id = '||l_txn_typ_id);
1985 mdebug('l_txn_action_id = '||l_txn_action_id);
1986 mdebug('l_txn_src_type_id = '||l_txn_src_type_id);
1987 mdebug('l_sub_code = '||l_sub_code);
1988 mdebug('l_loc_id = '||l_loc_id);
1989
1990 exception
1991 when others then
1992 if (l_debug = 1) THEN
1993 mdebug('Exception while fetching the data from parent MMTT');
1994 end if;
1995 end;
1996
1997 if ((l_txn_typ_id = 18 and l_txn_action_id = 27 and l_txn_src_type_id = 1)
1998 or (l_txn_typ_id = 15 and l_txn_action_id = 27 and l_txn_src_type_id = 12)
1999 or (l_txn_typ_id = 61 and l_txn_action_id = 12 and l_txn_src_type_id = 7) ) then
2000 -- only for po, asn, interorg receipts.
2001 mdebug('In if l_txn_typ_id in (18,15,61) ');
2002 l_insert_sub := l_sub_code;
2003 l_insert_loc := l_loc_id;
2004 else
2005 mdebug('In else l_txn_typ_id in (18,15,61) ');
2006 l_insert_sub := p_subinventory_code;
2007 l_insert_loc := p_locator_id;
2008 end if;
2009 -- end adding code for 11858770
2010
2011 INSERT INTO wms_exceptions(
2012 TASK_ID,
2013 SEQUENCE_NUMBER,
2014 ORGANIZATION_ID,
2015 INVENTORY_ITEM_ID,
2016 PERSON_ID,
2017 EFFECTIVE_START_DATE,
2018 EFFECTIVE_END_DATE ,
2019 INVENTORY_LOCATION_ID,
2020 REASON_ID,
2021 DISCREPANCY_TYPE,
2022 SUBINVENTORY_CODE,
2023 LOT_NUMBER,
2024 REVISION,
2025 LAST_UPDATE_DATE,
2026 LAST_UPDATED_BY,
2027 CREATION_DATE,
2028 created_by,
2029 transaction_header_id,
2030 lpn_id
2031 )
2032 VALUES(p_mmtt_id,
2033 l_sequence,
2034 p_organization_id,
2035 p_item_id,
2036 p_user_id,
2037 Sysdate,
2038 Sysdate,
2039 l_insert_loc, -- 11858770
2040 p_reason_id,
2041 p_discrepancy_type,
2042 l_insert_sub, -- 11858770
2043 p_lot_number,
2044 p_revision,
2045 Sysdate,
2046 FND_GLOBAL.user_id,--p_user_id,Bug:2672785
2047 Sysdate,
2048 FND_GLOBAL.user_id,--p_user_id,Bug:2672785
2049 p_mmtt_id,
2050 p_lpn_id);
2051
2052 --Bug #4058417 - Removed inline branching so that reason_id is
2053 --updated in MMTT for R12 as well
2054 IF (p_is_loc_desc) THEN --Added for bug 3989684
2055 IF (l_debug = 1) THEN
2056 mdebug('p_is_loc_desc is True, updating MMTT header'||p_mmtt_id||' with reason id '||p_reason_id);
2057 END IF;
2058
2059 UPDATE mtl_material_transactions_temp
2060 SET reason_id = p_reason_id
2061 WHERE transaction_header_id = p_mmtt_id;
2062 END IF;
2063
2064 x_return_status := FND_API.G_RET_STS_SUCCESS;
2065
2066 exception
2067 when others THEN
2068 x_return_status:=FND_API.G_RET_STS_ERROR;
2069
2070 l_return_err := 'Insert into WMS_Exceptions failed'||
2071 substrb(sqlerrm,1,55);
2072 raise_application_error(-20000,l_return_err);
2073
2074
2075 IF (l_debug = 1) THEN
2076 mdebug('Log Exception');
2077 END IF;
2078
2079 END log_exception;
2080
2081
2082 /* Will be called for
2083 1. PICK NONE exception - from PickLoad page directly
2084 2. CURTAIL PICK - confirm qty < requested_qty
2085 -- cleanup task will be called for each temp_id with this case..usually only one
2086 EXCEPT in case of BULK, there will be multiple MMTTs selected for the given temp_id
2087 -- it should be called only for qty exceptions where picked quantity < suggested quantity
2088 -- and not for overpicked qty
2089 3. CURTAIL PICK for all children of BULK- */
2090
2091 PROCEDURE cleanup_task(
2092 p_temp_id IN NUMBER
2093 , p_qty_rsn_id IN NUMBER
2094 , p_user_id IN NUMBER
2095 , p_employee_id IN NUMBER
2096 , x_return_status OUT NOCOPY VARCHAR2
2097 , x_msg_count OUT NOCOPY NUMBER
2098 , x_msg_data OUT NOCOPY VARCHAR2)
2099 IS
2100 l_mmtt_msg_cnt NUMBER;
2101 l_mmtt_msg_data VARCHAR2(2000);
2102 l_mmtt_return_status VARCHAR2(1);
2103
2104
2105 l_mmtt_temp_id NUMBER;
2106 l_item_id NUMBER;
2107 l_org_id NUMBER;
2108 l_sub VARCHAR2(30);
2109 l_loc NUMBER;
2110 /* Bug 7504490*/
2111 l_allocated_lpn_id NUMBER ;
2112 l_revision VARCHAR2(3);
2113
2114 l_proc_name VARCHAR2(60) := 'cleanup_task (wrapper)';
2115 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2116
2117
2118 CURSOR rem_mmtt_csr IS
2119 SELECT mmtt.transaction_temp_id
2120 FROM mtl_material_transactions_temp mmtt
2121 WHERE mmtt.organization_id = l_org_id
2122 AND mmtt.inventory_item_id = l_item_id
2123 AND mmtt.subinventory_code = l_sub
2124 AND mmtt.locator_id = l_loc
2125 AND NVL(mmtt.revision, '@') = NVL(l_revision, '@') --Bug 7504490
2126 AND NVL(mmtt.allocated_lpn_id, -1)= NVL(l_allocated_lpn_id, -1) --Bug 7504490
2127 AND mmtt.transaction_temp_id <> p_temp_id
2128 AND mmtt.parent_line_id IS NULL -- Bug# 5760606 - add condition so only non bulk tasks are considered
2129 -- without the condition curtail pick for bulk pick will fail since this cursor picks up child mmtt lines
2130 AND mmtt.item_lot_control_code = 1
2131 AND NOT EXISTS( /*Bug8304954-If no serial allocation, we should cleanup(only for non-lot case) */
2132 SELECT 1 FROM MTL_SERIAL_NUMBERS_TEMP MSNT
2133 WHERE MSNT.TRANSACTION_TEMP_ID= mmtt.transaction_temp_id --Should not have serials allocated.
2134 )
2135 AND NOT EXISTS (
2136 SELECT 1 FROM wms_dispatched_tasks
2137 WHERE transaction_temp_id= mmtt.transaction_temp_id
2138 AND status in (4,9)) ;
2139
2140 BEGIN
2141
2142 IF (l_debug = 1) THEN
2143 mdebug('IN : ' || l_proc_name);
2144 -- Start Bug# 5760606 - added more debug info
2145 mdebug('cleanup_task (w): p_temp_id:' || p_temp_id);
2146 mdebug('cleanup_task (w): p_qty_rsn_id: ' || p_qty_rsn_id);
2147 mdebug('cleanup_task (w): p_user_id: ' || p_user_id);
2148 mdebug('cleanup_task (w): p_employee_id: ' || p_employee_id);
2149 -- End Bug# 5760606
2150 END IF;
2151
2152 -- get the sub and loc where we picked the material from
2153 /* Bug 7504490 - Adding revision and allocated_lpn_id to the query */
2154 select organization_id,inventory_item_id,subinventory_code,locator_id,revision,allocated_lpn_id
2155 into l_org_id,l_item_id,l_sub,l_loc,l_revision,l_allocated_lpn_id
2156 from mtl_material_transactions_temp
2157 where transaction_temp_id = p_temp_id;
2158
2159 IF (l_debug = 1) THEN
2160 mdebug('cleanup_task (w) : Calling for the other mmtts');
2161 END IF;
2162
2163 OPEN rem_mmtt_csr;
2164
2165 IF (l_debug = 1) THEN
2166 mdebug('cleanup_task (w) :Values of p_act_sub:' || l_sub);
2167 mdebug('cleanup_task (w) :Values of p_act_loc:' || l_loc);
2168 END IF;
2169
2170 LOOP
2171
2172 FETCH rem_mmtt_csr INTO l_mmtt_temp_id ;
2173 EXIT WHEN rem_mmtt_csr%NOTFOUND ;
2174
2175 IF (l_debug = 1) THEN
2176 mdebug('cleanup_task (w) :Calling cleanup task API with');
2180 END IF;
2177 mdebug('cleanup_task (w) :TEMPID: ' || l_mmtt_temp_id);
2178 mdebug('cleanup_task (w) :UserId: ' || p_user_id);
2179 mdebug('cleanup_task (w) :p_qty_disc_rsn : ' || p_qty_rsn_id);
2181
2182 cleanup_task(
2183 p_temp_id =>l_mmtt_temp_id
2184 , p_qty_rsn_id =>p_qty_rsn_id
2185 , p_user_id =>p_user_id
2186 , p_employee_id =>p_employee_id
2187 , p_envoke_workflow => 'N'
2188 , x_return_status =>x_return_status
2189 , x_msg_count =>x_msg_count
2190 , x_msg_data =>x_msg_data
2191 , p_orig_temp_id =>p_temp_id); --Bug12812580
2192
2193 IF (l_debug = 1) THEN
2194 mdebug('after calling cleanup_task for transaction:'||l_mmtt_temp_id);
2195 END IF;
2196
2197 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2198
2199 IF l_debug = 1 THEN
2200 mdebug('cleanup_task (W) :Error occurred while calling cleanup_task');
2201 END IF ;
2202 RAISE fnd_api.g_exc_error;
2203
2204 END IF;
2205
2206 END LOOP;
2207
2208 CLOSE rem_mmtt_csr; --Closing the cursor
2209
2210
2211
2212
2213 -- call for the current line
2214 -- Bug# 5760606 - added more debug info
2215 mdebug('cleanup_task (w): before calling cleanup_task for the current line');
2216
2217 cleanup_task(
2218 p_temp_id =>p_temp_id
2219 , p_qty_rsn_id =>p_qty_rsn_id
2220 , p_user_id =>p_user_id
2221 , p_employee_id =>p_employee_id
2222 , p_envoke_workflow => 'Y'
2223 , x_return_status =>x_return_status
2224 , x_msg_count =>x_msg_count
2225 , x_msg_data =>x_msg_data );
2226 mdebug('END : ' || l_proc_name );
2227 EXCEPTION
2228 WHEN fnd_api.g_exc_error THEN
2229 x_return_status := l_g_ret_sts_error;
2230 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2231 mdebug('ROLLBACK ' );
2232 ROLLBACK ;
2233 mdebug('RAISE fnd_api.g_exc_error: ' || SQLERRM);
2234 WHEN OTHERS THEN
2235 x_return_status := l_g_ret_sts_unexp_error;
2236 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2237 mdebug('ROLLBACK ' );
2238 ROLLBACK ;
2239 mdebug('RAISE fnd_api.g_exc_error: ' || SQLERRM);
2240
2241
2242 END;
2243
2244 PROCEDURE cleanup_task(
2245 p_temp_id IN NUMBER
2246 , p_qty_rsn_id IN NUMBER
2247 , p_user_id IN NUMBER
2248 , p_employee_id IN NUMBER
2249 , p_envoke_workflow IN VARCHAR2
2250 , x_return_status OUT NOCOPY VARCHAR2
2251 , x_msg_count OUT NOCOPY NUMBER
2252 , x_msg_data OUT NOCOPY VARCHAR2
2253 , p_orig_temp_id IN NUMBER DEFAULT NULL) --Bug12812580
2254 IS
2255 l_txn_hdr_id NUMBER;
2256 l_txn_temp_id NUMBER;
2257 l_org_id NUMBER;
2258 l_item_id NUMBER;
2259 l_sub VARCHAR2(10);
2260 l_loc NUMBER;
2261 l_lot VARCHAR2(80);
2262 l_rev VARCHAR2(3);
2263 l_txn_qty NUMBER;
2264 l_sec_txn_qty NUMBER;
2265 l_other_mmtt_count NUMBER;
2266 l_mo_line_id NUMBER;
2267 l_mo_type NUMBER;
2268 l_mol_qty NUMBER;
2269 l_mol_qty_delivered NUMBER;
2270 l_mol_src_id NUMBER;
2271 l_mol_src_line_id NUMBER;
2272 l_mol_reference_id NUMBER;
2273 l_mol_pri_qty NUMBER;
2274 l_mol_sec_qty NUMBER;
2275 l_mol_sec_uom VARCHAR2(3);
2276 l_mtrl_uom VARCHAR2(3);
2277 l_primary_quantity NUMBER;
2278 l_mmtt_transaction_uom VARCHAR2(3);
2279
2280 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2281 l_proc_name VARCHAR2(30) := 'CLEANUP_TASK';
2282 l_progress VARCHAR2(30) := '100';
2283 l_wf NUMBER := -1;
2284 l_calling_program VARCHAR2(30) := 'CLEANUP_TASK: QTY EXCEPTION';
2285 l_update_parent BOOLEAN := FALSE ; -- No need to call update_parent_mmtt in INV_TRX_UTIL_PUB
2286 l_parent_line_id NUMBER; --For checking bulk task
2287 l_kill_mo_profile NUMBER := NVL(FND_PROFILE.VALUE_WNPS('INV_KILL_MOVE_ORDER'),2);
2288 l_return_status VARCHAR2(1);
2289
2290 --Bug#6027401.
2291 l_reservation_id NUMBER;
2292 l_pri_rsv_qty NUMBER;
2293 l_sec_rsv_qty NUMBER;
2294 l_rsv_qty NUMBER;
2295 l_pri_rsv_uom VARCHAR2(3);
2296 l_sec_rsv_uom VARCHAR2(3);
2297 l_rsv_uom VARCHAR2(3);
2298 l_rsv_lot_num VARCHAR2(80); -- Added for Bug 8542094
2299 l_rsv_org_id NUMBER; -- Added for Bug 8542094
2300 l_old_upd_resv_rec inv_reservation_global.mtl_reservation_rec_type;
2301 l_new_upd_resv_rec inv_reservation_global.mtl_reservation_rec_type;
2302 l_upd_dummy_sn inv_reservation_global.serial_number_tbl_type;
2303 --Bug#6027401.
2304
2305 l_primary_uom VARCHAR2(3);
2306 l_secondary_uom VARCHAR2(3);
2307 l_fulfillment_base VARCHAR2(1) := 'P';
2308
2309 CURSOR c_mmtt_info IS
2310 SELECT mmtt.transaction_header_id
2311 , mmtt.transaction_temp_id
2312 , mmtt.parent_line_id --For checking bulk task
2313 , mmtt.inventory_item_id
2314 , mmtt.organization_id
2315 , mmtt.revision
2316 , mmtt.lot_number
2317 , mmtt.subinventory_code
2318 , mmtt.locator_id
2319 , mmtt.move_order_line_id
2320 , mmtt.transaction_quantity
2321 , mmtt.transaction_uom
2322 , mmtt.primary_quantity
2323 , mmtt.item_primary_uom_code
2324 , mmtt.secondary_transaction_quantity
2325 , mmtt.secondary_uom_code
2326 , mmtt.fulfillment_base
2327 FROM mtl_material_transactions_temp mmtt
2328 WHERE mmtt.transaction_temp_id = p_temp_id
2329 AND NOT EXISTS(SELECT 1
2330 FROM mtl_material_transactions_temp t1
2331 WHERE t1.parent_line_id = mmtt.transaction_temp_id)
2332 UNION ALL
2333 SELECT mmtt.transaction_header_id
2334 , mmtt.transaction_temp_id
2335 , mmtt.parent_line_id --For checking bulk task
2336 , mmtt.inventory_item_id
2340 , mmtt.subinventory_code
2337 , mmtt.organization_id
2338 , mmtt.revision
2339 , mmtt.lot_number
2341 , mmtt.locator_id
2342 , mmtt.move_order_line_id
2343 , mmtt.transaction_quantity
2344 , mmtt.transaction_uom
2345 , mmtt.primary_quantity
2346 , mmtt.item_primary_uom_code
2347 , mmtt.secondary_transaction_quantity
2348 , mmtt.secondary_uom_code
2349 , mmtt.fulfillment_base
2350 FROM mtl_material_transactions_temp mmtt
2351 WHERE mmtt.parent_line_id = p_temp_id
2352 AND mmtt.parent_line_id <> mmtt.transaction_temp_id;
2353 -- This union by will end up getting all PARENTS too ***** mrana
2354
2355 CURSOR c_mo_line_info IS
2356 SELECT mtrh.move_order_type
2357 , mtrl.txn_source_id
2358 , mtrl.txn_source_line_id
2359 , mtrl.reference_id
2360 , mtrl.quantity
2361 , mtrl.uom_code
2362 , mtrl.quantity_delivered
2363 , mtrl.primary_quantity
2364 , mtrl.secondary_quantity
2365 , mtrl.secondary_uom_code
2366 FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
2367 WHERE mtrl.line_id = l_mo_line_id
2368 AND mtrh.header_id = mtrl.header_id;
2369
2370 CURSOR c_get_other_mmtt IS
2371 SELECT COUNT(*)
2372 FROM mtl_material_transactions_temp mmtt
2373 WHERE mmtt.move_order_line_id = l_mo_line_id
2374 AND mmtt.transaction_temp_id <> l_txn_temp_id
2375 AND NOT EXISTS(SELECT 1
2376 FROM mtl_material_transactions_temp t1
2377 WHERE t1.parent_line_id = mmtt.transaction_temp_id);
2378
2379 BEGIN
2380 x_return_status := fnd_api.g_ret_sts_success;
2381 g_debug := l_debug;
2382 g_module_name := l_proc_name;
2383 l_progress := '110';
2384 IF (l_debug = 1) THEN
2385 mdebug('IN : ' || l_proc_name);
2386 mdebug ('l_progress: ' || l_progress );
2387 mdebug('p_temp_id: ' || p_temp_id);
2388 mdebug('p_qty_rsn_id: ' || p_qty_rsn_id);
2389 mdebug('p_user_id: ' || p_user_id);
2390 mdebug('p_employee_id: ' || p_employee_id);
2391 mdebug('p_envoke_workflow: ' || p_envoke_workflow);
2392 mdebug('p_orig_temp_id: ' || p_orig_temp_id);
2393 END IF;
2394
2395 l_progress := '110';
2396 IF (l_debug = 1) THEN mdebug ('l_progress: ' || l_progress ); END IF;
2397
2398 IF p_qty_rsn_id IS NOT NULL
2399 THEN
2400 BEGIN
2401 SELECT 1
2402 INTO l_wf
2403 FROM mtl_transaction_reasons
2404 WHERE reason_id = p_qty_rsn_id
2405 AND workflow_name IS NOT NULL
2406 AND workflow_name <> ' '
2407 AND workflow_process IS NOT NULL
2408 AND workflow_process <> ' ';
2409 EXCEPTION
2410 WHEN NO_DATA_FOUND THEN
2411 l_wf := 0;
2412 END ;
2413 END IF;
2414
2415 IF p_envoke_workflow='N' THEN
2416 l_wf := 0;
2417 END IF;
2418
2419 l_progress := '115';
2420 IF (l_debug = 1) THEN mdebug ('l_progress: ' || l_progress ); END IF;
2421
2422 -- Insert the aborted task into wdth
2423 wms_insert_wdth_pvt.insert_into_wdth
2424 (x_return_status => x_return_status,
2425 p_txn_header_id => 0,
2426 p_transaction_temp_id => p_temp_id,
2427 p_transaction_batch_id => NULL,
2428 p_transaction_batch_seq => NULL,
2429 p_transfer_lpn_id => NULL,
2430 p_status => 11, -- aborted
2431 p_orig_transaction_temp_id => p_orig_temp_id); --Bug12812580
2432
2433 l_progress := '120';
2434 IF (l_debug = 1) THEN mdebug('l_wf: ' || l_wf); mdebug ('l_progress: ' || l_progress ); END IF;
2435
2436 OPEN c_mmtt_info;
2437 LOOP
2438 FETCH c_mmtt_info INTO l_txn_hdr_id
2439 , l_txn_temp_id
2440 , l_parent_line_id
2441 , l_item_id
2442 , l_org_id
2443 , l_rev
2444 , l_lot
2445 , l_sub
2446 , l_loc
2447 , l_mo_line_id
2448 , l_txn_qty
2449 , l_mmtt_transaction_uom
2450 , l_primary_quantity
2451 , l_primary_uom
2452 , l_sec_txn_qty
2453 , l_secondary_uom
2454 , l_fulfillment_base;
2455 EXIT WHEN c_mmtt_info%NOTFOUND;
2456
2457 l_progress := '200';
2458 IF (l_debug = 1) THEN
2459 mdebug ('l_progress: ' || l_progress );
2460 mdebug('l_mo_line_id:' || l_mo_line_id || ', l_txn_hdr_id:' || l_txn_hdr_id || ', l_txn_temp_id:' || l_txn_temp_id || ', l_parent_line_id:' || l_parent_line_id);
2461 mdebug('l_txn_qty:' || l_txn_qty || ', l_mmtt_transaction_uom:' || l_mmtt_transaction_uom || ', l_primary_quantity:' || l_primary_quantity || ', l_primary_uom:' || l_primary_uom);
2462 mdebug('l_sec_txn_qty:' || l_sec_txn_qty || ', l_secondary_uom:' || l_secondary_uom || ', l_fulfillment_base:' || l_fulfillment_base);
2463 END IF;
2464
2465
2466 IF l_wf > 0 THEN --{ Workflow exists then following should be executed
2467 l_progress := '220';
2468 IF (l_debug = 1) THEN
2469 mdebug ('l_progress: ' || l_progress );
2470 END IF;
2471 wms_workflow_wrappers.wf_wrapper(
2472 p_api_version => 1.0,
2473 p_init_msg_list => fnd_api.g_false,
2474 p_commit => fnd_api.g_false,
2475 p_org_id => l_org_id ,
2476 p_rsn_id => p_qty_rsn_id,
2477 p_calling_program => l_calling_program,
2478 p_tmp_id => l_txn_temp_id,
2479 p_quantity_picked => l_txn_qty,
2480 p_dest_sub => l_sub,
2481 p_dest_loc => l_loc,
2482 x_return_status => x_return_status ,
2483 x_msg_count => x_msg_count,
2484 x_msg_data => x_msg_data);
2485
2486 IF (l_debug = 1) THEN mdebug('x_return_status = ' || x_return_status); END IF;
2487 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2488 fnd_message.set_name('WMS', 'WMS_MULT_LPN_ERROR');
2489 fnd_msg_pub.ADD;
2490 RAISE fnd_api.g_exc_unexpected_error;
2491 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2492 fnd_message.set_name('WMS', 'WMS_MULT_LPN_ERROR');
2493 fnd_msg_pub.ADD;
2494 RAISE fnd_api.g_exc_error;
2495 END IF;
2496 -- MRANA : added the following 3392471 . : 26-feb-04
2497 -- Cascade delete the current MMTT and WDT That was just processed
2498
2499 DELETE FROM wms_dispatched_tasks WHERE transaction_temp_id = p_temp_id;
2500 IF SQL%NOTFOUND THEN
2501 mdebug ('NO WDT TO DELETE' );
2502 -- could not find the task to delete.. do not worry
2503 null;
2504 END IF;
2505 mdebug ('Calling INV_TRX_UTIL_PUB.delete_transaction ' );
2506 INV_TRX_UTIL_PUB.delete_transaction(
2507 x_return_status => x_return_status
2508 , x_msg_data => x_msg_data
2509 , x_msg_count => x_msg_count
2510 , p_transaction_temp_id => l_txn_temp_id
2511 ,p_update_parent => l_update_parent
2512 );
2513 mdebug ('x_return_status ' || x_return_status);
2514
2515 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2516 IF l_debug = 1 THEN
2517 mdebug('CLEANUP_TASK: Error occurred while deleting MMTT');
2518 END IF;
2519 RAISE fnd_api.g_exc_error;
2520 END IF;
2521 --}
2522 -- If WorkFlow does not exist only then the following shld be performed
2523 ELSE --{ wf <=0
2524 l_progress := '250';
2525 IF (l_debug = 1) THEN mdebug ('l_progress: ' || l_progress ); END IF;
2526 OPEN c_mo_line_info;
2527 FETCH c_mo_line_info
2528 INTO l_mo_type,
2529 l_mol_src_id,
2530 l_mol_src_line_id,
2531 l_mol_reference_id,
2532 l_mol_qty,
2533 l_mtrl_uom,
2534 l_mol_qty_delivered,
2535 l_mol_pri_qty,
2536 l_mol_sec_qty,
2537 l_mol_sec_uom;
2538 CLOSE c_mo_line_info;
2539
2540 l_progress := '260';
2541 IF (l_debug = 1) THEN
2542 mdebug('cleanup_task: transaction_uom :'|| l_mmtt_transaction_uom);
2543 mdebug('cleanup_task: move order line uom :'|| l_mtrl_uom);
2544 mdebug('cleanup_task: l_mol_pri_qty :'|| l_mol_pri_qty);
2545 mdebug('cleanup_task: l_mol_sec_qty :'|| l_mol_sec_qty);
2546 mdebug('cleanup_task: l_mol_sec_qty :'|| l_mol_sec_qty);
2547 END IF;
2548
2549 IF(l_mtrl_uom = l_primary_uom) THEN
2550 l_txn_qty := l_primary_quantity;
2551 ELSIF(l_mtrl_uom = NVL(l_secondary_uom, '@@@')) THEN
2552 l_txn_qty := l_sec_txn_qty;
2553 ELSIF(l_mtrl_uom = l_mmtt_transaction_uom) THEN
2554 l_txn_qty := l_txn_qty;
2555 ELSE
2556 l_txn_qty := inv_convert.inv_um_convert(
2557 item_id => l_item_id,
2558 precision => null,
2559 from_quantity => l_txn_qty,
2560 from_unit => l_mmtt_transaction_uom,
2561 to_unit => l_mtrl_uom,
2562 from_name => null,
2563 to_name => null);
2564 END IF;
2565
2566 l_progress := '270';
2567 IF (l_debug = 1) THEN
2568 mdebug ('l_progress: ' || l_progress );
2569 mdebug ('l_txn_qty : ' || l_txn_qty );
2570 END IF;
2571 /* IF (l_mtrl_uom <> l_mmtt_transaction_uom)
2572 THEN
2573 l_txn_qty := INV_Convert.inv_um_convert
2574 (item_id => l_item_id,
2575 precision => null,
2576 from_quantity => l_txn_qty,
2577 from_unit => l_mmtt_transaction_uom,
2578 to_unit => l_mtrl_uom,
2579 from_name => null,
2580 to_name => null);
2581 END IF;*/
2582
2583 l_progress := '280';
2584 IF (l_debug = 1) THEN
2585 mdebug ('l_progress: ' || l_progress );
2586 END IF;
2587 OPEN c_get_other_mmtt;
2588 FETCH c_get_other_mmtt INTO l_other_mmtt_count;
2589 CLOSE c_get_other_mmtt;
2590
2591 IF (l_debug = 1) THEN
2592 mdebug('CLEANUP_TASK: Number of MMTTs other than this MMTT : ' || l_other_mmtt_count);
2593 END IF;
2594
2595 IF l_other_mmtt_count > 0 THEN
2596 IF (l_debug = 1) THEN
2597 mdebug('CLEANUP_TASK: Other MMTT lines exist too. So cant close MO Line');
2598 END IF;
2599
2600 l_progress := '290';
2601 IF (l_debug = 1) THEN
2602 mdebug ('l_progress: ' || l_progress );
2603 END IF;
2604
2605 BEGIN
2606 IF (l_debug = 1) THEN
2607 mdebug('CLEANUP_TASK: Before we update MO and delete MMTT, we need to update reservation ');
2608 END IF;
2609
2610 SELECT NVL(mmtt.reservation_id,-1)
2611 ,mr.primary_reservation_quantity
2612 ,mr.primary_uom_code
2613 ,mr.reservation_quantity
2614 ,mr.reservation_uom_code
2615 ,mr.secondary_reservation_quantity
2616 ,mr.secondary_uom_code
2617 ,mr.lot_number
2618 ,mr.organization_id
2619 INTO l_reservation_id
2620 ,l_pri_rsv_qty
2621 ,l_pri_rsv_uom
2622 ,l_rsv_qty
2623 ,l_rsv_uom
2624 ,l_sec_rsv_qty
2625 ,l_sec_rsv_uom
2626 ,l_rsv_lot_num
2627 ,l_rsv_org_id
2628 FROM mtl_material_transactions_temp mmtt , mtl_reservations mr
2629 WHERE mmtt.transaction_temp_id = l_txn_temp_id
2630 AND mr.reservation_id = mmtt.reservation_id ;
2631
2632 IF (l_debug = 1) THEN
2633 mdebug('CLEANUP_TASK: l_reservation_id :'||l_reservation_id || ', l_pri_rsv_qty :' ||l_pri_rsv_qty ||', l_pri_rsv_uom :'||l_pri_rsv_uom || ', l_sec_rsv_qty :' ||l_sec_rsv_qty ||', l_sec_rsv_uom :'||l_sec_rsv_uom);
2634 mdebug('CLEANUP_TASK: MMTT.pri_qty :'||l_primary_quantity ||', l_rsv_qty :' ||l_rsv_qty||', l_rsv_uom :'||l_rsv_uom||', l_rsv_lot_num :'||l_rsv_lot_num||', l_rsv_org_id :'||l_rsv_org_id );
2635 END IF;
2636
2637 IF (l_pri_rsv_qty > l_primary_quantity OR (l_fulfillment_base = 'S' AND l_sec_rsv_qty > l_sec_txn_qty)) THEN --{
2638
2639 l_old_upd_resv_rec.reservation_id := l_reservation_id ;
2640 l_new_upd_resv_rec.primary_reservation_quantity := l_pri_rsv_qty - l_primary_quantity;
2641 l_new_upd_resv_rec.secondary_reservation_quantity := NVL(l_sec_rsv_qty,0) - NVL(l_sec_txn_qty,0);
2642
2643 IF(l_new_upd_resv_rec.secondary_reservation_quantity < 0) THEN
2644 l_new_upd_resv_rec.secondary_reservation_quantity := 0;
2645 END IF;
2646
2647 IF(l_new_upd_resv_rec.primary_reservation_quantity < 0 AND NVL(l_fulfillment_base, 'P') = 'S') THEN
2648 l_new_upd_resv_rec.primary_reservation_quantity := inv_convert.inv_um_convert (
2649 item_id => l_item_id,
2650 lot_number => l_rsv_lot_num,
2651 organization_id => l_rsv_org_id,
2652 precision => null,
2653 from_quantity => l_new_upd_resv_rec.secondary_reservation_quantity ,
2654 from_unit => l_sec_rsv_uom,
2655 to_unit => l_primary_uom,
2656 from_name => null,
2657 to_name => null);
2658 END IF;
2659
2660 IF(l_pri_rsv_uom = l_rsv_uom) THEN
2661 l_new_upd_resv_rec.reservation_quantity := l_rsv_qty - l_primary_quantity;
2662 ELSIF(l_sec_rsv_uom = l_rsv_uom) THEN
2663 l_new_upd_resv_rec.reservation_quantity := l_rsv_qty - l_sec_txn_qty;
2664 ELSE
2665 IF(NVL(l_fulfillment_base, 'P') = 'S') THEN
2666 l_new_upd_resv_rec.reservation_quantity := l_rsv_qty - inv_convert.inv_um_convert (
2667 item_id => l_item_id,
2668 lot_number => l_rsv_lot_num,
2669 organization_id => l_rsv_org_id,
2670 precision => null,
2671 from_quantity => l_sec_txn_qty,
2672 from_unit => l_secondary_uom,
2673 to_unit => l_rsv_uom,
2674 from_name => null,
2675 to_name => null);
2676 ELSE
2677 l_new_upd_resv_rec.reservation_quantity := l_rsv_qty - inv_convert.inv_um_convert (
2678 item_id => l_item_id,
2679 lot_number => l_rsv_lot_num,
2680 organization_id => l_rsv_org_id,
2681 precision => null,
2682 from_quantity => l_primary_quantity ,
2683 from_unit => l_pri_rsv_uom,
2684 to_unit => l_rsv_uom,
2685 from_name => null,
2686 to_name => null);
2687 END IF;
2688 END IF;
2689
2690 IF (l_debug = 1) THEN
2691 mdebug('CLEANUP_TASK: Calling update_reservation api : ' );
2692 END IF;
2693
2694 inv_reservation_pub.update_reservation(
2695 p_api_version_number => 1.0
2699 , x_msg_data => x_msg_data
2696 , p_init_msg_lst => fnd_api.g_false
2697 , x_return_status => x_return_status
2698 , x_msg_count => x_msg_count
2700 , p_original_rsv_rec => l_old_upd_resv_rec
2701 , p_to_rsv_rec => l_new_upd_resv_rec
2702 , p_original_serial_number => l_upd_dummy_sn
2703 , p_to_serial_number => l_upd_dummy_sn
2704 , p_validation_flag => fnd_api.g_true
2705 );
2706
2707 IF (l_debug = 1) THEN
2708 mdebug('CLEANUP_TASK: return of update_reservation api : ' || x_return_status);
2709 END IF;
2710 END IF; --}
2711 EXCEPTION
2712 WHEN NO_DATA_FOUND THEN
2713 IF (l_debug = 1) THEN
2714 mdebug('CLEANUP_TASK: There is no reservation for this MMTT ' );
2715 END IF;
2716 WHEN OTHERS THEN
2717 IF (l_debug = 1) THEN
2718 mdebug('CLEANUP_TASK: OTHERS EXCEPTION !!!! while Updating reservation ' );
2719 END IF;
2720 END;
2721
2722 l_progress := '295';
2723 IF (l_debug = 1) THEN
2724 mdebug ('l_progress: ' || l_progress );
2725 END IF;
2726
2727 INV_TRX_UTIL_PUB.delete_transaction(
2728 x_return_status => x_return_status
2729 , x_msg_data => x_msg_data
2730 , x_msg_count => x_msg_count
2731 , p_transaction_temp_id => l_txn_temp_id
2732 ,p_update_parent => l_update_parent
2733 );
2734
2735 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2736 IF l_debug = 1 THEN
2737 mdebug('CLEANUP_TASK: Error occurred while deleting MMTT');
2738 END IF;
2739 RAISE fnd_api.g_exc_error;
2740 END IF;
2741
2742 IF (l_wf <= 0) or (p_qty_rsn_id <= 0) then
2743 l_progress := '300';
2744 IF (l_debug = 1) THEN
2745 mdebug ('l_progress: ' || l_progress );
2746 END IF;
2747 UPDATE mtl_txn_request_lines
2748 SET quantity_detailed = quantity_detailed - l_txn_qty
2749 , secondary_quantity_detailed = DECODE(secondary_uom_code,NULL,NULL,
2750 secondary_quantity_detailed - l_sec_txn_qty) -- Bug 8312574
2751 , last_update_date = SYSDATE
2752 , last_updated_by = p_user_id
2753 WHERE line_id = l_mo_line_id;
2754 mdebug ('quantity_detailed : ' || l_txn_qty );
2755 END IF;
2756 ELSE
2757 L_progress := '310';
2758 if (l_debug = 1) THEN
2759 mdebug ('l_progress: ' || l_progress );
2760 mdebug('CLEANUP_TASK: Just one MMTT line exists. Close MO');
2761 END IF;
2762
2763 IF (l_mo_type IN ( INV_GLOBALS.G_MOVE_ORDER_PICK_WAVE,
2764 INV_GLOBALS.G_MOVE_ORDER_MFG_PICK)) THEN
2765 l_progress := '320';
2766 IF (l_debug = 1) THEN
2767 mdebug ('l_progress: ' || l_progress );
2768 END IF;
2769 DELETE FROM wms_dispatched_tasks WHERE transaction_temp_id = p_temp_id;
2770
2771 IF SQL%NOTFOUND THEN
2772 mdebug ('NO WDT TO DELETE' );
2773 -- could not find the task to delete.. do not worry
2774 null;
2775 END IF;
2776 inv_mo_backorder_pvt.backorder(
2777 p_line_id => l_mo_line_id
2778 , x_return_status => x_return_status
2779 , x_msg_count => x_msg_count
2780 , x_msg_data => x_msg_data
2781 );
2782
2783 IF x_return_status <> l_g_ret_sts_success
2784 THEN
2785 IF (l_debug = 1) THEN
2786 mdebug('CLEANUP_TASK: Unexpected error occurrend while calling BackOrder API');
2787 END IF;
2788 RAISE fnd_api.g_exc_error;
2789 END IF;
2790
2791
2792 ELSIF l_mo_type IN (INV_GLOBALS.G_MOVE_ORDER_REQUISITION, INV_GLOBALS.G_MOVE_ORDER_REPLENISHMENT) THEN
2793 l_progress := '370';
2794 IF (l_debug = 1) THEN mdebug ('l_progress: ' || l_progress ); END IF;
2795 UPDATE mtl_txn_request_lines
2796 SET quantity_detailed = quantity_delivered
2797 , secondary_quantity_detailed =
2798 DECODE(secondary_uom_code,NULL,NULL,secondary_quantity_delivered) -- Bug 8312574
2799 , last_update_date = SYSDATE
2800 , last_updated_by = p_user_id
2801 WHERE line_id = l_mo_line_id;
2802
2803 INV_TRX_UTIL_PUB.delete_transaction(
2804 x_return_status => x_return_status
2805 , x_msg_data => x_msg_data
2806 , x_msg_count => x_msg_count
2807 , p_transaction_temp_id => l_txn_temp_id
2808 ,p_update_parent => l_update_parent
2809 );
2810
2811 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2812 IF l_debug = 1 THEN
2813 mdebug('CLEANUP_TASK: Error occurred while deleting MMTT');
2814 END IF;
2815 RAISE fnd_api.g_exc_error;
2816 END IF;
2817
2818 --Bug 5162468 for Fill kill zero pick condition
2819 --close the MO line
2820
2821 IF (l_kill_mo_profile = 1) and (l_mo_type = INV_GLOBALS.G_MOVE_ORDER_REPLENISHMENT)
2822 AND ((l_other_mmtt_count = 0) AND (NVL(l_mol_qty_delivered,0) =0)) THEN
2823
2824 IF (l_debug = 1) THEN
2825 l_progress := '375';
2826 mdebug ('l_progress: ' || l_progress);
2827 mdebug ('Check for MO line closing for Fill Kill pick none ... ');
2828 mdebug('Replenishment Move Order... pending task count :'|| l_other_mmtt_count);
2829 mdebug('Replenishment Move Order... quantity delivered :'|| l_mol_qty_delivered);
2830 mdebug('Replenishment Move Order... Closing the Move Order');
2831 END IF;
2832
2833 INV_MO_ADMIN_PUB.close_line(1.0,'F','F','F',l_mo_line_id,x_msg_count,x_msg_data,l_return_status);
2834 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2835 RAISE FND_API.G_EXC_ERROR;
2836 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2838 END IF;
2839 wms_task_dispatch_gen.process_remaining_wdds_repl(l_mo_line_id, l_org_id); --BUG14014540
2840 END IF;
2841
2842
2843 l_progress := '380';
2844 IF (l_debug = 1) THEN mdebug ('l_progress: ' || l_progress ); END IF;
2845 IF (l_wf <= 0) or (p_qty_rsn_id <= 0) then
2846 UPDATE mtl_txn_request_lines
2847 SET quantity_detailed = quantity_delivered
2848 , secondary_quantity_detailed =
2849 DECODE(secondary_uom_code,NULL,NULL,secondary_quantity_delivered) -- Bug 8312574
2850 , last_update_date = SYSDATE
2851 , last_updated_by = p_user_id
2852 WHERE line_id = l_mo_line_id;
2853 END IF;
2854 END IF;
2855 END IF;
2856 END IF; --} WF <=0
2857 END LOOP;
2858
2859 wms_txnrsn_actions_pub.log_exception(
2860 p_api_version_number => 1.0
2861 , p_init_msg_lst => fnd_api.g_false
2862 , p_commit => fnd_api.g_false
2863 , x_return_status => x_return_status
2864 , x_msg_count => x_msg_count
2865 , x_msg_data => x_msg_data
2866 , p_organization_id => l_org_id
2867 , p_item_id => l_item_id
2868 , p_revision => l_rev
2869 , p_lot_number => l_lot
2870 , p_subinventory_code => l_sub
2871 , p_locator_id => l_loc
2872 , p_mmtt_id => p_temp_id
2873 , p_task_id => p_temp_id
2874 , p_reason_id => p_qty_rsn_id
2875 , p_discrepancy_type => 1
2876 , p_user_id => p_employee_id);
2877
2878 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2879 fnd_message.set_name('WMS', 'WMS_LOG_EXCEPTION_FAIL');
2883 fnd_message.set_name('WMS', 'WMS_LOG_EXCEPTION_FAIL');
2880 fnd_msg_pub.ADD;
2881 RAISE fnd_api.g_exc_unexpected_error;
2882 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2884 fnd_msg_pub.ADD;
2885 RAISE fnd_api.g_exc_error;
2886 END IF;
2887
2888 l_progress := '390';
2889
2890 -- Bug# 5760606 - set global name back since it is changed in the above call to log_exception
2891 g_module_name := l_proc_name;
2892
2893 l_progress := '400';
2894
2895 -- For checking Bulk task.Check if p_temp_id passed is also
2896 --parent line id.If it's bulk task then call delete transaction.
2897
2898
2899 IF l_parent_line_id = p_temp_id
2900 THEN
2901 mdebug('Now calling delete transaction for parent line');
2902 INV_TRX_UTIL_PUB.delete_transaction(
2903 x_return_status => x_return_status
2904 , x_msg_data => x_msg_data
2905 , x_msg_count => x_msg_count
2906 , p_transaction_temp_id => l_parent_line_id
2907 ,p_update_parent => l_update_parent
2908 );
2909
2910 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2911 mdebug ('Clean up task in loop for deleting parent line ');
2912 IF l_debug = 1 THEN
2913 mdebug('CLEANUP_TASK: Error occurred while deleting parent line inMMTT');
2914 END IF;
2915 RAISE fnd_api.g_exc_error;
2916 END IF;
2917 END IF; --for parent line IF
2918
2919 CLOSE c_mmtt_info;
2920 --COMMIT; --???
2921 mdebug('END : ' || l_proc_name );
2922 EXCEPTION
2923 WHEN fnd_api.g_exc_error THEN
2924 x_return_status := l_g_ret_sts_error;
2925 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2926 mdebug('ROLLBACK ' );
2927 ROLLBACK ;
2928 mdebug('l_progress = ' || l_proc_name || ':'|| l_progress);
2929 mdebug('RAISE fnd_api.g_exc_error: ' || SQLERRM);
2930 WHEN OTHERS THEN
2931 x_return_status := l_g_ret_sts_unexp_error;
2932 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2933 mdebug('ROLLBACK ' );
2934 ROLLBACK ;
2935 mdebug('l_progress = ' || l_proc_name || ':'|| l_progress);
2936 mdebug('RAISE fnd_api.g_exc_error: ' || SQLERRM);
2937
2938 END cleanup_task;
2939
2940
2941 PROCEDURE process_exceptions
2942 (p_organization_id IN NUMBER,
2943 p_employee_id IN NUMBER,
2944 p_effective_start_date IN DATE,
2945 p_effective_end_date IN DATE,
2946 p_inventory_item_id IN NUMBER,
2947 p_revision IN VARCHAR2,
2948 p_discrepancies IN VARCHAR2,
2949 x_return_status OUT nocopy VARCHAR2,
2950 x_msg_count OUT nocopy NUMBER,
2951 x_msg_data OUT nocopy VARCHAR2) IS
2952
2953 l_discrepancies VARCHAR2(4000) := p_discrepancies;
2954 l_start_index NUMBER;
2955 l_end_index NUMBER;
2956 l_discrepancy VARCHAR2(1000);
2957 l_reason_context_code VARCHAR2(2);
2958 l_reason_id NUMBER;
2959 l_transaction_temp_id NUMBER;
2960 l_subinventory_code wms_exceptions.subinventory_code%TYPE;
2961 l_locator_id NUMBER;
2962 l_lpn_id NUMBER;
2963 l_lot_number mtl_lot_numbers.lot_number%TYPE;
2964 l_workflow_name mtl_transaction_reasons.workflow_name%TYPE;
2965 l_workflow_process mtl_transaction_reasons.workflow_process%TYPE;
2966 l_user_id NUMBER := fnd_global.user_id;
2967
2968 BEGIN
2969 x_return_status := 'S';
2970
2971 IF p_discrepancies IS NOT NULL THEN
2972 l_start_index := Instr(l_discrepancies, '{');
2973 l_end_index := Instr(l_discrepancies, '}');
2974
2975 WHILE l_start_index > 0 LOOP
2976 l_discrepancy := Substr(l_discrepancies, l_start_index + 1, l_end_index - l_start_index -1);
2977 l_discrepancies := Substr(l_discrepancies, l_end_index + 1);
2978
2979 IF g_trace_on = 1 THEN
2980 mdebug(l_discrepancy, 'PROCESS_EXCEPTIONS');
2981 END IF;
2982
2983 l_end_index := Instr(l_discrepancy, '|');
2984 l_reason_context_code := Substr(l_discrepancy, 1, l_end_index -1);
2985
2986 IF g_trace_on = 1 THEN
2987 mdebug('Reason Context Code: ' || l_reason_context_code, 'PROCESS_EXCEPTIONS');
2988 END IF;
2989
2990 ---
2991 IF g_trace_on = 1 THEN
2992 mdebug('p_employee_id: ' || p_employee_id, 'PROCESS_EXCEPTIONS');
2993 mdebug('l_user_id: ' || l_user_id, 'PROCESS_EXCEPTIONS');
2994 END IF;
2995 -----
2996
2997 l_discrepancy := Substr(l_discrepancy, l_end_index + 1);
2998
2999 l_end_index := Instr(l_discrepancy, '|');
3000 l_reason_id := To_number(Substr(l_discrepancy, 1, l_end_index -1));
3001
3002 IF g_trace_on = 1 THEN
3003 mdebug('Reason ID: ' || l_reason_id, 'PROCESS_EXCEPTIONS');
3004 END IF;
3005
3006 l_discrepancy := Substr(l_discrepancy, l_end_index + 1);
3007
3008 l_end_index := Instr(l_discrepancy, '|');
3009 l_transaction_temp_id := To_number(Substr(l_discrepancy, 1, l_end_index -1));
3010
3011 IF g_trace_on = 1 THEN
3012 mdebug('Transaction Temp ID: ' || l_transaction_temp_id, 'PROCESS_EXCEPTIONS');
3013 END IF;
3014
3015 l_discrepancy := Substr(l_discrepancy, l_end_index + 1);
3016
3017 l_end_index := Instr(l_discrepancy, '|');
3018 l_subinventory_code := Substr(l_discrepancy, 1, l_end_index -1);
3019
3020 IF g_trace_on = 1 THEN
3021 mdebug('Subinventory Code: ' || l_subinventory_code, 'PROCESS_EXCEPTIONS');
3022 END IF;
3023
3024 l_discrepancy := Substr(l_discrepancy, l_end_index + 1);
3025
3026 l_end_index := Instr(l_discrepancy, '|');
3027 l_locator_id := To_number(Substr(l_discrepancy, 1, l_end_index -1));
3028
3029 IF g_trace_on = 1 THEN
3030 mdebug('Locator ID: ' || l_locator_id, 'PROCESS_EXCEPTIONS');
3031 END IF;
3032
3033 l_discrepancy := Substr(l_discrepancy, l_end_index + 1);
3034
3035 l_end_index := Instr(l_discrepancy, '|');
3036 l_workflow_name := Substr(l_discrepancy, 1, l_end_index -1);
3037
3038 IF Upper(l_workflow_name) = 'NULL' THEN
3039 l_workflow_name := NULL;
3040 END IF;
3041
3042 IF g_trace_on = 1 THEN
3043 mdebug('Workflow Name: ' || l_workflow_name, 'PROCESS_EXCEPTIONS');
3044 END IF;
3045
3046 l_discrepancy := Substr(l_discrepancy, l_end_index + 1);
3047
3048 l_end_index := Instr(l_discrepancy, '|');
3049
3050 IF l_end_index <> 0 THEN
3051 l_workflow_process := Substr(l_discrepancy, 1, l_end_index -1);
3052 ELSE
3053 l_workflow_process := Substr(l_discrepancy, 1);
3054 END IF;
3055
3056 IF Upper(l_workflow_process) = 'NULL' THEN
3057 l_workflow_process := NULL;
3058 END IF;
3059
3060 IF g_trace_on = 1 THEN
3061 mdebug('Workflow Process: ' || l_workflow_process, 'PROCESS_EXCEPTIONS');
3062 END IF;
3063
3064 IF (l_reason_context_code = 'PS') THEN
3065 l_discrepancy := Substr(l_discrepancy, l_end_index + 1);
3066
3067 l_end_index := Instr(l_discrepancy, '|');
3068 l_lpn_id := To_number(Substr(l_discrepancy, 1, l_end_index -1));
3069
3070 IF g_trace_on = 1 THEN
3071 mdebug('LPN ID: ' || l_lpn_id, 'PROCESS_EXCEPTIONS');
3072 END IF;
3073
3074 l_discrepancy := Substr(l_discrepancy, l_end_index + 1);
3075
3076 l_lot_number := Substr(l_discrepancy, 1);
3077
3078 IF g_trace_on = 1 THEN
3079 mdebug('Lot Number: ' || l_lot_number, 'PROCESS_EXCEPTIONS');
3080 END IF;
3081
3082 END IF;
3083
3084 IF (g_trace_on = 1) THEN
3085 mdebug('Inserting into exceptions', 'PROCESS_EXCEPTIONS');
3086 END IF;
3087
3088 log_exception
3089 (p_api_version_number => 1.0,
3090 p_init_msg_lst => 'F',
3091 p_commit => 'F',
3092 x_return_status => x_return_status,
3093 x_msg_count => x_msg_count,
3094 x_msg_data => x_msg_data,
3095 p_organization_id => p_organization_id,
3096 p_mmtt_id => l_transaction_temp_id,
3097 p_task_id => NULL,
3098 p_reason_id => l_reason_id,
3099 p_subinventory_code => l_subinventory_code,
3100 p_locator_id => l_locator_id,
3101 p_discrepancy_type => 1,
3102 --p_user_id => l_user_id,
3103 p_user_id => p_employee_id,
3104 p_item_id => p_inventory_item_id,
3105 p_revision => p_revision,
3106 p_lot_number => l_lot_number,
3107 p_lpn_id => l_lpn_id);
3108
3109 l_start_index := Instr(l_discrepancies, '{');
3110 l_end_index := Instr(l_discrepancies, '}');
3111 END LOOP;
3112
3113 END IF;
3114 END process_exceptions;
3115
3116 --Bug 6278066 Added a wrapper for log_exception
3117 PROCEDURE Log_exception
3118 ( x_return_status OUT NOCOPY VARCHAR2
3119 , x_msg_count OUT NOCOPY NUMBER
3120 , x_msg_data OUT NOCOPY VARCHAR2
3121 , p_organization_id IN NUMBER
3122 , p_mmtt_id IN NUMBER
3123 , p_task_id IN NUMBER
3124 , p_reason_id IN NUMBER
3125 , p_subinventory_code IN VARCHAR2
3126 , p_locator_id IN NUMBER
3127 , p_discrepancy_type IN NUMBER
3128 , p_user_id IN VARCHAR2
3129 , p_item_id IN NUMBER:=NULL
3130 , p_revision IN VARCHAR2:=NULL
3131 , p_lot_number IN VARCHAR2:=NULL
3132 , p_lpn_id IN NUMBER:=NULL
3133 , p_is_loc_desc IN VARCHAR2
3134 )IS
3135 l_is_loc_desc BOOLEAN;
3136 l_return_err VARCHAR2(230);
3137 BEGIN
3138 IF p_is_loc_desc = 'false' THEN
3139 l_is_loc_desc := FALSE;
3140 ELSE
3141 l_is_loc_desc := TRUE;
3142 END IF;
3143
3144 Log_exception(
3145 p_api_version_number => 1.0
3146 , p_init_msg_lst => fnd_api.g_false
3147 , p_commit => fnd_api.g_false
3148 , x_return_status => x_return_status
3149 , x_msg_count => x_msg_count
3150 , x_msg_data => x_msg_data
3151 , p_organization_id => p_organization_id
3152 , p_mmtt_id => p_mmtt_id
3153 , p_task_id => p_task_id
3154 , p_reason_id => p_reason_id
3155 , p_subinventory_code => p_subinventory_code
3156 , p_locator_id => p_locator_id
3157 , p_discrepancy_type => p_discrepancy_type
3158 , p_user_id => p_user_id
3159 , p_item_id => p_item_id
3160 , p_revision => p_revision
3161 , p_lot_number => p_lot_number
3162 , p_lpn_id => p_lpn_id
3163 , p_is_loc_desc => l_is_loc_desc
3164 );
3165
3166 EXCEPTION
3167 WHEN OTHERS THEN
3168 x_return_status:=FND_API.G_RET_STS_ERROR;
3169 l_return_err := 'Insert into WMS_Exceptions failed'|| substrb(sqlerrm,1,55);
3170 raise_application_error(-20000,l_return_err);
3171
3172 END Log_exception;
3173
3174 END wms_txnrsn_actions_pub ;