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