[Home] [Help]
PACKAGE BODY: APPS.INV_MO_CANCEL_PVT
Source
1 PACKAGE BODY INV_MO_CANCEL_PVT AS
2 /* $Header: INVMOCNB.pls 120.8.12010000.4 2010/02/25 11:31:32 mporecha ship $ */
3
4 g_version_printed BOOLEAN := FALSE;
5 g_pkg_name VARCHAR2(50) := 'INV_MO_CANCEL_PVT';
6 g_auto_del_alloc VARCHAR2(1); --ER3969328: CI project
7 g_conv_precision CONSTANT NUMBER := 5;
8
9
10 PROCEDURE DEBUG(p_message IN VARCHAR2, p_module IN VARCHAR2) IS
11 BEGIN
12 IF NOT g_version_printed THEN
13 inv_log_util.trace('$Header: INVMOCNB.pls 120.8.12010000.4 2010/02/25 11:31:32 mporecha ship $',g_pkg_name, 3);
14 g_version_printed := TRUE;
15 END IF;
16
17 inv_log_util.trace(p_message, g_pkg_name || '.' || p_module, 9);
18 END;
19
20
21 /**
22 * Cancels a Move Order line and sets the status to Closed or Cancelled by Source.
23 * 1. Undetailed reservations are deleted.
24 * 2. Detailed reservations are delinked from the allocations.
25 * 3. Allocation is deleted for a WMS Organization.
26 * 4. Move Order Line is updated.
27 * For more info... see the documentation given in the Spec.
28 */
29 PROCEDURE cancel_move_order_line
30 ( x_return_status OUT NOCOPY VARCHAR2
31 , x_msg_count OUT NOCOPY NUMBER
32 , x_msg_data OUT NOCOPY VARCHAR2
33 , p_line_id IN NUMBER
34 , p_delete_reservations IN VARCHAR2
35 , p_txn_source_line_id IN NUMBER DEFAULT NULL
36 , p_delete_alloc IN VARCHAR2 DEFAULT NULL -- ER3969328: CI project
37 , p_delivery_detail_id IN NUMBER DEFAULT NULL -- Planned Crossdocking project
38 ) IS
39
40 l_line_status NUMBER;
41 l_org_id NUMBER;
42 l_is_wms_org BOOLEAN;
43 l_txn_source_line_id NUMBER;
44 l_quantity NUMBER;
45 l_quantity_detailed NUMBER;
46 l_quantity_to_delete NUMBER;
47 l_deleted_quantity NUMBER;
48 l_max_delete_quantity NUMBER;
49 l_transaction_temp_id NUMBER;
50 l_primary_quantity NUMBER;
51 l_parent_line_id NUMBER;
52 l_task_status NUMBER;
53 l_reservation_id NUMBER;
54 l_delete_reservations VARCHAR2(1);
55 l_task_dispatched VARCHAR2(1);
56 l_rsv_count NUMBER;
57 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
58 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
59 l_update_rec inv_reservation_global.mtl_reservation_rec_type;
60 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
61 l_error_code NUMBER;
62 l_alloc_flag VARCHAR2(1); --ER3969328: CI project
63 l_flag BOOLEAN; --ER3969328: CI project
64 l_count NUMBER; --ER3969328: CI project
65 l_count_alloc NUMBER; --ER3969328: CI project
66 l_sec_quantity NUMBER; --INVCONV
67 l_sec_quantity_detailed NUMBER; --INVCONV
68 l_sec_quantity_to_delete NUMBER; --INVCONV
69 l_max_delete_sec_quantity NUMBER; --INVCONV
70 l_sec_deleted_quantity NUMBER; --INVCONV
71 l_secondary_quantity NUMBER; --INVCONV
72 l_move_order_type NUMBER;
73 l_total_rsv_quantity NUMBER;
74 l_total_wdd_req_qty NUMBER;
75 l_extra_rsv_quantity NUMBER;
76 -- INVCONV - Added Qty2
77 l_extra_rsv_quantity2 NUMBER;
78 l_total_rsv_quantity2 NUMBER;
79 l_total_wdd_sec_req_qty NUMBER;
80 l_rsv_index NUMBER;
81
82 l_debug NUMBER;
83 l_dummy VARCHAR2(1);
84 l_api_return_status VARCHAR2(1);
85
86 l_serial_tbl inv_reservation_global.serial_number_tbl_type;
87
88 TYPE xdock_rsv_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
89 l_xdock_rsv_tbl xdock_rsv_tbl;
90
91 record_locked EXCEPTION;
92 PRAGMA EXCEPTION_INIT (record_locked, -54);
93
94 -- INVCONV -Use NVL with secondary_quantity_detailed similar to Qty1
95 CURSOR c_line_info IS
96 SELECT quantity
97 , NVL(quantity_detailed, 0)
98 , secondary_quantity
99 , NVL(secondary_quantity_detailed,0)
100 , organization_id
101 , txn_source_line_id
102 FROM mtl_txn_request_lines
103 WHERE line_id = p_line_id
104 FOR UPDATE NOWAIT;
105
106 CURSOR c_reservations IS
107 SELECT mr.reservation_id
108 FROM mtl_reservations mr
109 WHERE mr.demand_source_type_id IN (2, 8)
110 AND nvl(mr.staged_flag,'N') <> 'Y'
111 AND demand_source_line_detail IS NULL
112 AND mr.demand_source_line_id = l_txn_source_line_id
113 AND mr.primary_reservation_quantity > NVL(mr.detailed_quantity, 0);
114
115 CURSOR c_mmtt_info IS
116 SELECT mmtt.transaction_temp_id
117 , ABS(mmtt.primary_quantity)
118 , ABS(mmtt.secondary_transaction_quantity) --INVCONV
119 , mmtt.reservation_id
120 , mmtt.parent_line_id
121 FROM mtl_material_transactions_temp mmtt
122 WHERE mmtt.move_order_line_id = p_line_id
123 AND NOT EXISTS (SELECT 1 FROM mtl_material_transactions_temp t
124 WHERE t.parent_line_id = mmtt.transaction_temp_id)
125 ORDER BY mmtt.transaction_quantity ASC
126 FOR UPDATE NOWAIT;
127
128 CURSOR c_dispatched_task IS
129 SELECT status
130 FROM wms_dispatched_tasks
131 WHERE (l_parent_line_id IS NULL AND transaction_temp_id = l_transaction_temp_id)
132 OR (l_parent_line_id IS NOT NULL AND transaction_temp_id = l_parent_line_id);
133
134 -- INVCONV - Added Qty2
135 CURSOR c_primary_rsv_qty IS
136 SELECT sum(primary_reservation_quantity),
137 sum(secondary_reservation_quantity)
138 FROM mtl_reservations
139 WHERE reservation_id IN (SELECT DISTINCT reservation_id
140 FROM mtl_material_transactions_temp
141 WHERE move_order_line_id = p_line_id);
142
143 CURSOR c_wdd_requested_qty is
144 SELECT sum(requested_quantity), sum(requested_quantity2)--INVCONV
145 FROM wsh_delivery_details
146 WHERE move_order_line_id = p_line_id
147 AND released_status ='S';
148
149 --
150 -- ER3969328: CI project. Added this cursor
151 -- to pick only those allocations which are
152 -- currently not being transacted.
153 --
154 CURSOR c_mmtt IS
155 SELECT mmtt.transaction_temp_id
156 FROM mtl_material_transactions_temp mmtt
157 WHERE mmtt.move_order_line_id = p_line_id
158 AND nvl(mmtt.transaction_status,1) = 2
159 FOR UPDATE NOWAIT;
160
161 CURSOR c_xdock_rsv
162 ( p_wdd_id IN NUMBER
163 ) IS
164 SELECT mr.reservation_id
165 FROM mtl_reservations mr
166 WHERE mr.demand_source_line_detail = p_wdd_id
167 AND mr.demand_source_type_id IN (2,8)
168 AND NVL(mr.crossdock_flag,'N') = 'Y'
169 FOR UPDATE NOWAIT;
170
171 BEGIN
172 x_return_status := fnd_api.g_ret_sts_success;
173 SAVEPOINT cancelmo_sp;
174
175 -- {{
176 -- BEGIN cancel_move_order_line }}
177 --
178 IF (l_debug = 1) THEN
179 DEBUG('Entered with parameters: '
180 || ' p_line_id: ' || to_char(p_line_id)
181 || ', p_delete_reservations: ' || p_delete_reservations
182 || ', p_txn_source_line_id: ' || to_char(p_txn_source_line_id)
183 || ', p_delete_alloc: ' || p_delete_alloc
184 || ', p_delivery_detail_id: ' || to_char(p_delivery_detail_id)
185 ,'CANCEL_MOVE_ORDER_LINE');
186 END IF;
187
188 -- Initializations
189 l_deleted_quantity := 0;
190 l_flag := FALSE;
191 l_count := 0;
192 l_count_alloc := 0;
193 l_sec_deleted_quantity := 0;
194 l_total_rsv_quantity := 0;
195 l_total_wdd_req_qty := -1;
196 l_extra_rsv_quantity := 0;
197 l_extra_rsv_quantity2 := 0;
198 l_total_rsv_quantity2 := 0;
199 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
200
201 IF p_line_id IS NOT NULL
202 THEN
203 -- {
204 BEGIN
205 SELECT NVL(mtrh.move_order_type,0)
206 INTO l_move_order_type
207 FROM mtl_txn_request_lines mtrl
208 , mtl_txn_request_headers mtrh
209 WHERE mtrl.line_id = p_line_id
210 AND mtrh.header_id = mtrl.header_id;
211
212 IF (l_debug = 1) THEN
213 DEBUG('Move order type: ' || to_char(l_move_order_type)
214 ,'CANCEL_MOVE_ORDER_LINE');
215 END IF;
216
217 IF l_move_order_type <= 0
218 THEN
219 RAISE fnd_api.g_exc_unexpected_error;
220 END IF;
221
222 EXCEPTION
223 WHEN OTHERS THEN
224 IF (l_debug = 1) THEN
225 DEBUG('Unxexpected error querying MO type: '
226 || sqlerrm ,'CANCEL_MOVE_ORDER_LINE');
227 END IF;
228 RAISE fnd_api.g_exc_unexpected_error;
229 END;
230
231 IF l_move_order_type <> INV_GLOBALS.G_MOVE_ORDER_PUT_AWAY
232 THEN
233 -- {
234 -- {{
235 -- Verify that cancel API works as before for non-putaway
236 -- (sales/internal order, WIP) move order lines }}
237 --
238 -- Query Move Order Line info
239 OPEN c_line_info;
240 FETCH c_line_info
241 INTO l_quantity
242 , l_quantity_detailed
243 , l_sec_quantity
244 , l_sec_quantity_detailed
245 , l_org_id
246 , l_txn_source_line_id; --INVCONV
247 IF c_line_info%NOTFOUND THEN
248 IF (l_debug = 1) THEN
249 DEBUG('Error: Could not find the Move Order Line'
250 ,'CANCEL_MOVE_ORDER_LINE');
251 END IF;
252 fnd_message.set_name('INV','INV_MO_LINE_NOT_FOUND');
253 fnd_message.set_token('LINE_ID',p_line_id);
254 fnd_msg_pub.ADD;
255 RAISE fnd_api.g_exc_error;
256 END IF;
257 CLOSE c_line_info;
258
259 l_is_wms_org := inv_install.adv_inv_installed(l_org_id);
260
261 --
262 -- If not all of the move order quantity is detailed, we may
263 -- need to delete some outstanding reservations here. If this API
264 -- is called with the delete rsvs flag set to Yes, then we must reduce
265 -- reservation quantity by the same amount that we reduce move order
266 -- quantity. The reservations which are eligible to be updated at this
267 -- point are the reservations where rsv quantity > detailed quantity.
268 --
269 IF p_delete_reservations IS NULL OR p_delete_reservations <> 'Y'
270 THEN
271 l_delete_reservations := 'N';
272 ELSE
273 l_delete_reservations := 'Y';
274 END IF;
275
276 IF (l_debug = 1) THEN
277 DEBUG('Delete Reservations (Y/N) = '
278 || l_delete_reservations, 'CANCEL_MOVE_ORDER_LINE');
279 END IF;
280
281 IF l_delete_reservations = 'Y' AND l_quantity > l_quantity_detailed
282 THEN
283 -- {
284 l_quantity_to_delete := l_quantity - l_quantity_detailed;
285 l_sec_quantity_to_delete := l_sec_quantity - l_sec_quantity_detailed; --INVCONV
286 IF (l_debug = 1) THEN
287 DEBUG('Deleting Reservations for undetailed qty = '
288 || l_quantity_to_delete, 'Cancel_Move_Order_Line');
289 DEBUG('Deleting Reservations for secondary undetailed qty = '
290 || l_sec_quantity_to_delete, 'Cancel_Move_Order_Line');
291 END IF;
292
293 -- we query by the sales order line id. If that value is not
294 -- passed in, we need to get it from shipping table
295 IF p_txn_source_line_id IS NOT NULL THEN
296 l_txn_source_line_id := p_txn_source_line_id;
297 END IF;
298
299 IF (l_debug = 1) THEN
300 DEBUG('Source Line ID = ' || l_txn_source_line_id
301 ,'CANCEL_MOVE_ORDER_LINE');
302 END IF;
303
304 -- find all reservations where reservation quantity exceeds
305 -- detailed quantity.
306 OPEN c_reservations;
307 LOOP
308 -- {
309 EXIT WHEN l_quantity_to_delete <= 0;
310 FETCH c_reservations INTO l_reservation_id;
311 EXIT WHEN c_reservations%NOTFOUND;
312
313 l_rsv_rec.reservation_id := l_reservation_id;
314
315 IF (l_debug = 1) THEN
316 DEBUG('Reservation ID = ' || l_reservation_id
317 ,'CANCEL_MOVE_ORDER_LINE');
318 END IF;
319
320 -- query reservation
321 l_api_return_status := fnd_api.g_ret_sts_success;
322 inv_reservation_pvt.query_reservation
323 ( p_api_version_number => 1.0
324 , p_init_msg_lst => fnd_api.g_false
325 , x_return_status => l_api_return_status
326 , x_msg_count => x_msg_count
327 , x_msg_data => x_msg_data
328 , p_query_input => l_rsv_rec
329 , x_mtl_reservation_tbl => l_rsv_tbl
330 , x_mtl_reservation_tbl_count => l_rsv_count
331 , x_error_code => l_error_code
332 );
333
334 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
335 IF (l_debug = 1) THEN
336 DEBUG('query_reservation returned success','CANCEL_MOVE_ORDER_LINE');
337 END IF;
338 ELSE
339 IF (l_debug = 1) THEN
340 DEBUG('Error: Query Reservations return status: '
341 || l_api_return_status
342 ,'CANCEL_MOVE_ORDER_LINE');
343 END IF;
344 fnd_message.set_name('INV','INV_QRY_RSV_FAILED');
345 fnd_msg_pub.ADD;
346 RAISE fnd_api.g_exc_unexpected_error;
347 END IF;
348
349 IF l_rsv_count <= 0 THEN
350 IF (l_debug = 1) THEN
351 DEBUG('Error: Query Reservations returned Reservation Count 0'
352 ,'CANCEL_MOVE_ORDER_LINE');
353 END IF;
354 fnd_message.set_name('INV','INV_NO_RSVS_FOUND');
355 fnd_msg_pub.ADD;
356 RAISE fnd_api.g_exc_unexpected_error;
357 END IF;
358
359 l_update_rec := l_rsv_tbl(1);
360 l_update_rec.reservation_quantity := NULL;
361
362 -- Reservation Qty can be reduced by a Maximum of
363 -- either Primary Rsv Qty or Detailed Qty.
364 l_max_delete_quantity
365 := l_update_rec.primary_reservation_quantity
366 - l_update_rec.detailed_quantity;
367 l_max_delete_sec_quantity
368 := l_update_rec.secondary_reservation_quantity
369 - l_update_rec.secondary_detailed_quantity; --INVCONV
370
371 IF l_max_delete_quantity > l_quantity_to_delete
372 THEN
373 l_update_rec.primary_reservation_quantity
374 := l_update_rec.primary_reservation_quantity - l_quantity_to_delete;
375 l_quantity_to_delete := 0;
376 l_update_rec.secondary_reservation_quantity
377 := l_update_rec.secondary_reservation_quantity
378 - l_sec_quantity_to_delete; --INVCONV
379 l_sec_quantity_to_delete := 0; --INVCONV
380 ELSE
381 l_quantity_to_delete
382 := l_quantity_to_delete - l_max_delete_quantity;
383 l_update_rec.primary_reservation_quantity
384 := l_update_rec.primary_reservation_quantity - l_max_delete_quantity;
385 l_sec_quantity_to_delete
386 := l_quantity_to_delete - l_max_delete_quantity; --INVCONV
387 l_update_rec.secondary_reservation_quantity
388 := l_update_rec.secondary_reservation_quantity
389 - l_max_delete_sec_quantity; --INVCONV
390 END IF;
391
392 IF (l_debug = 1) THEN
393 DEBUG('New Reservation Qty = '
394 || l_update_rec.primary_reservation_quantity
395 ,'CANCEL_MOVE_ORDER_LINE');
396 DEBUG('New seconday Reservation Qty = '
397 || l_update_rec.secondary_reservation_quantity
398 ,'CANCEL_MOVE_ORDER_LINE');
399 END IF;
400
401 -- update reservation
402 -- INVCONV - Make sure Qty2 are NULL if nor present
403 IF (l_update_rec.secondary_uom_code IS NULL)
404 THEN
405 l_update_rec.secondary_reservation_quantity := NULL;
406 l_update_rec.secondary_detailed_quantity := NULL;
407 END IF;
408
409 l_api_return_status := fnd_api.g_ret_sts_success;
410 inv_reservation_pub.update_reservation
411 ( p_api_version_number => 1.0
412 , p_init_msg_lst => fnd_api.g_false
413 , x_return_status => l_api_return_status
414 , x_msg_count => x_msg_count
415 , x_msg_data => x_msg_data
416 , p_original_rsv_rec => l_rsv_tbl(1)
417 , p_to_rsv_rec => l_update_rec
418 , p_original_serial_number => l_dummy_sn
419 , p_to_serial_number => l_dummy_sn
420 , p_validation_flag => fnd_api.g_true
421 );
422
423 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
424 IF (l_debug = 1) THEN
425 DEBUG('update_reservation returned success'
426 ,'CANCEL_MOVE_ORDER_LINE');
427 END IF;
428 ELSE
429 IF (l_debug = 1) THEN
430 DEBUG('Error: Update Reservations return status: '
431 || l_api_return_status, 'CANCEL_MOVE_ORDER_LINE');
432 END IF;
433
434 fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
435 fnd_msg_pub.ADD;
436 RAISE fnd_api.g_exc_unexpected_error;
437 END IF;
438 -- }
439 END LOOP;
440
441 CLOSE c_reservations;
442 -- }
443 END IF;
444
445 -- Reduce Move Order Line Qty to include only existing allocations.
446 IF l_quantity > l_quantity_detailed
447 THEN
448 l_quantity := l_quantity_detailed;
449 l_sec_quantity := l_sec_quantity_detailed; --INVCONV
450 END IF;
451
452 -- If no allocations exist, close move order line
453 IF l_quantity <= 0 OR l_quantity_detailed <= 0
454 THEN
455 l_quantity := 0;
456 l_sec_quantity := 0; -- INVCONV
457 l_line_status := 5;
458
459 IF (l_debug = 1) THEN
460 DEBUG('No allocations. Closing MO line', 'CANCEL_MOVE_ORDER_LINE');
461 END IF;
462 ELSE
463 -- {
464 IF (l_debug = 1) THEN
465 DEBUG('If the MOL Detailed quantity is more than'
466 ||' requested quantity then need to delete'
467 ,'CANCEL_MOVE_ORDER_LINE');
468 DEBUG(' extra reservations','CANCEL_MOVE_ORDER_LINE');
469 END IF;
470
471 IF l_quantity_detailed > l_quantity
472 THEN
473 -- {
474 IF (l_debug = 1) THEN
475 DEBUG('Qty detailed is greater than qty..calculating extra rsved qty'
476 ,'CANCEL_MOVE_ORDER_LINE');
477 END IF;
478
479 OPEN c_primary_rsv_qty;
480 -- HW INVCONV - Added Qty2
481 FETCH c_primary_rsv_qty INTO l_total_rsv_quantity,l_total_rsv_quantity2;
482 CLOSE c_primary_rsv_qty;
483
484 IF (l_debug =1) THEN
485 DEBUG('The total unstaged reservation for the Move Order line is '
486 || l_total_rsv_quantity,'CANCEL_MOVE_ORDER_LINE');
487 DEBUG('The total unstaged secondary reservation for the Move Order line is '
488 || l_total_rsv_quantity2,'CANCEL_MOVE_ORDER_LINE');
489 END IF;
490
491 IF (l_total_rsv_quantity > 0)
492 THEN
493 -- {
494 OPEN c_wdd_requested_qty;
495 FETCH c_wdd_requested_qty INTO l_total_wdd_req_qty, l_total_wdd_sec_req_qty;
496 CLOSE c_wdd_requested_qty;
497
498 IF (l_debug=1) THEN
499 DEBUG('The total unstaged wdd requested quantity'
500 || l_total_wdd_req_qty,'CANCEL_MOVE_ORDER_LINE');
501 END IF;
502
503 IF (l_total_wdd_req_qty >= 0)
504 THEN
505 l_extra_rsv_quantity := l_total_rsv_quantity - l_total_wdd_req_qty;
506 -- INVCONV -Added Qty2
507 l_extra_rsv_quantity := l_total_rsv_quantity - l_total_wdd_req_qty;
508 l_extra_rsv_quantity2 := l_total_rsv_quantity2 - l_total_wdd_sec_req_qty;
509 END IF;
510 IF (l_debug=1) THEN
511 DEBUG('The extra reserved quantity that needs to be deleted'
512 || l_extra_rsv_quantity,'CANCEL_MOVE_ORDER_LINE');
513 -- INVCONV - Added Qty2
514 DEBUG('The extra reserved quantity2 that needs to be deleted'
515 || l_extra_rsv_quantity2,'CANCEL_MOVE_ORDER_LINE');
516 END IF;
517 -- }
518 END IF;
519 -- }
520 END IF;
521 -- }
522 END IF;
523
524 -- query all allocations
525 OPEN c_mmtt_info;
526
527 IF (l_debug = 1) THEN
528 DEBUG('Fetching tasks', 'CANCEL_MOVE_ORDER_LINE');
529 END IF;
530
531 -- INVCONV - Added qty2
532 LOOP
533 -- {
534 FETCH c_mmtt_info INTO l_transaction_temp_id, l_primary_quantity,
535 l_secondary_quantity, l_reservation_id, l_parent_line_id;
536 EXIT WHEN c_mmtt_info%NOTFOUND;
537
538 IF (l_debug = 1) THEN
539 DEBUG('Transaction Temp ID = ' || l_transaction_temp_id
540 ,'CANCEL_MOVE_ORDER_LINE');
541 DEBUG('Primary Quantity = ' || l_primary_quantity
542 ,'CANCEL_MOVE_ORDER_LINE');
543 -- INVCONV - Added Qty2
544 DEBUG('Secondary Quantity = ' || l_secondary_quantity
545 ,'CANCEL_MOVE_ORDER_LINE');
546 DEBUG('Reservation ID = ' || l_reservation_id
547 ,'CANCEL_MOVE_ORDER_LINE');
548 DEBUG('Parent Line ID = ' || l_parent_line_id
549 ,'CANCEL_MOVE_ORDER_LINE');
550 END IF;
551
552 -- if the allocation corresponds to a reservation, we need to update
553 -- the reservation to reduce detailed_quantity and possibly
554 -- reservation quantity
555 IF l_reservation_id IS NOT NULL
556 THEN
557 -- {
558 l_rsv_rec.reservation_id := l_reservation_id;
559 l_api_return_status := fnd_api.g_ret_sts_success;
560 inv_reservation_pvt.query_reservation
561 ( p_api_version_number => 1.0
562 , p_init_msg_lst => fnd_api.g_false
563 , x_return_status => l_api_return_status
564 , x_msg_count => x_msg_count
565 , x_msg_data => x_msg_data
566 , p_query_input => l_rsv_rec
567 , x_mtl_reservation_tbl => l_rsv_tbl
568 , x_mtl_reservation_tbl_count => l_rsv_count
569 , x_error_code => l_error_code
570 );
571
572 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
573 IF (l_debug = 1) THEN
574 DEBUG('query_reservation returned success'
575 ,'CANCEL_MOVE_ORDER_LINE');
576 END IF;
577 ELSE
578 IF (l_debug = 1) THEN
579 DEBUG('Error: Query Reservations returned '
580 || l_api_return_status, 'CANCEL_MOVE_ORDER_LINE');
581 END IF;
582 fnd_message.set_name('INV','INV_QRY_RSV_FAILED');
583 fnd_msg_pub.ADD;
584 RAISE fnd_api.g_exc_unexpected_error;
585 END IF;
586
587 IF l_rsv_count <= 0
588 THEN
589 IF (l_debug = 1) THEN
590 DEBUG('Error: Query Reservations returned Reservation Count 0'
591 ,'CANCEL_MOVE_ORDER_LINE');
592 END IF;
593 fnd_message.set_name('INV','INV_NO_RSVS_FOUND');
594 fnd_msg_pub.ADD;
595 RAISE fnd_api.g_exc_unexpected_error;
596 END IF;
597
598 l_update_rec := l_rsv_tbl(1);
599
600 -- update detailed quantity
601 IF l_update_rec.detailed_quantity > l_primary_quantity
602 THEN
603 l_update_rec.detailed_quantity
604 := l_update_rec.detailed_quantity - l_primary_quantity;
605 -- INVCONV - Update Sec. Qty
606 l_update_rec.secondary_detailed_quantity
607 := l_update_rec.secondary_detailed_quantity - l_secondary_quantity;
608 ELSE
609 l_update_rec.detailed_quantity := 0;
610 -- INVCONV - Added Qty2
611 l_update_rec.secondary_detailed_quantity := 0;
612 END IF;
613
614 IF (l_debug = 1) THEN
615 DEBUG('New Detailed Qty = ' || l_update_rec.detailed_quantity
616 ,'CANCEL_MOVE_ORDER_LINE');
617 -- INVCONV - Added Qty2
618 DEBUG('New Detailed Qty1 = ' || l_update_rec.secondary_detailed_quantity
619 ,'CANCEL_MOVE_ORDER_LINE');
620 END IF;
621
622 -- if delete reservations = Yes, then update rsv quantity
623 IF l_delete_reservations = 'Y' OR l_extra_rsv_quantity > 0
624 THEN
625 -- {
626 l_update_rec.reservation_quantity := NULL;
627 -- INVCONV - Need to initialize secondary_qty
628 l_update_rec.secondary_reservation_quantity := NULL;
629
630 IF l_update_rec.primary_reservation_quantity > l_primary_quantity
631 THEN
632 l_update_rec.primary_reservation_quantity
633 := l_update_rec.primary_reservation_quantity - l_primary_quantity;
634 -- INVCONV - Added Qty2
635 l_update_rec.secondary_reservation_quantity
636 := l_update_rec.secondary_reservation_quantity - l_secondary_quantity;
637
638 IF (l_extra_rsv_quantity > 0)
639 THEN
640 l_extra_rsv_quantity := l_extra_rsv_quantity - l_primary_quantity;
641 -- INVCONV -Added Qty2
642 l_extra_rsv_quantity2 := l_extra_rsv_quantity2 - l_secondary_quantity;
643
644 IF (l_debug=1) THEN
645 DEBUG('New remaning extra rsv quantity'
646 || l_extra_rsv_quantity,'CANCEL_MOVE_ORDER_LINE');
647 -- INVCONV -Qty2
648 DEBUG('New remaning extra rsv quantity2'
649 || l_extra_rsv_quantity2,'CANCEL_MOVE_ORDER_LINE');
650 END IF;
651 END IF;
652 ELSIF (l_delete_reservations ='N'
653 AND
654 (l_update_rec.primary_reservation_quantity >= l_extra_rsv_quantity))
655 THEN
656 IF (l_debug=1) THEN
657 DEBUG('need to reduce reservation quantity only for the extra qty'
658 || l_extra_rsv_quantity,'CANCEL_MOVE_ORDER_LINE');
659 END IF;
660 l_update_rec.primary_reservation_quantity
661 := l_update_rec.primary_reservation_quantity - l_extra_rsv_quantity;
662 l_extra_rsv_quantity :=0;
663 -- INVCONV -Qty2
664 l_update_rec.secondary_reservation_quantity
665 := l_update_rec.secondary_reservation_quantity - l_extra_rsv_quantity2;
666 l_extra_rsv_quantity2 := 0;
667
668 IF (l_debug=1) THEN
669 DEBUG('Primary reserervation quantity is '
670 || l_update_rec.primary_reservation_quantity
671 ,'CANCEL_MOVE_ORDER_LINE');
672 END IF;
673 ELSE
674 IF (l_extra_rsv_quantity > 0
675 AND
676 l_extra_rsv_quantity >= l_update_rec.primary_reservation_quantity)
677 THEN
678 l_extra_rsv_quantity
679 := l_extra_rsv_quantity - l_update_rec.primary_reservation_quantity;
680 -- INVCONV -Qty2
681 l_extra_rsv_quantity2
682 := l_extra_rsv_quantity2 - l_update_rec.secondary_reservation_quantity;
683 ELSE
684 l_extra_rsv_quantity := 0;
685 -- INVCONV -Qty2
686 l_extra_rsv_quantity2 := 0;
687 END IF;
688
689 IF (l_debug=1) THEN
690 DEBUG('Extra rsv quantity is ' || l_extra_rsv_quantity
691 ,'CANCEL_MOVE_ORDER_LINE');
692 DEBUG('Primary reservation quantity'
693 || l_update_rec.primary_reservation_quantity
694 ,'CANCEL_MOVE_ORDER_LINE');
695 -- INVCONV -Qty2
696 DEBUG('Extra rsv quantity2 is '
697 || l_extra_rsv_quantity2,'CANCEL_MOVE_ORDER_LINE');
698 DEBUG('Secondary reservation quantity'
699 || l_update_rec.secondary_reservation_quantity
700 ,'CANCEL_MOVE_ORDER_LINE');
701 END IF;
702
703 l_update_rec.primary_reservation_quantity := 0;
704 -- INVCONV -Qty2
705 l_update_rec.secondary_reservation_quantity := 0;
706 END IF; -- rsv qty > task qty
707
708 IF (l_debug = 1) THEN
709 DEBUG('New rsv qty = '
710 || l_update_rec.primary_reservation_quantity
711 ,'Cancel_Move_Order_Line');
712 DEBUG('New sec rsv qty = '
713 || l_update_rec.secondary_reservation_quantity
714 ,'Cancel_Move_Order_Line');
715 END IF;
716 -- }
717 END IF; -- delete reservations
718
719 -- INVCONV - Make sure Qty2 are NULL if nor present
720 IF ( l_update_rec.secondary_uom_code IS NULL )
721 THEN
722 l_update_rec.secondary_reservation_quantity := NULL;
723 l_update_rec.secondary_detailed_quantity := NULL;
724 END IF;
725
726 -- update reservations
727 l_api_return_status := fnd_api.g_ret_sts_success;
728 inv_reservation_pub.update_reservation
729 ( p_api_version_number => 1.0
730 , p_init_msg_lst => fnd_api.g_false
731 , x_return_status => l_api_return_status
732 , x_msg_count => x_msg_count
733 , x_msg_data => x_msg_data
734 , p_original_rsv_rec => l_rsv_tbl(1)
735 , p_to_rsv_rec => l_update_rec
736 , p_original_serial_number => l_dummy_sn
737 , p_to_serial_number => l_dummy_sn
738 , p_validation_flag => fnd_api.g_true
739 , p_over_reservation_flag => 2 -- Bug 5158514
740 );
741
742 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
743 IF (l_debug = 1) THEN
744 DEBUG('update_reservation returned success'
745 ,'CANCEL_MOVE_ORDER_LINE');
746 END IF;
747 ELSE
748 IF (l_debug = 1) THEN
749 DEBUG('Error: Update Reservations returned '
750 || l_api_return_status, 'CANCEL_MOVE_ORDER_LINE');
751 END IF;
752
753 fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
754 fnd_msg_pub.ADD;
755 RAISE fnd_api.g_exc_unexpected_error;
756 END IF;
757 -- }
758 END IF; -- reservation id is not null
759
760 -- Check if WMS is installed
761 IF l_is_wms_org THEN
762 -- {
763 OPEN c_dispatched_task;
764 FETCH c_dispatched_task INTO l_task_status;
765
766 IF c_dispatched_task%NOTFOUND OR l_task_status NOT IN(4, 9)
767 THEN
768 IF (l_debug = 1) THEN
769 DEBUG('Task is not yet Loaded or is not Active'
770 ,'CANCEL_MOVE_ORDER_LINE');
771 END IF;
772
773 l_deleted_quantity := l_deleted_quantity + l_primary_quantity;
774 -- INVCONV -Qty2
775 l_sec_deleted_quantity := l_sec_deleted_quantity + l_secondary_quantity;
776
777 l_api_return_status := fnd_api.g_ret_sts_success;
778 inv_trx_util_pub.delete_transaction
779 ( x_return_status => l_api_return_status
780 , x_msg_data => x_msg_data
781 , x_msg_count => x_msg_count
782 , p_transaction_temp_id => l_transaction_temp_id
783 );
784
785 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
786 IF (l_debug = 1) THEN
787 DEBUG('delete_transaction returned success'
788 ,'CANCEL_MOVE_ORDER_LINE');
789 END IF;
790 ELSE
791 IF (l_debug = 1) THEN
792 DEBUG('Error: delete_transaction return status: '
793 || l_api_return_status
794 ,'CANCEL_MOVE_ORDER_LINE');
795 END IF;
796 fnd_message.set_name('INV','INV_DELETE_TXN_FAILED');
797 fnd_message.set_token('TXN_TEMP_ID',l_transaction_temp_id);
798 fnd_msg_pub.ADD;
799 RAISE fnd_api.g_exc_error;
800 END IF;
801 ELSE
802 IF (l_debug = 1) THEN
803 DEBUG('Task is Loaded or Active - Not deleting the Allocation'
804 ,'CANCEL_MOVE_ORDER_LINE');
805 END IF;
806 END IF; -- task is not dispatched
807
808 CLOSE c_dispatched_task;
809 -- }
810 END IF; -- wms installed
811 -- }
812 END LOOP; -- loop through each task
813
814 CLOSE c_mmtt_info;
815
816 -- NULL out the Reservation ID in MMTT.
817 -- This delinks the Reservations from Allocations.
818 UPDATE mtl_material_transactions_temp
819 SET reservation_id = NULL
820 WHERE move_order_line_id = p_line_id;
821
822 -- If all of the quantity for the move order line was deleted,
823 -- close the move order line
824 IF l_deleted_quantity >= l_quantity
825 THEN
826 l_quantity := 0;
827 l_line_status := 5;
828 -- INVCONV -Qty2
829 l_sec_quantity := 0;
830 ELSE
831 l_quantity := l_quantity - l_deleted_quantity;
832 l_line_status := 9;
833 -- INVCONV -Qty2
834 l_sec_quantity := l_sec_quantity - l_sec_deleted_quantity;
835 END IF;
836
837 IF (l_debug = 1) THEN
838 DEBUG('MO Line - New Status = '
839 || l_line_status, 'CANCEL_MOVE_ORDER_LINE');
840 DEBUG('MO Line - New Quantity = '
841 || l_quantity, 'CANCEL_MOVE_ORDER_LINE');
842 DEBUG('MO Line - New Secondary Quantity = '
843 || l_sec_quantity, 'CANCEL_MOVE_ORDER_LINE');
844 END IF;
845
846 -- Update line status, quantity, and required_quantity
847 -- INVCONV -Qty2
848 UPDATE mtl_txn_request_lines
849 SET quantity = l_quantity
850 , required_quantity = 0
851 , line_status = l_line_status
852 , secondary_quantity = l_sec_quantity
853 , secondary_required_quantity
854 = decode(l_sec_quantity, NULL, NULL, 0)
855 , status_date =sysdate --BUG 6932648
856 WHERE line_id = p_line_id;
857
858 --
859 -- ER3969328: CI project. The following changes made for CI project ER.
860 -- Check first if this API was called with a value passed to p_delete_alloc.
861 -- If this is null pick the org level parameter setting. Check if the option
862 -- 'Auto Delete Allocations at Move Order Cancel' is set Yes. If Yes then
863 -- the allocations should be deleted and the move order line closed.
864 -- The variable g_auto_del_alloc is cached.
865 --
866
867 IF p_delete_alloc IS NULL
868 THEN
869 IF g_auto_del_alloc is null
870 THEN
871 select NVL(auto_del_alloc_flag,'N')
872 into g_auto_del_alloc
873 from mtl_parameters
874 where organization_id = l_org_id;
875 END IF;
876 l_alloc_flag := g_auto_del_alloc;
877 ELSE
878 l_alloc_flag := p_delete_alloc;
879 END IF;
880
881 --
882 -- ER3969328: CI project.Check the total number of allocations
883 -- for this move_order_line_id
884 --
885 select count(*)
886 into l_count_alloc
887 from mtl_material_transactions_temp
888 where move_order_line_id = p_line_id;
889
890 --
891 -- ER3969328: CI project.Only if this flag is set to 'Y'
892 -- will the delete allocations API be called.
893 --
894 IF (l_alloc_flag = 'Y' and l_is_wms_org = FALSE)
895 THEN
896 -- {
897 for c_mmtt_rec in c_mmtt
898 LOOP
899 l_count := l_count + 1; --counter
900 l_api_return_status := fnd_api.g_ret_sts_success;
901 inv_mo_line_detail_util.delete_allocations
902 ( x_return_status => l_api_return_status
903 , x_msg_data => x_msg_count
904 , x_msg_count => x_msg_data
905 , p_mo_line_id => p_line_id
906 , p_transaction_temp_id => c_mmtt_rec.transaction_temp_id
907 );
908 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
909 IF (l_debug = 1) THEN
910 DEBUG('delete_allocations returned success'
911 ,'Cancel_Move_Order_Line');
912 END IF;
913 l_flag := TRUE;
914 ELSE
915 IF (l_debug = 1) THEN
916 DEBUG('Error: delete_allocations return status: '
917 || l_api_return_status
918 ,'Cancel_Move_Order_Line');
919 END IF;
920 l_flag := FALSE;
921 RAISE fnd_api.g_exc_error;
922 END IF;
923 END LOOP;
924
925 --
926 -- ER3969328: CI project.After deleting allocations successfully
927 -- close the move order line.
928 --
929 IF (l_flag and l_count = l_count_alloc)
930 THEN
931 update mtl_txn_request_lines
932 set line_status = 5
933 , status_date =sysdate --BUG 6932648
934 where line_id = p_line_id;
935 END IF;
936 -- }
937 END IF; -- g_auto_del_alloc = 'Y' and l_is_wms_org = FALSE
938 -- }
939 ELSE -- MO type putaway
940 -- {
941 -- {{
942 -- Cancel sales/internal order where a xdock peg
943 -- exists, with material in Receiving. The operation
944 -- plan should get cancelled, and material should not
945 -- be staged }}
946 --
947 -- Lock the putaway move order line
948 BEGIN
949 SELECT 'x'
950 INTO l_dummy
951 FROM mtl_txn_request_lines mtrl
952 WHERE mtrl.line_id = p_line_id
953 FOR UPDATE NOWAIT;
954 EXCEPTION
955 WHEN record_locked THEN
956 DEBUG('Unable to lock the putaway MO line'
957 ,'CANCEL_MOVE_ORDER_LINE');
958 fnd_message.set_name('WMS', 'INV_PUTAWAY_MOL_LOCK_FAIL');
959 fnd_msg_pub.ADD;
960 RAISE fnd_api.g_exc_error;
961 END;
962
963 wms_xdock_utils_pvt.g_demand_triggered := TRUE;
964
965 l_api_return_status := fnd_api.g_ret_sts_success;
966 inv_rcv_integration_pvt.call_atf_api
967 ( x_return_status => l_api_return_status
968 , x_msg_data => x_msg_data
969 , x_msg_count => x_msg_count
970 , x_error_code => l_error_code
971 , p_source_task_id => NULL
972 , p_activity_type_id => 1
973 , p_mol_id => p_line_id
974 , p_atf_api_name => inv_rcv_integration_pvt.g_atf_api_cancel
975 , p_mmtt_error_code => 'INV_XDK_DEMAND_CHG'
976 , p_mmtt_error_explanation => NULL
977 , p_retain_mmtt => 'Y'
978 );
979
980 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
981
982 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
983 IF (l_debug = 1)
984 THEN
985 DEBUG('inv_rcv_integration_pvt.call_atf_api returned success'
986 ,'CANCEL_MOVE_ORDER_LINE');
987 END IF;
988 ELSE
989 IF (l_debug = 1)
990 THEN
991 DEBUG('inv_rcv_integration_pvt.call_atf_api returned an error status: '
992 || l_api_return_status, 'CANCEL_MOVE_ORDER_LINE');
993 DEBUG('l_error_code: ' || l_error_code
994 ,'CANCEL_MOVE_ORDER_LINE');
995 END IF;
996
997 IF l_api_return_status = fnd_api.g_ret_sts_error
998 THEN
999 RAISE fnd_api.g_exc_error;
1000 ELSE
1001 RAISE fnd_api.g_exc_unexpected_error;
1002 END IF;
1003 END IF;
1004 -- }
1005 END IF;
1006 -- }
1007 ELSE -- line ID is NULL
1008 -- {
1009 IF p_delivery_detail_id IS NULL
1010 THEN
1011 IF (l_debug = 1) THEN
1012 DEBUG('Both p_line_id and p_delivery_detail_id are null!'
1013 ,'CANCEL_MOVE_ORDER_LINE');
1014 RAISE fnd_api.g_exc_unexpected_error;
1015 END IF;
1016 END IF;
1017 -- {{
1018 -- Cancellation of sales/internal order where a xdock peg
1019 -- exists, before material is received. The reservations
1020 -- should get deleted }}
1021 --
1022 l_xdock_rsv_tbl.DELETE;
1023 BEGIN
1024 OPEN c_xdock_rsv (p_delivery_detail_id);
1025 FETCH c_xdock_rsv BULK COLLECT INTO l_xdock_rsv_tbl;
1026 CLOSE c_xdock_rsv;
1027 EXCEPTION
1028 WHEN record_locked THEN
1029 IF (l_debug = 1) THEN
1030 DEBUG('Unable to lock xdock rsv record(s)'
1031 ,'CANCEL_MOVE_ORDER_LINE');
1032 END IF;
1033 IF c_xdock_rsv%ISOPEN
1034 THEN
1035 CLOSE c_xdock_rsv;
1036 END IF;
1037 fnd_message.set_name('WMS', 'INV_RSV_LOCK_FAIL');
1038 fnd_msg_pub.ADD;
1039 RAISE fnd_api.g_exc_error;
1040 END;
1041
1042 wms_xdock_utils_pvt.g_demand_triggered := TRUE;
1043
1044 l_rsv_index := l_xdock_rsv_tbl.FIRST;
1045 LOOP
1046 -- {
1047 IF (l_xdock_rsv_tbl.COUNT = 0) THEN
1048 IF (l_debug = 1) THEN
1049 DEBUG('No xdock rsv records to process','CANCEL_MOVE_ORDER_LINE');
1050 END IF;
1051 EXIT;
1052 END IF;
1053
1054 l_rsv_rec.reservation_id := l_xdock_rsv_tbl(l_rsv_index);
1055 IF (l_debug = 1) THEN
1056 DEBUG('About to delete reservation ID: '
1057 || to_char(l_rsv_rec.reservation_id)
1058 ,'CANCEL_MOVE_ORDER_LINE');
1059 END IF;
1060
1061 l_api_return_status := fnd_api.g_ret_sts_success;
1062 inv_reservation_pub.delete_reservation
1063 ( p_api_version_number => 1.0
1064 , p_init_msg_lst => fnd_api.g_false
1065 , x_return_status => l_api_return_status
1066 , x_msg_count => x_msg_count
1067 , x_msg_data => x_msg_data
1068 , p_rsv_rec => l_rsv_rec
1069 , p_serial_number => l_serial_tbl
1070 );
1071
1072 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
1073 IF (l_debug = 1)
1074 THEN
1075 DEBUG('inv_reservation_pub.delete_reservation returned success'
1076 ,'CANCEL_MOVE_ORDER_LINE');
1077 END IF;
1078 ELSE
1079 IF (l_debug = 1)
1080 THEN
1081 DEBUG('inv_reservation_pub.delete_reservation returned an error status: '
1082 || l_api_return_status, 'CANCEL_MOVE_ORDER_LINE');
1083 END IF;
1084
1085 IF l_api_return_status = fnd_api.g_ret_sts_error
1086 THEN
1087 RAISE fnd_api.g_exc_error;
1088 ELSE
1089 RAISE fnd_api.g_exc_unexpected_error;
1090 END IF;
1091 END IF;
1092
1093 EXIT WHEN l_rsv_index = l_xdock_rsv_tbl.LAST;
1094 l_rsv_index := l_xdock_rsv_tbl.NEXT(l_rsv_index);
1095 -- }
1096 END LOOP;
1097
1098 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
1099
1100 l_xdock_rsv_tbl.DELETE;
1101 -- }
1102 END IF;
1103
1104 -- {{
1105 -- END cancel_move_order_line }}
1106 --
1107 EXCEPTION
1108 WHEN fnd_api.g_exc_error THEN
1109 ROLLBACK TO cancelmo_sp;
1110 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
1111 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1112 p_data => x_msg_data,
1113 p_encoded => 'F');
1114 x_return_status := fnd_api.g_ret_sts_error;
1115
1116 IF (l_debug = 1) THEN
1117 DEBUG('Return status = ' || x_return_status ||
1118 ', x_msg_data = ' || x_msg_data
1119 ,'CANCEL_MOVE_ORDER_LINE');
1120 END IF;
1121
1122 WHEN fnd_api.g_exc_unexpected_error THEN
1123 ROLLBACK TO cancelmo_sp;
1124 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
1125 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1126 p_data => x_msg_data,
1127 p_encoded => 'F');
1128 x_return_status := fnd_api.g_ret_sts_unexp_error;
1129
1130 IF (l_debug = 1) THEN
1131 DEBUG('Return status = ' || x_return_status ||
1132 ', x_msg_data = ' || x_msg_data
1133 ,'CANCEL_MOVE_ORDER_LINE');
1134 END IF;
1135
1136 WHEN OTHERS THEN
1137 ROLLBACK TO cancelmo_sp;
1138 x_return_status := fnd_api.g_ret_sts_unexp_error;
1139
1140 --ER: CI project. Setting the fields x_msg_count and x_msg_data correctly.
1141 x_msg_count := SQLCODE;
1142 x_msg_data := SQLERRM;
1143
1144 IF (l_debug = 1) THEN
1145 DEBUG('Other error: Code = ' || SQLCODE || ' : Msg = ' || SQLERRM
1146 ,'CANCEL_MOVE_ORDER_LINE');
1147 END IF;
1148
1149 END cancel_move_order_line;
1150
1151
1152 --Procedure
1153 -- Reduce_Move_Order_Quantity
1154 --Description
1155 -- This procedure is called from Shipping when the quantity on a
1156 -- sales order line is reduced, leading to the quantity on a delivery
1157 -- detail being reduced. This procedure reduces the required_quantity
1158 -- column on the move order line by p_reduction_quantity. The required
1159 -- quantity is the quantity needed by shipping to fulfill the sales order.
1160 -- Any quantity transacted for this move order line in excess of the
1161 -- required_quantity will be moved to staging, but will not be
1162 -- reserved or shipped to the customer. Since the
1163 -- sales order line quantity has been reduced, the reservation quantity
1164 -- for the sales order should also be reduced. Some reservations are
1165 -- reduced here, and some are reduced in Finalize_Pick_Confirm
1166 -- (INVVTROB.pls).
1167 -- If WMS is installed, undispatched tasks may be deleted, since these
1168 -- tasks are no longer necessary.
1169 -- Parameters
1170 -- p_line_id: The move order line id to be reduced
1171 -- p_reduction_quantity: How much to reduce the required
1172 -- quantity by, in the UOM of the move order line
1173 -- p_txn_source_line_Id: The sales order line id. If this
1174 -- parameter is not passed in, we get it from the delivery detail.
1175 -- p_delivery_detail_id: Added for Planned Crossdocking in Release 12.0
1176 -- Shipping passes in delivery detail ID if the WDD record is pegged
1177 -- to a supply source (via reservations) and the supply has not been
1178 -- received. After receipt reductions are not allowed so this API
1179 -- should not be called to reduce qty on a putaway move order line
1180
1181 PROCEDURE reduce_move_order_quantity
1182 ( x_return_status OUT NOCOPY VARCHAR2
1183 , x_msg_count OUT NOCOPY NUMBER
1184 , x_msg_data OUT NOCOPY VARCHAR2
1185 , p_line_id IN NUMBER
1186 , p_reduction_quantity IN NUMBER
1187 , p_sec_reduction_quantity IN NUMBER DEFAULT NULL
1188 , p_txn_source_line_id IN NUMBER DEFAULT NULL
1189 , p_delivery_detail_id IN NUMBER DEFAULT NULL -- planned crossdocking project
1190 ) IS
1191 l_quantity NUMBER;
1192 l_quantity_detailed NUMBER;
1193 l_organization_id NUMBER;
1194 l_transaction_temp_id NUMBER;
1195 l_task_qty NUMBER;
1196 l_return_status VARCHAR2(1);
1197 l_deleted_quantity NUMBER;
1198 l_reservation_id NUMBER;
1199 l_primary_quantity NUMBER;
1200 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1201 l_rsv_rec2 inv_reservation_global.mtl_reservation_rec_type;
1202 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
1203 l_update_rec inv_reservation_global.mtl_reservation_rec_type;
1204 l_serial_tbl inv_reservation_global.serial_number_tbl_type;
1205 l_rsv_count NUMBER;
1206 l_rsv_index NUMBER;
1207 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
1208 l_quantity_to_delete NUMBER;
1209 l_sec_quantity_to_delete NUMBER; --INVCONV
1210 l_sec_deleted_quantity NUMBER; --INVCONV
1211 l_sec_quantity NUMBER; --INVCONV
1212 l_sec_quantity_detailed NUMBER; --INVCONV
1213 l_sec_reduction_quantity NUMBER; --INVCONV
1214 l_sec_qty NUMBER; --INVCONV
1215 l_max_delete_sec_quantity NUMBER; --INVCONV
1216 l_sec_qty_to_delete NUMBER; --INVCONV
1217 l_max_delete_quantity NUMBER;
1218 l_txn_source_line_id NUMBER;
1219 l_reduction_quantity NUMBER;
1220 l_error_code NUMBER;
1221 l_mo_uom_code VARCHAR2(3);
1222 l_primary_uom_code VARCHAR2(3);
1223 l_inventory_item_id NUMBER;
1224 l_prim_quantity_to_delete NUMBER;
1225 l_move_order_type NUMBER;
1226 l_reduc_qty_conv NUMBER;
1227 l_conv_rate NUMBER;
1228 l_sec_reduc_qty_conv NUMBER;
1229 l_prim_qty_conv NUMBER;
1230
1231 l_debug NUMBER;
1232 l_required_quantity NUMBER; --Bug#5095840.
1233 l_sec_required_quantity NUMBER; --Bug#5095840.
1234
1235 TYPE xdock_rsv_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1236 l_xdock_rsv_tbl xdock_rsv_tbl;
1237
1238 record_locked EXCEPTION;
1239 PRAGMA EXCEPTION_INIT (record_locked, -54);
1240
1241 /*Bug#5095840. In the below cursor, the columns, 'Required_quantity' and
1242 'secnodary_required_quantity' are also selected.*/
1243 CURSOR c_line_info IS
1244 SELECT quantity
1245 ,NVL(required_quantity, quantity)
1246 , NVL(quantity_detailed, 0)
1247 , secondary_quantity
1248 , NVL(secondary_required_quantity, secondary_quantity) --INVCONV
1249 , secondary_quantity_detailed --INVCONV
1250 , organization_id
1251 , inventory_item_id
1252 , uom_code
1253 , txn_source_line_id
1254 FROM mtl_txn_request_lines
1255 WHERE line_id = p_line_id
1256 FOR UPDATE;
1257
1258 CURSOR c_primary_uom IS
1259 SELECT primary_uom_code
1260 FROM mtl_system_items
1261 WHERE organization_id = l_organization_id
1262 AND inventory_item_id = l_inventory_item_id;
1263
1264 /*Bug#5095840. Added the below cursor to fetch the source line id
1265 if 'p_txn_source_line_id' is passed as NULL.*/
1266 CURSOR c_txn_source_line IS
1267 SELECT source_line_id
1268 FROM wsh_delivery_details
1269 WHERE move_order_line_id IS NOT NULL
1270 AND move_order_line_id = p_line_id
1271 AND released_status = 'S';
1272
1273 CURSOR c_reservations IS
1274 SELECT mr.reservation_id
1275 FROM mtl_reservations mr
1276 WHERE mr.demand_source_type_id IN (2,8)
1277 AND NVL(mr.staged_flag,'N') <> 'Y'
1278 AND mr.demand_source_line_id = l_txn_source_line_id
1279 AND mr.demand_source_line_detail IS NULL
1280 AND mr.primary_reservation_quantity > NVL(mr.detailed_quantity, 0);
1281
1282 CURSOR c_undispatched_tasks IS
1283 SELECT mmtt.transaction_temp_id
1284 , ABS(mmtt.transaction_quantity)
1285 , ABS(mmtt.secondary_transaction_quantity)
1286 , ABS(mmtt.primary_quantity)
1287 , mmtt.reservation_id
1288 FROM mtl_material_transactions_temp mmtt
1289 WHERE mmtt.move_order_line_id = p_line_id
1290 AND NOT EXISTS( SELECT 'Y'
1291 FROM wms_dispatched_tasks wdt
1292 WHERE wdt.transaction_temp_id
1293 = nvl( mmtt.parent_line_id
1294 , mmtt.transaction_temp_id
1295 )
1296 )
1297 ORDER BY mmtt.transaction_quantity ASC;
1298
1299 CURSOR c_xdock_rsv
1300 ( p_wdd_id IN NUMBER
1301 ) IS
1302 SELECT mr.reservation_id
1303 FROM mtl_reservations mr
1304 WHERE mr.demand_source_line_detail = p_wdd_id
1305 AND mr.demand_source_type_id IN (2,8)
1306 AND NVL(mr.crossdock_flag,'N') = 'Y'
1307 FOR UPDATE NOWAIT;
1308
1309 CURSOR c_lock_wdd
1310 ( p_wdd_id IN NUMBER
1311 ) IS
1312 SELECT wdd.delivery_detail_id
1313 , wdd.requested_quantity
1314 , wdd.requested_quantity_uom
1315 , wdd.requested_quantity2
1316 , wdd.requested_quantity_uom2
1317 FROM wsh_delivery_details wdd
1318 WHERE wdd.delivery_detail_id = p_wdd_id
1319 FOR UPDATE NOWAIT;
1320
1321 l_wdd_rec c_lock_wdd%ROWTYPE;
1322
1323 BEGIN
1324 x_return_status := fnd_api.g_ret_sts_success;
1325 SAVEPOINT reducemo_sp;
1326
1327 -- {{
1328 -- BEGIN reduce_move_order_quantity }}
1329 --
1330 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1331
1332 IF (l_debug = 1) THEN
1333 DEBUG('Entered with parameters: '
1334 || ' p_line_id: ' || to_char(p_line_id)
1335 || ', p_reduction_quantity: ' || to_char(p_reduction_quantity)
1336 || ', p_sec_reduction_quantity: ' || to_char(p_sec_reduction_quantity)
1337 || ', p_txn_source_line_id: ' || to_char(p_txn_source_line_id)
1338 || ', p_delivery_detail_id: ' || to_char(p_delivery_detail_id)
1339 ,'Reduce_Move_Order_Quantity');
1340 END IF;
1341
1342 IF p_reduction_quantity <= 0 THEN
1343 RETURN;
1344 END IF;
1345
1346 l_deleted_quantity := 0;
1347 l_sec_deleted_quantity := 0; --INVCONV
1348 l_reduction_quantity := p_reduction_quantity;
1349 l_sec_reduction_quantity := p_sec_reduction_quantity; --INVCONV
1350
1351 IF p_line_id IS NOT NULL
1352 THEN
1353 -- {
1354 -- {{
1355 -- Verify that reduce API works as before for non-putaway
1356 -- (sales/internal order) move order lines }}
1357 --
1358 BEGIN
1359 SELECT NVL(mtrh.move_order_type,0)
1360 INTO l_move_order_type
1361 FROM mtl_txn_request_lines mtrl
1362 , mtl_txn_request_headers mtrh
1363 WHERE mtrl.line_id = p_line_id
1364 AND mtrh.header_id = mtrl.header_id;
1365
1366 IF (l_debug = 1) THEN
1367 DEBUG('Move order type: ' || to_char(l_move_order_type),'Reduce_Move_Order_Quantity');
1368 END IF;
1369
1370 IF l_move_order_type <= 0
1371 THEN
1372 RAISE fnd_api.g_exc_unexpected_error;
1373 END IF;
1374
1375 EXCEPTION
1376 WHEN OTHERS THEN
1377 IF (l_debug = 1) THEN
1378 DEBUG('Unxexpected error querying MO type: '|| sqlerrm ,'Reduce_Move_Order_Quantity');
1379 END IF;
1380 RAISE fnd_api.g_exc_unexpected_error;
1381 END;
1382
1383 -- {{
1384 -- Reduce API must return an error if called after
1385 -- pegged material is in Receiving }}
1386 --
1387 IF l_move_order_type = INV_GLOBALS.G_MOVE_ORDER_PUT_AWAY
1388 THEN
1389 IF (l_debug = 1) THEN
1390 DEBUG('Cannot reduce putaway MO line qty','Reduce_Move_Order_Quantity');
1391 END IF;
1392 fnd_message.set_name('WMS', 'INV_MOL_PUTAWAY_QTY_NOCHG');
1393 fnd_msg_pub.ADD;
1394 RAISE fnd_api.g_exc_error;
1395 END IF;
1396
1397 -- query mo line info
1398 OPEN c_line_info;
1399 FETCH c_line_info
1400 INTO l_quantity
1401 , l_required_quantity /*Bug#5095840*/
1402 , l_quantity_detailed
1403 , l_sec_quantity
1404 , l_sec_required_quantity /*Bug#5095840*/
1405 , l_sec_quantity_detailed
1406 , l_organization_id
1407 , l_inventory_item_id
1408 , l_mo_uom_code
1409 , l_txn_source_line_id; --INVCONV
1410
1411 IF (l_debug = 1) THEN
1412 DEBUG('Move order line details are:', 'Reduce_Move_Order_Quantity');
1413 DEBUG('l_quantity:'||l_quantity||' l_required_quantity:'||l_required_quantity, 'Reduce_Move_Order_Quantity');
1414 DEBUG('l_quantity_detailed : ' || l_quantity_detailed, 'Reduce_Move_Order_Quantity');
1415 DEBUG('l_organization_id : ' || l_organization_id, 'Reduce_Move_Order_Quantity');
1416 DEBUG('l_inventory_item_id : ' || l_inventory_item_id, 'Reduce_Move_Order_Quantity');
1417 DEBUG('l_mo_uom_code : ' || l_mo_uom_code, 'Reduce_Move_Order_Quantity');
1418 DEBUG('l_txn_source_line_id : ' || l_txn_source_line_id, 'Reduce_Move_Order_Quantity');
1419 END IF;
1420
1421 IF c_line_info%NOTFOUND
1422 THEN
1423 IF (l_debug = 1) THEN
1424 DEBUG('Move order line not found', 'Reduce_Move_Order_Quantity');
1425 END IF;
1426 CLOSE c_line_info;
1427 RAISE fnd_api.g_exc_error;
1428 END IF;
1429
1430 IF c_line_info%ISOPEN
1431 THEN
1432 CLOSE c_line_info;
1433 END IF;
1434
1435 -- Call Cancel MO Line when Reduction Quantity > Quantity
1436 /*Bug#5095840. In the below IF statement, added the comparision of
1437 l_reduction_quantity with l_required_quantity as well.*/
1438 IF l_reduction_quantity >= l_quantity OR
1439 l_reduction_quantity >= l_required_quantity
1440 THEN
1441 cancel_move_order_line
1442 ( x_return_status => l_return_status
1443 , x_msg_count => x_msg_count
1444 , x_msg_data => x_msg_data
1445 , p_line_id => p_line_id
1446 , p_delete_reservations => 'Y'
1447 , p_txn_source_line_id => p_txn_source_line_id
1448 );
1449
1450 IF l_return_status = fnd_api.g_ret_sts_error THEN
1451 RAISE fnd_api.g_exc_error;
1452 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1453 RAISE fnd_api.g_exc_unexpected_error;
1454 END IF;
1455 END IF;
1456
1457 --
1458 -- If not all of the move order quantity is detailed, we must reduce
1459 -- reservation quantity by the same amount that we reduce move order
1460 -- quantity. The reservations which are eligible to be updated at this
1461 -- point are the reservations where rsv quantity > detailed quantity.
1462 --
1463 IF l_quantity > l_quantity_detailed
1464 THEN
1465 -- {
1466 l_quantity_to_delete := l_quantity - l_quantity_detailed;
1467 l_sec_quantity_to_delete := l_sec_quantity - l_sec_quantity_detailed; --INVCONV
1468 IF l_reduction_quantity < l_quantity_to_delete
1469 THEN
1470 l_quantity_to_delete := l_reduction_quantity;
1471 l_sec_quantity_to_delete := l_sec_reduction_quantity; --INVCONV
1472 l_reduction_quantity := 0;
1473 l_sec_reduction_quantity := 0; --INVCONV
1474 ELSE
1475 l_reduction_quantity := l_reduction_quantity - l_quantity_to_delete;
1476 l_sec_reduction_quantity := l_sec_reduction_quantity - l_sec_quantity_to_delete; --INVCONV
1477 END IF;
1478
1479 IF (l_debug = 1) THEN
1480 DEBUG('l_reduction_quantity : ' || l_reduction_quantity, 'Reduce_Move_Order_Quantity');
1481 DEBUG('l_quantity_to_delete : ' || l_quantity_to_delete, 'Reduce_Move_Order_Quantity');
1482 END IF;
1483 --Bug 9212270: No need to update l_quantity here
1484 --l_quantity := l_quantity - l_quantity_to_delete; /*Bug#5095840*/
1485 -- find primary qty to delete
1486 OPEN c_primary_uom;
1487 FETCH c_primary_uom INTO l_primary_uom_code;
1488 IF c_primary_uom%NOTFOUND THEN
1489 IF (l_debug = 1) THEN
1490 DEBUG('Item not found', 'Reduce_Move_Order_Quantity');
1491 END IF;
1492 CLOSE c_primary_uom;
1493 RAISE fnd_api.g_exc_unexpected_error;
1494 END IF;
1495 CLOSE c_primary_uom;
1496
1497 IF l_primary_uom_code <> l_mo_uom_code
1498 THEN
1499 l_prim_quantity_to_delete := inv_convert.inv_um_convert
1500 ( l_inventory_item_id
1501 , NULL
1502 , l_quantity_to_delete
1503 , l_mo_uom_code
1504 , l_primary_uom_code
1505 , NULL
1506 , NULL
1507 );
1508 IF (l_prim_quantity_to_delete = -99999)
1509 THEN
1510 IF (l_debug = 1) THEN
1511 DEBUG('Cannot convert uom to primary uom', 'Reduce_Move_Order_Quantity');
1512 END IF;
1513
1514 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
1515 fnd_message.set_token('UOM', l_primary_uom_code);
1516 fnd_message.set_token('ROUTINE', 'Reduce Move Order Quantity');
1517 fnd_msg_pub.ADD;
1518 RAISE fnd_api.g_exc_unexpected_error;
1519 END IF;
1520 ELSE
1521 l_prim_quantity_to_delete := l_quantity_to_delete;
1522 END IF;
1523
1524 l_sec_qty_to_delete := l_sec_quantity_to_delete; --INVCONV
1525
1526 IF (l_debug = 1) THEN
1527 DEBUG('l_prim_quantity_to_delete: ' || l_prim_quantity_to_delete, 'Reduce_Move_Order_Quantity');
1528 DEBUG('l_txn_source_line_id: ' || l_txn_source_line_id, 'Reduce_Move_Order_Quantity');
1529 END IF;
1530
1531 -- we query by the sales order line id. If that value is not
1532 -- passed in, we need to get it from shipping table
1533 IF p_txn_source_line_id IS NOT NULL THEN
1534 l_txn_source_line_id := p_txn_source_line_id;
1535 ELSE /*Bug#5095840. Added this ELSE part*/
1536 OPEN c_txn_source_line;
1537 FETCH c_txn_source_line INTO l_txn_source_line_id;
1538 IF c_txn_source_line%NOTFOUND THEN
1539 CLOSE c_txn_source_line;
1540 IF ( l_debug = 1) THEN
1541 DEBUG('Did Not Find Any Sales Order Line', 'Reduce_Move_Order_Quantity');
1542 END IF;
1543 RAISE No_Data_Found;
1544 END IF;
1545 CLOSE c_txn_source_line;
1546 END IF;
1547
1548 OPEN c_reservations;
1549 LOOP
1550 -- {
1551 EXIT WHEN l_prim_quantity_to_delete <= 0;
1552 FETCH c_reservations INTO l_reservation_id;
1553 EXIT WHEN c_reservations%NOTFOUND;
1554 l_rsv_rec.reservation_id := l_reservation_id;
1555
1556 -- query reservation
1557 inv_reservation_pvt.query_reservation
1558 ( p_api_version_number => 1.0
1559 , p_init_msg_lst => fnd_api.g_false
1560 , x_return_status => l_return_status
1561 , x_msg_count => x_msg_count
1562 , x_msg_data => x_msg_data
1563 , p_query_input => l_rsv_rec
1564 , x_mtl_reservation_tbl => l_rsv_tbl
1565 , x_mtl_reservation_tbl_count => l_rsv_count
1566 , x_error_code => l_error_code
1567 );
1568
1569 IF l_return_status = fnd_api.g_ret_sts_error THEN
1570 IF (l_debug = 1) THEN
1571 DEBUG('Query reservation returned error','Reduce_Move_Order_Quantity');
1572 END IF;
1573 RAISE fnd_api.g_exc_error;
1574 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
1575 THEN
1576 IF (l_debug = 1) THEN
1577 DEBUG('Query reservation returned unexpected error','Reduce_Move_Order_Quantity');
1578 END IF;
1579 RAISE fnd_api.g_exc_unexpected_error;
1580 END IF;
1581
1582 l_update_rec := l_rsv_tbl(1);
1583 l_update_rec.reservation_quantity := NULL;
1584
1585 -- INVCONV - Need to initialize secondary_qty
1586 l_update_rec.secondary_reservation_quantity := NULL;
1587 l_max_delete_quantity := l_update_rec.primary_reservation_quantity - l_update_rec.detailed_quantity;
1588 l_max_delete_sec_quantity := l_update_rec.secondary_reservation_quantity
1589 - l_update_rec.secondary_detailed_quantity; --INVCONV
1590
1591 IF (l_debug = 1) THEN
1592 DEBUG('l_max_delete_quantity::' || l_max_delete_quantity, 'Reduce_Move_Order_Quantity');
1593 END IF;
1594
1595 -- determine new reservation quantity
1596 IF l_max_delete_quantity > l_prim_quantity_to_delete
1597 THEN
1598 l_update_rec.primary_reservation_quantity := l_update_rec.primary_reservation_quantity
1599 - l_prim_quantity_to_delete;
1600 l_prim_quantity_to_delete := 0;
1601 l_sec_qty_to_delete := 0; --INVCONV
1602 l_update_rec.secondary_reservation_quantity := l_update_rec.secondary_reservation_quantity
1603 - l_sec_quantity_to_delete; --INVCONV
1604 ELSE
1605 l_prim_quantity_to_delete := l_prim_quantity_to_delete - l_max_delete_quantity;
1606 l_update_rec.primary_reservation_quantity := l_update_rec.primary_reservation_quantity
1607 - l_max_delete_quantity;
1608 l_sec_qty_to_delete := l_sec_qty_to_delete - l_max_delete_sec_quantity; --INVCONV
1609 l_update_rec.secondary_reservation_quantity := l_update_rec.secondary_reservation_quantity
1610 - l_max_delete_sec_quantity; --INVCONV
1611 END IF;
1612
1613 -- INVCONV - Make sure Qty2 are NULL if not present
1614 IF ( l_update_rec.secondary_uom_code IS NULL ) THEN
1615 l_update_rec.secondary_reservation_quantity := NULL;
1616 l_update_rec.secondary_detailed_quantity := NULL;
1617 END IF;
1618
1619 IF (l_debug = 1) THEN
1620 DEBUG('l_update_rec.primary_reservation_quantity::' || l_update_rec.primary_reservation_quantity,
1621 'Reduce_Move_Order_Quantity');
1622 DEBUG('l_update_rec.detailed_quantity::' || l_update_rec.detailed_quantity, 'Reduce_Move_Order_Quantity');
1623 END IF;
1624
1625 -- update reservation
1626 inv_reservation_pub.update_reservation
1627 ( p_api_version_number => 1.0
1628 , p_init_msg_lst => fnd_api.g_false
1629 , x_return_status => l_return_status
1630 , x_msg_count => x_msg_count
1631 , x_msg_data => x_msg_data
1632 , p_original_rsv_rec => l_rsv_tbl(1)
1633 , p_to_rsv_rec => l_update_rec
1634 , p_original_serial_number => l_dummy_sn
1635 , p_to_serial_number => l_dummy_sn
1636 , p_validation_flag => fnd_api.g_true
1637 );
1638
1639 IF l_return_status = fnd_api.g_ret_sts_error THEN
1640 IF (l_debug = 1) THEN
1641 DEBUG('Update reservation returned error','Reduce_Move_Order_Quantity');
1642 END IF;
1643 RAISE fnd_api.g_exc_error;
1644 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
1645 THEN
1646 IF (l_debug = 1) THEN
1647 DEBUG('Update reservation returned unexpected error','Reduce_Move_Order_Quantity');
1648 END IF;
1649 RAISE fnd_api.g_exc_unexpected_error;
1650 END IF;
1651 -- }
1652 END LOOP;
1653
1654 CLOSE c_reservations;
1655 -- }
1656 END IF;
1657
1658 IF l_reduction_quantity > 0
1659 THEN
1660 -- {
1661 -- Check if WMS is installed
1662 IF inv_install.adv_inv_installed(l_organization_id)
1663 THEN
1664 -- {
1665 OPEN c_undispatched_tasks;
1666 LOOP
1667 -- {
1668 EXIT WHEN l_reduction_quantity <= 0;
1669 FETCH c_undispatched_tasks
1670 INTO l_transaction_temp_id
1671 , l_task_qty
1672 , l_sec_qty
1673 , l_primary_quantity
1674 , l_reservation_id;
1675 IF (l_debug = 1) THEN
1676 DEBUG('l_task_qty: ' || l_task_qty, 'Reduce_Move_Order_Quantity');
1677 DEBUG('l_transaction_temp_id: ' || l_transaction_temp_id, 'Reduce_Move_Order_Quantity');
1678 DEBUG('l_primary_quantity: ' || l_primary_quantity, 'Reduce_Move_Order_Quantity');
1679 DEBUG('l_reservation_id: ' || l_reservation_id, 'Reduce_Move_Order_Quantity');
1680 END IF;
1681 EXIT WHEN c_undispatched_tasks%NOTFOUND;
1682
1683 IF l_task_qty > l_reduction_quantity THEN
1684 l_quantity_to_delete := l_reduction_quantity;
1685 l_sec_quantity_to_delete := l_sec_reduction_quantity; --INVCONV
1686 ELSE
1687 l_quantity_to_delete := l_task_qty;
1688 l_sec_quantity_to_delete := l_sec_qty; --INVCONV
1689 END IF;
1690
1691 l_reduction_quantity := l_reduction_quantity
1692 - l_quantity_to_delete;
1693 l_sec_reduction_quantity := l_sec_reduction_quantity
1694 - l_sec_quantity_to_delete; --INVCONV
1695
1696 IF (l_debug = 1) THEN
1697 DEBUG('calling reduce_rsv_allocation with l_reduction_quantity: ' || l_reduction_quantity, 'Reduce_Move_Order_Quantity');
1698 DEBUG('l_quantity_to_delete: ' || l_quantity_to_delete, 'Reduce_Move_Order_Quantity');
1699 DEBUG('l_transaction_temp_id: ' || l_transaction_temp_id, 'Reduce_Move_Order_Quantity');
1700 END IF;
1701 -- Removing reservation and allocation for this task
1702 reduce_rsv_allocation
1703 ( x_return_status => l_return_status
1704 , x_msg_count => x_msg_count
1705 , x_msg_data => x_msg_data
1706 , p_transaction_temp_id => l_transaction_temp_id
1707 , p_quantity_to_delete => l_quantity_to_delete
1708 , p_sec_quantity_to_delete => l_sec_quantity_to_delete
1709 );
1710
1711 l_deleted_quantity := l_deleted_quantity
1712 + l_quantity_to_delete;
1713 l_sec_deleted_quantity := l_sec_deleted_quantity
1714 + l_sec_quantity_to_delete; --INCONV
1715 -- }
1716 IF (l_debug = 1) THEN
1717 DEBUG('l_deleted_quantity: ' || l_deleted_quantity, 'Reduce_Move_Order_Quantity');
1718 DEBUG('l_reduction_quantity: ' || l_reduction_quantity, 'Reduce_Move_Order_Quantity');
1719 END IF;
1720 END LOOP; -- loop through each task
1721
1722 CLOSE c_undispatched_tasks;
1723 -- }
1724 END IF; -- wms installed
1725 -- }
1726 END IF; -- allocations exists
1727
1728 --
1729 -- No matter what happens above, we want to reduce the shipping
1730 -- quantity by the original reduction quantity. We know shipping qty
1731 -- is greater than reduction quantity, since we checked that at the
1732 -- beginning of the procedure.
1733 --
1734
1735 l_required_quantity := l_required_quantity - p_reduction_quantity; /*Bug#5095840*/
1736 --Bug 9212270: corrected computation for sec_req_qty
1737 l_sec_required_quantity := l_sec_required_quantity - p_sec_reduction_quantity; /*Bug#5095840*/
1738 l_quantity := l_quantity - l_deleted_quantity;/*Bug#5095840*/
1739 l_sec_quantity := l_sec_quantity - l_sec_deleted_quantity; --INVCONV/*Bug#5095840*/
1740
1741 --Bug 5054658
1742 --Decremented quantity_detailed in addition to quantity
1743 --and update the move order line with the decremented quantity
1744 --Bug 9212270: Commented since we do not update detailed_quantity
1745 /*IF (NVL(l_quantity_detailed, 0) > 0) THEN
1746 IF l_quantity_detailed > p_reduction_quantity THEN
1747 l_quantity_detailed := l_quantity_detailed - p_reduction_quantity;
1748 END IF;
1749 ELSE
1750 l_quantity_detailed := 0;
1751 END IF;
1752
1753 IF (NVL(l_sec_quantity_detailed, 0) > 0) THEN
1754 IF l_sec_quantity_detailed > p_sec_reduction_quantity THEN
1755 l_sec_quantity_detailed := l_sec_quantity_detailed - p_sec_reduction_quantity;
1756 END IF;
1757 ELSE
1758 l_sec_quantity_detailed := 0;
1759 END IF;*/
1760
1761 IF (l_debug = 1) THEN
1762 DEBUG(' update MTRL with quantity: ' || l_quantity ||
1763 ', quantity_detailed: ' || l_quantity_detailed ||', l_required_quantity: '||l_required_quantity
1764 , 'Reduce_Move_Order_Quantity');
1765 END IF;
1766
1767 -- Update line status, quantity, and required_quantity
1768 /*Bug#5095840. Modified the below UPDATE statement to update
1769 the 'required_quantity' and 'secondary_required_quantity' with
1770 'l_required_quantity' and 'l_sec_required_quantity' respectively
1771 rather than with NULL. Also updation of 'quantity_detailed' is
1772 commented.*/
1773 --Bug 9212270: Do not update sec_qty_det
1774 UPDATE mtl_txn_request_lines
1775 SET quantity = l_quantity
1776 , required_quantity = l_required_quantity
1777 , secondary_quantity = l_sec_quantity --INVCONV
1778 , secondary_required_quantity = l_sec_required_quantity --INVCONV
1779 -- , quantity_detailed = l_quantity_detailed
1780 --, secondary_quantity_detailed = l_sec_quantity_detailed
1781 WHERE line_id = p_line_id;
1782
1783 -- }
1784 ELSE -- MO line is null, so process xdock reservations
1785 -- {
1786 -- {{
1787 -- Reduce quantity on a sales/internal order line
1788 -- that is pegged to material not yet received
1789 -- Reservations are reduced or deleted }}
1790 --
1791 BEGIN
1792 OPEN c_lock_wdd (p_delivery_detail_id);
1793 FETCH c_lock_wdd INTO l_wdd_rec;
1794 CLOSE c_lock_wdd;
1795 EXCEPTION
1796 WHEN record_locked THEN
1797 IF (l_debug = 1)
1798 THEN
1799 DEBUG('Unable to lock WDD: ' || to_char(p_delivery_detail_id)
1800 ,'Reduce_Move_Order_Quantity');
1801 END IF;
1802 IF c_lock_wdd%ISOPEN
1803 THEN
1804 CLOSE c_lock_wdd;
1805 END IF;
1806 fnd_message.set_name('INV', 'INV_WDD_LOCK_FAIL');
1807 fnd_msg_pub.ADD;
1808 fnd_msg_pub.count_and_get
1809 ( p_count => x_msg_count
1810 , p_data => x_msg_data
1811 , p_encoded => 'F'
1812 );
1813 RAISE fnd_api.g_exc_error;
1814 END;
1815
1816 IF (l_debug = 1)
1817 THEN
1818 DEBUG('Locked WDD: ' || to_char(p_delivery_detail_id)
1819 ,'Reduce_Move_Order_Quantity');
1820 END IF;
1821
1822 --
1823 -- Lock and fetch reservations for the WDD record
1824 -- For each record:
1825 -- If rsv qty (after conv to WDD UOM) >
1826 -- remaining qty to reduce
1827 -- Reduce qty and exit
1828 -- else
1829 -- Delete rsv, decrement remaining reduction qty
1830 --
1831 l_xdock_rsv_tbl.DELETE;
1832 BEGIN
1833 OPEN c_xdock_rsv (p_delivery_detail_id);
1834 FETCH c_xdock_rsv BULK COLLECT INTO l_xdock_rsv_tbl;
1835 CLOSE c_xdock_rsv;
1836 EXCEPTION
1837 WHEN record_locked THEN
1838 DEBUG('Unable to lock xdock rsv record(s)'
1839 ,'Reduce_Move_Order_Quantity');
1840 IF c_xdock_rsv%ISOPEN
1841 THEN
1842 CLOSE c_xdock_rsv;
1843 END IF;
1844 fnd_message.set_name('WMS', 'INV_RSV_LOCK_FAIL');
1845 fnd_msg_pub.ADD;
1846 RAISE fnd_api.g_exc_error;
1847 END;
1848
1849 wms_xdock_utils_pvt.g_demand_triggered := TRUE;
1850
1851 l_rsv_index := l_xdock_rsv_tbl.FIRST;
1852 LOOP
1853 -- {
1854 IF (l_xdock_rsv_tbl.COUNT = 0) THEN
1855 IF (l_debug = 1) THEN
1856 DEBUG('No xdock rsv records to process'
1857 ,'Reduce_Move_Order_Quantity');
1858 END IF;
1859 EXIT;
1860 END IF;
1861
1862 EXIT WHEN l_reduction_quantity <= 0;
1863
1864 l_rsv_rec.reservation_id := l_xdock_rsv_tbl(l_rsv_index);
1865
1866 IF (l_debug = 1) THEN
1867 DEBUG('Reservation ID = ' || to_char(l_rsv_rec.reservation_id)
1868 ,'Reduce_Move_Order_Quantity');
1869 END IF;
1870
1871 -- query reservation
1872 inv_reservation_pvt.query_reservation
1873 ( p_api_version_number => 1.0
1874 , p_init_msg_lst => fnd_api.g_false
1875 , x_return_status => x_return_status
1876 , x_msg_count => x_msg_count
1877 , x_msg_data => x_msg_data
1878 , p_query_input => l_rsv_rec
1879 , x_mtl_reservation_tbl => l_rsv_tbl
1880 , x_mtl_reservation_tbl_count => l_rsv_count
1881 , x_error_code => l_error_code
1882 );
1883
1884 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1885 IF (l_debug = 1) THEN
1886 DEBUG('Error: Query Reservations returned '
1887 || x_return_status
1888 ,'Reduce_Move_Order_Quantity');
1889 END IF;
1890 IF x_return_status = fnd_api.g_ret_sts_error
1891 THEN
1892 fnd_message.set_name('INV','INV_QRY_RSV_FAILED');
1893 fnd_msg_pub.ADD;
1894 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1895 p_data => x_msg_data,
1896 p_encoded => 'F');
1897 RAISE fnd_api.g_exc_error;
1898 ELSE
1899 RAISE fnd_api.g_exc_unexpected_error;
1900 END IF;
1901 END IF;
1902
1903 IF l_rsv_count <= 0 THEN
1904 IF (l_debug = 1) THEN
1905 DEBUG('Error: Query Reservations returned Reservation Count 0'
1906 ,'Reduce_Move_Order_Quantity');
1907 END IF;
1908 fnd_message.set_name('INV','INV_NO_RSVS_FOUND');
1909 fnd_msg_pub.ADD;
1910
1911 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1912 p_data => x_msg_data,
1913 p_encoded => 'F');
1914 RAISE fnd_api.g_exc_error;
1915 END IF;
1916
1917 l_rsv_rec2 := l_rsv_tbl(1);
1918
1919 -- Convert reduction qty to current RSV UOM
1920 IF l_wdd_rec.requested_quantity_uom
1921 = l_rsv_rec2.reservation_uom_code
1922 THEN
1923 l_reduc_qty_conv := l_reduction_quantity;
1924 ELSE
1925 inv_convert.inv_um_conversion
1926 ( from_unit => l_wdd_rec.requested_quantity_uom
1927 , to_unit => l_rsv_rec2.reservation_uom_code
1928 , item_id => l_rsv_rec2.inventory_item_id
1929 , uom_rate => l_conv_rate
1930 );
1931 IF (NVL(l_conv_rate,0) <= 0)
1932 THEN
1933 IF (l_debug = 1)
1934 THEN
1935 DEBUG('Invalid conversion factor: ' || l_conv_rate
1936 ,'Reduce_Move_Order_Quantity');
1937 END IF;
1938 RAISE fnd_api.g_exc_unexpected_error;
1939 ELSE
1940 l_reduc_qty_conv := ROUND( l_conv_rate * l_reduction_quantity
1941 , g_conv_precision
1942 );
1943 END IF;
1944 END IF;
1945
1946 IF l_rsv_rec2.reservation_quantity > l_reduc_qty_conv
1947 THEN
1948 -- {
1949 -- Determine primary qty to reduce
1950 IF l_wdd_rec.requested_quantity_uom
1951 = l_rsv_rec2.primary_uom_code
1952 THEN
1953 l_prim_qty_conv := l_reduction_quantity;
1954 ELSE
1955 inv_convert.inv_um_conversion
1956 ( from_unit => l_wdd_rec.requested_quantity_uom
1957 , to_unit => l_rsv_rec2.primary_uom_code
1958 , item_id => l_rsv_rec2.inventory_item_id
1959 , uom_rate => l_conv_rate
1960 );
1961 IF (NVL(l_conv_rate,0) <= 0)
1962 THEN
1963 IF (l_debug = 1)
1964 THEN
1965 DEBUG('Invalid conversion factor: ' || l_conv_rate
1966 ,'Reduce_Move_Order_Quantity');
1967 END IF;
1968 RAISE fnd_api.g_exc_unexpected_error;
1969 ELSE
1970 l_prim_qty_conv := ROUND( l_conv_rate * l_reduction_quantity
1971 , g_conv_precision
1972 );
1973 END IF;
1974 END IF;
1975
1976 -- Convert secondary qty if required
1977 IF NVL(l_sec_reduction_quantity,0) > 0
1978 AND
1979 l_wdd_rec.requested_quantity_uom2 IS NOT NULL
1980 THEN
1981 IF l_wdd_rec.requested_quantity_uom2
1982 = l_rsv_rec2.secondary_uom_code
1983 THEN
1984 l_sec_reduc_qty_conv := l_sec_reduction_quantity;
1985 ELSE
1986 inv_convert.inv_um_conversion
1987 ( from_unit => l_wdd_rec.requested_quantity_uom2
1988 , to_unit => l_rsv_rec2.secondary_uom_code
1989 , item_id => l_rsv_rec2.inventory_item_id
1990 , uom_rate => l_conv_rate
1991 );
1992 IF (NVL(l_conv_rate,0) <= 0)
1993 THEN
1994 IF (l_debug = 1)
1995 THEN
1996 DEBUG('Invalid conversion factor: ' || l_conv_rate
1997 ,'Reduce_Move_Order_Quantity');
1998 END IF;
1999 RAISE fnd_api.g_exc_unexpected_error;
2000 ELSE
2001 l_sec_reduc_qty_conv := ROUND( l_conv_rate * l_sec_reduction_quantity
2002 , g_conv_precision
2003 );
2004 END IF;
2005 END IF;
2006 END IF; -- end IF secondary UOM specified
2007
2008 l_update_rec := l_rsv_tbl(1);
2009 l_update_rec.reservation_quantity
2010 := l_update_rec.reservation_quantity - l_reduc_qty_conv;
2011 l_update_rec.primary_reservation_quantity
2012 := l_update_rec.primary_reservation_quantity - l_prim_qty_conv;
2013
2014 IF l_update_rec.secondary_uom_code IS NOT NULL
2015 THEN
2016 l_update_rec.secondary_reservation_quantity
2017 := l_update_rec.secondary_reservation_quantity
2018 - l_sec_reduc_qty_conv;
2019 END IF;
2020
2021 inv_reservation_pub.update_reservation
2022 ( p_api_version_number => 1.0
2023 , p_init_msg_lst => fnd_api.g_false
2024 , x_return_status => x_return_status
2025 , x_msg_count => x_msg_count
2026 , x_msg_data => x_msg_data
2027 , p_original_rsv_rec => l_rsv_tbl(1)
2028 , p_to_rsv_rec => l_update_rec
2029 , p_original_serial_number => l_dummy_sn
2030 , p_to_serial_number => l_dummy_sn
2031 , p_validation_flag => fnd_api.g_true
2032 );
2033
2034 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2035 IF (l_debug = 1) THEN
2036 DEBUG('Error: Update Reservations returned '
2037 || x_return_status
2038 ,'Reduce_Move_Order_Quantity');
2039 END IF;
2040 IF x_return_status = fnd_api.g_ret_sts_error
2041 THEN
2042 fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
2043 fnd_msg_pub.ADD;
2044 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2045 p_data => x_msg_data,
2046 p_encoded => 'F');
2047 RAISE fnd_api.g_exc_error;
2048 ELSE
2049 RAISE fnd_api.g_exc_unexpected_error;
2050 END IF;
2051 END IF;
2052
2053 l_reduction_quantity := 0;
2054 l_sec_reduction_quantity := 0;
2055 -- }
2056 ELSE -- RSV qty <= qty to reduce, so delete RSV
2057 -- {
2058 IF l_rsv_rec2.reservation_quantity = l_reduc_qty_conv
2059 THEN
2060 l_reduction_quantity := 0;
2061 l_sec_reduction_quantity := 0;
2062 ELSE
2063 -- {
2064 -- Convert RSV qty to WDD UOM
2065 IF l_wdd_rec.requested_quantity_uom
2066 = l_rsv_rec2.reservation_uom_code
2067 THEN
2068 l_reduction_quantity := l_reduction_quantity
2069 - l_rsv_rec2.reservation_quantity;
2070 ELSE
2071 inv_convert.inv_um_conversion
2072 ( from_unit => l_rsv_rec2.reservation_uom_code
2073 , to_unit => l_wdd_rec.requested_quantity_uom
2074 , item_id => l_rsv_rec2.inventory_item_id
2075 , uom_rate => l_conv_rate
2076 );
2077 IF (NVL(l_conv_rate,0) <= 0)
2078 THEN
2079 IF (l_debug = 1)
2080 THEN
2081 DEBUG('Invalid conversion factor: ' || l_conv_rate
2082 ,'Reduce_Move_Order_Quantity');
2083 END IF;
2084 RAISE fnd_api.g_exc_unexpected_error;
2085 ELSE
2086 l_reduction_quantity := l_reduction_quantity
2087 - ROUND( l_conv_rate
2088 * l_rsv_rec2.reservation_quantity
2089 , g_conv_precision
2090 );
2091 END IF;
2092 END IF;
2093
2094 -- Convert secondary qty if required
2095 IF NVL(l_sec_reduction_quantity,0) > 0
2096 AND
2097 l_wdd_rec.requested_quantity_uom2 IS NOT NULL
2098 THEN
2099 IF l_wdd_rec.requested_quantity_uom2
2100 = l_rsv_rec2.secondary_uom_code
2101 THEN
2102 l_sec_reduction_quantity
2103 := l_sec_reduction_quantity
2104 - l_rsv_rec2.secondary_reservation_quantity;
2105 ELSE
2106 inv_convert.inv_um_conversion
2107 ( from_unit => l_rsv_rec2.secondary_uom_code
2108 , to_unit => l_wdd_rec.requested_quantity_uom2
2109 , item_id => l_rsv_rec2.inventory_item_id
2110 , uom_rate => l_conv_rate
2111 );
2112 IF (NVL(l_conv_rate,0) <= 0)
2113 THEN
2114 IF (l_debug = 1)
2115 THEN
2116 DEBUG('Invalid conversion factor: ' || l_conv_rate
2117 ,'Reduce_Move_Order_Quantity');
2118 END IF;
2119 RAISE fnd_api.g_exc_unexpected_error;
2120 ELSE
2121 l_sec_reduction_quantity := l_sec_reduction_quantity
2122 - ROUND( l_conv_rate
2123 * l_rsv_rec2.secondary_reservation_quantity
2124 , g_conv_precision
2125 );
2126 END IF;
2127 END IF; -- end IF secondary UOMs match
2128 END IF; -- end IF secondary UOM specified
2129 -- }
2130 END IF;
2131
2132 IF (l_debug = 1) THEN
2133 DEBUG('About to delete reservation ID: '
2134 || to_char(l_rsv_rec2.reservation_id)
2135 ,'Reduce_Move_Order_Quantity');
2136 END IF;
2137
2138 inv_reservation_pub.delete_reservation
2139 ( p_api_version_number => 1.0
2140 , p_init_msg_lst => fnd_api.g_false
2141 , x_return_status => x_return_status
2142 , x_msg_count => x_msg_count
2143 , x_msg_data => x_msg_data
2144 , p_rsv_rec => l_rsv_rec2
2145 , p_serial_number => l_serial_tbl
2146 );
2147
2148 IF x_return_status <> fnd_api.g_ret_sts_success
2149 THEN
2150 IF (l_debug = 1)
2151 THEN
2152 DEBUG('inv_reservation_pub.delete_reservation returned an error status: '
2153 || x_return_status, 'Reduce_Move_Order_Quantity');
2154 END IF;
2155
2156 IF x_return_status = fnd_api.g_ret_sts_error
2157 THEN
2158 fnd_msg_pub.count_and_get
2159 ( p_count => x_msg_count
2160 , p_data => x_msg_data
2161 , p_encoded => 'F'
2162 );
2163 IF (l_debug = 1)
2164 THEN
2165 DEBUG('x_msg_data: ' || x_msg_data
2166 ,'Reduce_Move_Order_Quantity');
2167 END IF;
2168 RAISE fnd_api.g_exc_error;
2169 ELSE
2170 RAISE fnd_api.g_exc_unexpected_error;
2171 END IF;
2172 ELSE
2173 IF (l_debug = 1)
2174 THEN
2175 DEBUG('inv_reservation_pub.delete_reservation returned success'
2176 ,'Reduce_Move_Order_Quantity');
2177 END IF;
2178 END IF;
2179 -- }
2180 END IF;
2181
2182 EXIT WHEN l_rsv_index = l_xdock_rsv_tbl.LAST;
2183 l_rsv_index := l_xdock_rsv_tbl.NEXT(l_rsv_index);
2184 -- }
2185 END LOOP;
2186
2187 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
2188
2189 l_xdock_rsv_tbl.DELETE;
2190 -- }
2191 END IF;
2192
2193 -- {{
2194 -- END reduce_move_order_quantity }}
2195 --
2196
2197 EXCEPTION
2198 WHEN fnd_api.g_exc_error THEN
2199 ROLLBACK TO reducemo_sp;
2200 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
2201 x_return_status := fnd_api.g_ret_sts_error;
2202 fnd_msg_pub.count_and_get
2203 ( p_count => x_msg_count
2204 , p_data => x_msg_data
2205 , p_encoded => 'F'
2206 );
2207 IF (l_debug = 1)
2208 THEN
2209 DEBUG('x_msg_data: ' || x_msg_data
2210 ,'Reduce_Move_Order_Quantity');
2211 END IF;
2212
2213 WHEN OTHERS THEN
2214 ROLLBACK TO reducemo_sp;
2215 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
2216 IF (l_debug = 1) THEN
2217 DEBUG('Others error' || SQLERRM
2218 ,'Reduce_Move_Order_Quantity');
2219 END IF;
2220 x_return_status := fnd_api.g_ret_sts_unexp_error;
2221
2222 END reduce_move_order_quantity;
2223
2224
2225 --Procedure
2226 --reduce_rsv_allocation
2227 --Description
2228 -- This procedure is called from WMSTSKUB.pls and
2229 --inv_mo_cancel_pvt.reduce_move_order_quantity .Given the
2230 --transaction_temp_id AND quantity TO DELETE it deletes/reduces allocations
2231
2232 PROCEDURE reduce_rsv_allocation(
2233 x_return_status OUT NOCOPY VARCHAR2
2234 , x_msg_count OUT NOCOPY NUMBER
2235 , x_msg_data OUT NOCOPY VARCHAR2
2236 , p_transaction_temp_id IN NUMBER
2237 , p_quantity_to_delete IN NUMBER
2238 , p_sec_quantity_to_delete IN NUMBER DEFAULT NULL --INVCONV
2239 , p_ato_serial_pick IN VARCHAR2 DEFAULT NULL --7190635 Added to check whether the call is for ATO serial picking
2240 ) IS
2241 l_reservation_id NUMBER;
2242 l_transaction_temp_id NUMBER;
2243 l_task_qty NUMBER;
2244 l_primary_quantity NUMBER;
2245 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
2246 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
2247 l_update_rec inv_reservation_global.mtl_reservation_rec_type;
2248 l_rsv_count NUMBER;
2249 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
2250 l_quantity_to_delete NUMBER;
2251 l_sec_quantity_to_delete NUMBER; --INVCONV
2252 l_sec_qty NUMBER; --INVCONV
2253 l_sec_deleted_quantity NUMBER; --INVCONV
2254 l_sec_qty_to_delete NUMBER; --INVCONV
2255 l_mo_uom_code VARCHAR2(3);
2256 l_primary_uom_code VARCHAR2(3);
2257 l_inventory_item_id NUMBER;
2258 l_prim_quantity_to_delete NUMBER;
2259 l_organization_id NUMBER;
2260 l_deleted_quantity NUMBER;
2261 l_return_status VARCHAR2(1);
2262 l_error_code NUMBER;
2263 l_ato_serial_pick VARCHAR2(1); -- Bug 7190635
2264 l_retain_ato_profile VARCHAR2(1) := NVL(fnd_profile.VALUE('WSH_RETAIN_ATO_RESERVATIONS'),'N'); --Bug 7190635
2265
2266 CURSOR c_primary_uom IS
2267 SELECT primary_uom_code
2268 FROM mtl_system_items
2269 WHERE organization_id = l_organization_id
2270 AND inventory_item_id = l_inventory_item_id;
2271
2272 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2273 BEGIN
2274 IF (l_debug = 1) THEN
2275 DEBUG('Setting Savepoint', 'reduce_rsv_allocation');
2276 END IF;
2277
2278 SAVEPOINT del_rsv_all_sp;
2279 l_transaction_temp_id := p_transaction_temp_id;
2280 l_deleted_quantity := 0;
2281 l_quantity_to_delete := p_quantity_to_delete;
2282 l_sec_deleted_quantity := 0; --INVCONV
2283 l_sec_quantity_to_delete := p_sec_quantity_to_delete; --INVCONV
2284 l_ato_serial_pick := NVL(p_ato_serial_pick,'N'); --Bug 7190635
2285
2286 IF (l_debug = 1) THEN
2287 DEBUG(' transaction_temp_id:' || l_transaction_temp_id, 'reduce_rsv_allocation');
2288 DEBUG('quantity_to_delete:' || l_quantity_to_delete, 'reduce_rsv_allocation');
2289 DEBUG('sec_quantity_to_delete:' || l_sec_quantity_to_delete, 'reduce_rsv_allocation');
2290 END IF;
2291
2292 SELECT ABS(mmtt.transaction_quantity)
2293 , ABS(mmtt.primary_quantity)
2294 , ABS(mmtt.secondary_transaction_quantity) --INVCONV
2295 , mmtt.reservation_id
2296 , mmtt.organization_id
2297 , mmtt.inventory_item_id
2298 , mtrl.uom_code
2299 -- INVCONV correcting the orders
2300 INTO l_task_qty
2301 , l_primary_quantity
2302 , l_sec_qty --INVCONV
2303 , l_reservation_id
2304 , l_organization_id
2305 , l_inventory_item_id
2306 , l_mo_uom_code
2307 FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
2308 WHERE mmtt.transaction_temp_id = l_transaction_temp_id
2309 AND mmtt.move_order_line_id = mtrl.line_id;
2310
2311 -- find quantity to delete in primary UOM
2312 OPEN c_primary_uom;
2313 FETCH c_primary_uom INTO l_primary_uom_code;
2314
2315 IF c_primary_uom%NOTFOUND THEN
2316 IF (l_debug = 1) THEN
2317 DEBUG('Move order line not found', 'reduce_rsv_allocation');
2318 END IF;
2319
2320 CLOSE c_primary_uom;
2321 RAISE fnd_api.g_exc_unexpected_error;
2322 END IF;
2323
2324 CLOSE c_primary_uom;
2325
2326 IF (l_debug = 1) THEN
2327 DEBUG('before uom check', 'reduce_rsv_allocation');
2328 END IF;
2329
2330 IF l_primary_uom_code <> l_mo_uom_code THEN
2331 l_prim_quantity_to_delete :=
2332 inv_convert.inv_um_convert(l_inventory_item_id, NULL, l_quantity_to_delete, l_mo_uom_code, l_primary_uom_code, NULL, NULL);
2333
2334 IF (l_prim_quantity_to_delete = -99999) THEN
2335 IF (l_debug = 1) THEN
2336 DEBUG('Cannot convert uom to primary uom', 'reduce_rsv_allocation');
2337 END IF;
2338
2339 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
2340 fnd_message.set_token('UOM', l_primary_uom_code);
2341 fnd_message.set_token('ROUTINE', 'reduce_rsv_allocation');
2342 fnd_msg_pub.ADD;
2343 RAISE fnd_api.g_exc_unexpected_error;
2344 END IF;
2345 ELSE
2346 l_prim_quantity_to_delete := l_quantity_to_delete;
2347 END IF;
2348 l_sec_qty_to_delete := l_sec_quantity_to_delete; --INVCONV
2349 -- if the allocation corresponds to a reservation, we need to update
2350 -- the reservation
2351 IF l_ato_serial_pick = 'N' THEN --Bug 7190635
2352 IF l_reservation_id IS NOT NULL THEN
2353 l_rsv_rec.reservation_id := l_reservation_id;
2354
2355 IF (l_debug = 1) THEN
2356 DEBUG('query reservation', 'reduce_rsv_allocation');
2357 END IF;
2358
2359 -- query reservation
2360 inv_reservation_pvt.query_reservation(
2361 p_api_version_number => 1.0
2362 , p_init_msg_lst => fnd_api.g_false
2363 , x_return_status => l_return_status
2364 , x_msg_count => x_msg_count
2365 , x_msg_data => x_msg_data
2366 , p_query_input => l_rsv_rec
2367 , x_mtl_reservation_tbl => l_rsv_tbl
2368 , x_mtl_reservation_tbl_count => l_rsv_count
2369 , x_error_code => l_error_code
2370 );
2371
2372 IF l_return_status = fnd_api.g_ret_sts_error THEN
2373 IF (l_debug = 1) THEN
2374 DEBUG('Query reservation returned error', 'reduce_rsv_allocation');
2375 END IF;
2376
2377 RAISE fnd_api.g_exc_error;
2378 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2379 IF (l_debug = 1) THEN
2380 DEBUG('Query reservation returned unexpected error', 'reduce_rsv_allocation');
2381 END IF;
2382
2383 RAISE fnd_api.g_exc_unexpected_error;
2384 END IF;
2385
2386 l_update_rec := l_rsv_tbl(1);
2387
2388 -- update detailed quantity
2389 IF l_update_rec.detailed_quantity > l_prim_quantity_to_delete THEN
2390 l_update_rec.detailed_quantity := l_update_rec.detailed_quantity - l_prim_quantity_to_delete;
2391 l_update_rec.secondary_detailed_quantity := l_update_rec.secondary_detailed_quantity - l_sec_qty_to_delete; --INCONV
2392 ELSE
2393 l_update_rec.detailed_quantity := 0;
2394 l_update_rec.secondary_detailed_quantity := 0; --INCONV
2395 END IF;
2396
2397
2398 l_update_rec.reservation_quantity := NULL;
2399
2400 --set primary reservation quantity
2401 IF l_update_rec.primary_reservation_quantity > l_prim_quantity_to_delete THEN
2402 l_update_rec.primary_reservation_quantity := l_update_rec.primary_reservation_quantity - l_prim_quantity_to_delete;
2403 l_update_rec.secondary_reservation_quantity := l_update_rec.secondary_reservation_quantity - l_sec_qty_to_delete; --INVCONV
2404 ELSE -- delete entire reservation
2405 l_update_rec.primary_reservation_quantity := 0;
2406 l_update_rec.secondary_reservation_quantity := 0; --INVCONV
2407 END IF; -- rsv qty > task qty
2408
2409 IF (l_debug = 1) THEN
2410 DEBUG('update reservation', 'reduce_rsv_allocation');
2411 END IF;
2412
2413 -- INVCONV - Make sure Qty2 are NULL if nor present
2414 IF ( l_update_rec.secondary_uom_code IS NULL ) THEN
2415 l_update_rec.secondary_reservation_quantity := NULL;
2416 l_update_rec.secondary_detailed_quantity := NULL;
2417 END IF;
2418
2419 -- update reservations
2420 inv_reservation_pub.update_reservation(
2421 p_api_version_number => 1.0
2422 , p_init_msg_lst => fnd_api.g_false
2423 , x_return_status => l_return_status
2424 , x_msg_count => x_msg_count
2425 , x_msg_data => x_msg_data
2426 , p_original_rsv_rec => l_rsv_tbl(1)
2427 , p_to_rsv_rec => l_update_rec
2428 , p_original_serial_number => l_dummy_sn
2429 , p_to_serial_number => l_dummy_sn
2430 , p_validation_flag => fnd_api.g_true
2431 );
2432
2433 IF l_return_status = fnd_api.g_ret_sts_error THEN
2434 IF (l_debug = 1) THEN
2435 DEBUG('Update reservation returned error', 'reduce_rsv_allocation');
2436 END IF;
2437
2438 RAISE fnd_api.g_exc_error;
2439 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2440 IF (l_debug = 1) THEN
2441 DEBUG('Update reservation returned unexpected error', 'reduce_rsv_allocation');
2442 END IF;
2443
2444 RAISE fnd_api.g_exc_unexpected_error;
2445 END IF;
2446 END IF; -- reservation id is not null
2447 END IF; --Bug 7190635, l_ato_serial_pick is 'N'
2448
2449 IF (l_debug = 1) THEN
2450 DEBUG('Retain ATO reservation:'||l_retain_ato_profile, 'reduce_rsv_allocation');
2451 DEBUG('Quantity to delete:'||l_quantity_to_delete, 'reduce_rsv_allocation');
2452 DEBUG('reservation id:'||l_reservation_id, 'reduce_rsv_allocation');
2453 END IF;
2454 -- If we are deleting entire allocation
2455 --Bug 7190635, we are deleting entire allocation for ATO serial picking
2456
2457 IF l_quantity_to_delete = l_task_qty
2458 OR (l_retain_ato_profile = 'Y' AND l_ato_serial_pick='Y') THEN
2459 l_deleted_quantity := l_deleted_quantity + l_task_qty;
2460 l_sec_deleted_quantity := l_sec_deleted_quantity + l_sec_qty; --INVCONV
2461
2462 inv_trx_util_pub.delete_transaction(
2463 x_return_status => l_return_status
2464 , x_msg_data => x_msg_data
2465 , x_msg_count => x_msg_count
2466 , p_transaction_temp_id => l_transaction_temp_id
2467 );
2468
2469 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2470 IF (l_debug = 1) THEN
2471 DEBUG('Error occurred while Deleting the Transaction', 'reduce_rsv_allocation');
2472 END IF;
2473
2474 RAISE fnd_api.g_exc_error;
2475 END IF;
2476 ELSE -- reduce the quantity on the allocation
2477 IF (l_debug = 1) THEN
2478 DEBUG('reducing quantity on the allocation', 'reduce_rsv_allocation');
2479 END IF;
2480
2481 l_deleted_quantity := l_deleted_quantity + l_quantity_to_delete;
2482 l_sec_deleted_quantity := l_sec_deleted_quantity + l_sec_quantity_to_delete; --INVCONV
2483 inv_mo_line_detail_util.reduce_allocation_quantity(
2484 x_return_status => l_return_status
2485 , p_transaction_temp_id => l_transaction_temp_id
2486 , p_quantity => l_quantity_to_delete
2487 , p_secondary_quantity => l_sec_quantity_to_delete --INVCONV
2488 );
2489
2490 IF l_return_status = fnd_api.g_ret_sts_error THEN
2491 IF (l_debug = 1) THEN
2492 DEBUG('Reduce allocation returned error', 'reduce_rsv_allocation');
2493 END IF;
2494
2495 RAISE fnd_api.g_exc_error;
2496 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2497 IF (l_debug = 1) THEN
2498 DEBUG('Reduce allocation returned unexpected error', 'reduce_rsv_allocation');
2499 END IF;
2500
2501 RAISE fnd_api.g_exc_unexpected_error;
2502 END IF;
2503 END IF; -- det. quantity = task qty
2504
2505 x_return_status := fnd_api.g_ret_sts_success;
2506 EXCEPTION
2507 WHEN fnd_api.g_exc_error THEN
2508 ROLLBACK TO del_rsv_all_sp;
2509 x_return_status := fnd_api.g_ret_sts_error;
2510 WHEN OTHERS THEN
2511 ROLLBACK TO del_rsv_all_sp;
2512
2513 IF (l_debug = 1) THEN
2514 DEBUG('Others error' || SQLERRM, 'reduce_rsv_allocation');
2515 END IF;
2516
2517 x_return_status := fnd_api.g_ret_sts_unexp_error;
2518 END reduce_rsv_allocation;
2519
2520
2521 /* The following procedure is called by shipping to update carton group id whenever
2522 shipping unassign a wdd line from a delivery */
2523 PROCEDURE update_mol_carton_group
2524 ( x_return_status OUT NOCOPY VARCHAR2
2525 , x_msg_cnt OUT NOCOPY NUMBER
2526 , x_msg_data OUT NOCOPY VARCHAR2
2527 , p_line_id IN NUMBER
2528 , p_carton_grouping_id IN NUMBER
2529 ) IS
2530
2531 l_debug NUMBER;
2532 l_mo_type NUMBER;
2533
2534 CURSOR c_get_mo_type
2535 ( p_mo_line_id IN NUMBER
2536 ) IS
2537 SELECT mtrh.move_order_type
2538 FROM mtl_txn_request_lines mtrl
2539 , mtl_txn_request_headers mtrh
2540 WHERE mtrl.line_id = p_mo_line_id
2541 AND mtrh.header_id = mtrl.header_id;
2542
2543 BEGIN
2544 IF (l_debug = 1) THEN
2545 DEBUG('move order line id: ' || p_line_id, 'update_mol_carton_group');
2546 DEBUG('carton group id: ' || p_carton_grouping_id, 'update_mol_carton_group');
2547 DEBUG('before update statement...', 'update_mol_carton_group');
2548 END IF;
2549
2550 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2551 x_return_status := fnd_api.g_ret_sts_success;
2552
2553 -- {{
2554 -- BEGIN update_mol_carton_group }}
2555 --
2556 OPEN c_get_mo_type(p_line_id);
2557 FETCH c_get_mo_type INTO l_mo_type;
2558 CLOSE c_get_mo_type;
2559
2560 IF l_mo_type <> INV_GLOBALS.G_MOVE_ORDER_PUT_AWAY
2561 THEN
2562 -- {{
2563 -- Unassign a pick released delivery detail from
2564 -- the delivery. Carton grouping ID must get updated }}
2565 --
2566 UPDATE mtl_txn_request_lines
2567 SET carton_grouping_id = p_carton_grouping_id
2568 WHERE line_id = p_line_id;
2569
2570 -- {{
2571 -- Unassign a cross dock pegged delivery detail from
2572 -- the delivery, with material already received.
2573 -- Carton grouping ID column stays as is }}
2574 --
2575 IF SQL%NOTFOUND THEN
2576 x_return_status := fnd_api.g_ret_sts_unexp_error;
2577
2578 IF (l_debug = 1) THEN
2579 DEBUG('can not find move order line', 'update_mol_carton_group');
2580 END IF;
2581
2582 fnd_message.set_name('INV', 'INV_PP_INPUT_LINE_NOT_FOUND');
2583 fnd_msg_pub.ADD;
2584 END IF;
2585
2586 IF (l_debug = 1) THEN
2587 DEBUG('after update statement', 'update_mol_carton_group');
2588 END IF;
2589 END IF;
2590
2591 -- {{
2592 -- END update_mol_carton_group }}
2593 --
2594
2595 EXCEPTION
2596 WHEN OTHERS THEN
2597 x_return_status := fnd_api.g_ret_sts_unexp_error;
2598
2599 IF (l_debug = 1) THEN
2600 DEBUG('Exception: ' || sqlerrm, 'update_mol_carton_group');
2601 END IF;
2602
2603 fnd_message.set_name('INV', 'FAIL_TO_UPDATE_CARTON_GROUP'); -- need new msg
2604 fnd_msg_pub.ADD;
2605 END update_mol_carton_group;
2606
2607 END inv_mo_cancel_pvt;