[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.2 2008/07/29 12:53:01 ptkumar 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.2 2008/07/29 12:53:01 ptkumar 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 WHERE line_id = p_line_id;
856
857 --
858 -- ER3969328: CI project. The following changes made for CI project ER.
859 -- Check first if this API was called with a value passed to p_delete_alloc.
860 -- If this is null pick the org level parameter setting. Check if the option
861 -- 'Auto Delete Allocations at Move Order Cancel' is set Yes. If Yes then
862 -- the allocations should be deleted and the move order line closed.
863 -- The variable g_auto_del_alloc is cached.
864 --
865
866 IF p_delete_alloc IS NULL
867 THEN
868 IF g_auto_del_alloc is null
869 THEN
870 select NVL(auto_del_alloc_flag,'N')
871 into g_auto_del_alloc
872 from mtl_parameters
873 where organization_id = l_org_id;
874 END IF;
875 l_alloc_flag := g_auto_del_alloc;
876 ELSE
877 l_alloc_flag := p_delete_alloc;
878 END IF;
879
880 --
881 -- ER3969328: CI project.Check the total number of allocations
882 -- for this move_order_line_id
883 --
884 select count(*)
885 into l_count_alloc
886 from mtl_material_transactions_temp
887 where move_order_line_id = p_line_id;
888
889 --
890 -- ER3969328: CI project.Only if this flag is set to 'Y'
891 -- will the delete allocations API be called.
892 --
893 IF (l_alloc_flag = 'Y' and l_is_wms_org = FALSE)
894 THEN
895 -- {
896 for c_mmtt_rec in c_mmtt
897 LOOP
898 l_count := l_count + 1; --counter
899 l_api_return_status := fnd_api.g_ret_sts_success;
900 inv_mo_line_detail_util.delete_allocations
901 ( x_return_status => l_api_return_status
902 , x_msg_data => x_msg_count
903 , x_msg_count => x_msg_data
904 , p_mo_line_id => p_line_id
905 , p_transaction_temp_id => c_mmtt_rec.transaction_temp_id
906 );
907 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
908 IF (l_debug = 1) THEN
909 DEBUG('delete_allocations returned success'
910 ,'Cancel_Move_Order_Line');
911 END IF;
912 l_flag := TRUE;
913 ELSE
914 IF (l_debug = 1) THEN
915 DEBUG('Error: delete_allocations return status: '
916 || l_api_return_status
917 ,'Cancel_Move_Order_Line');
918 END IF;
919 l_flag := FALSE;
920 RAISE fnd_api.g_exc_error;
921 END IF;
922 END LOOP;
923
924 --
925 -- ER3969328: CI project.After deleting allocations successfully
926 -- close the move order line.
927 --
928 IF (l_flag and l_count = l_count_alloc)
929 THEN
930 update mtl_txn_request_lines
931 set line_status = 5
932 where line_id = p_line_id;
933 END IF;
934 -- }
935 END IF; -- g_auto_del_alloc = 'Y' and l_is_wms_org = FALSE
936 -- }
937 ELSE -- MO type putaway
938 -- {
939 -- {{
940 -- Cancel sales/internal order where a xdock peg
941 -- exists, with material in Receiving. The operation
942 -- plan should get cancelled, and material should not
943 -- be staged }}
944 --
945 -- Lock the putaway move order line
946 BEGIN
947 SELECT 'x'
948 INTO l_dummy
949 FROM mtl_txn_request_lines mtrl
950 WHERE mtrl.line_id = p_line_id
951 FOR UPDATE NOWAIT;
952 EXCEPTION
953 WHEN record_locked THEN
954 DEBUG('Unable to lock the putaway MO line'
955 ,'CANCEL_MOVE_ORDER_LINE');
956 fnd_message.set_name('WMS', 'INV_PUTAWAY_MOL_LOCK_FAIL');
957 fnd_msg_pub.ADD;
958 RAISE fnd_api.g_exc_error;
959 END;
960
961 wms_xdock_utils_pvt.g_demand_triggered := TRUE;
962
963 l_api_return_status := fnd_api.g_ret_sts_success;
964 inv_rcv_integration_pvt.call_atf_api
965 ( x_return_status => l_api_return_status
966 , x_msg_data => x_msg_data
967 , x_msg_count => x_msg_count
968 , x_error_code => l_error_code
969 , p_source_task_id => NULL
970 , p_activity_type_id => 1
971 , p_mol_id => p_line_id
972 , p_atf_api_name => inv_rcv_integration_pvt.g_atf_api_cancel
973 , p_mmtt_error_code => 'INV_XDK_DEMAND_CHG'
974 , p_mmtt_error_explanation => NULL
975 , p_retain_mmtt => 'Y'
976 );
977
978 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
979
980 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
981 IF (l_debug = 1)
982 THEN
983 DEBUG('inv_rcv_integration_pvt.call_atf_api returned success'
984 ,'CANCEL_MOVE_ORDER_LINE');
985 END IF;
986 ELSE
987 IF (l_debug = 1)
988 THEN
989 DEBUG('inv_rcv_integration_pvt.call_atf_api returned an error status: '
990 || l_api_return_status, 'CANCEL_MOVE_ORDER_LINE');
991 DEBUG('l_error_code: ' || l_error_code
992 ,'CANCEL_MOVE_ORDER_LINE');
993 END IF;
994
995 IF l_api_return_status = fnd_api.g_ret_sts_error
996 THEN
997 RAISE fnd_api.g_exc_error;
998 ELSE
999 RAISE fnd_api.g_exc_unexpected_error;
1000 END IF;
1001 END IF;
1002 -- }
1003 END IF;
1004 -- }
1005 ELSE -- line ID is NULL
1006 -- {
1007 IF p_delivery_detail_id IS NULL
1008 THEN
1009 IF (l_debug = 1) THEN
1010 DEBUG('Both p_line_id and p_delivery_detail_id are null!'
1011 ,'CANCEL_MOVE_ORDER_LINE');
1012 RAISE fnd_api.g_exc_unexpected_error;
1013 END IF;
1014 END IF;
1015 -- {{
1016 -- Cancellation of sales/internal order where a xdock peg
1017 -- exists, before material is received. The reservations
1018 -- should get deleted }}
1019 --
1020 l_xdock_rsv_tbl.DELETE;
1021 BEGIN
1022 OPEN c_xdock_rsv (p_delivery_detail_id);
1023 FETCH c_xdock_rsv BULK COLLECT INTO l_xdock_rsv_tbl;
1024 CLOSE c_xdock_rsv;
1025 EXCEPTION
1026 WHEN record_locked THEN
1027 IF (l_debug = 1) THEN
1028 DEBUG('Unable to lock xdock rsv record(s)'
1029 ,'CANCEL_MOVE_ORDER_LINE');
1030 END IF;
1031 IF c_xdock_rsv%ISOPEN
1032 THEN
1033 CLOSE c_xdock_rsv;
1034 END IF;
1035 fnd_message.set_name('WMS', 'INV_RSV_LOCK_FAIL');
1036 fnd_msg_pub.ADD;
1037 RAISE fnd_api.g_exc_error;
1038 END;
1039
1040 wms_xdock_utils_pvt.g_demand_triggered := TRUE;
1041
1042 l_rsv_index := l_xdock_rsv_tbl.FIRST;
1043 LOOP
1044 -- {
1045 IF (l_xdock_rsv_tbl.COUNT = 0) THEN
1046 IF (l_debug = 1) THEN
1047 DEBUG('No xdock rsv records to process','CANCEL_MOVE_ORDER_LINE');
1048 END IF;
1049 EXIT;
1050 END IF;
1051
1052 l_rsv_rec.reservation_id := l_xdock_rsv_tbl(l_rsv_index);
1053 IF (l_debug = 1) THEN
1054 DEBUG('About to delete reservation ID: '
1055 || to_char(l_rsv_rec.reservation_id)
1056 ,'CANCEL_MOVE_ORDER_LINE');
1057 END IF;
1058
1059 l_api_return_status := fnd_api.g_ret_sts_success;
1060 inv_reservation_pub.delete_reservation
1061 ( p_api_version_number => 1.0
1062 , p_init_msg_lst => fnd_api.g_false
1063 , x_return_status => l_api_return_status
1064 , x_msg_count => x_msg_count
1065 , x_msg_data => x_msg_data
1066 , p_rsv_rec => l_rsv_rec
1067 , p_serial_number => l_serial_tbl
1068 );
1069
1070 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
1071 IF (l_debug = 1)
1072 THEN
1073 DEBUG('inv_reservation_pub.delete_reservation returned success'
1074 ,'CANCEL_MOVE_ORDER_LINE');
1075 END IF;
1076 ELSE
1077 IF (l_debug = 1)
1078 THEN
1079 DEBUG('inv_reservation_pub.delete_reservation returned an error status: '
1080 || l_api_return_status, 'CANCEL_MOVE_ORDER_LINE');
1081 END IF;
1082
1083 IF l_api_return_status = fnd_api.g_ret_sts_error
1084 THEN
1085 RAISE fnd_api.g_exc_error;
1086 ELSE
1087 RAISE fnd_api.g_exc_unexpected_error;
1088 END IF;
1089 END IF;
1090
1091 EXIT WHEN l_rsv_index = l_xdock_rsv_tbl.LAST;
1092 l_rsv_index := l_xdock_rsv_tbl.NEXT(l_rsv_index);
1093 -- }
1094 END LOOP;
1095
1096 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
1097
1098 l_xdock_rsv_tbl.DELETE;
1099 -- }
1100 END IF;
1101
1102 -- {{
1103 -- END cancel_move_order_line }}
1104 --
1105 EXCEPTION
1106 WHEN fnd_api.g_exc_error THEN
1107 ROLLBACK TO cancelmo_sp;
1108 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
1109 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1110 p_data => x_msg_data,
1111 p_encoded => 'F');
1112 x_return_status := fnd_api.g_ret_sts_error;
1113
1114 IF (l_debug = 1) THEN
1115 DEBUG('Return status = ' || x_return_status ||
1116 ', x_msg_data = ' || x_msg_data
1117 ,'CANCEL_MOVE_ORDER_LINE');
1118 END IF;
1119
1120 WHEN fnd_api.g_exc_unexpected_error THEN
1121 ROLLBACK TO cancelmo_sp;
1122 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
1123 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1124 p_data => x_msg_data,
1125 p_encoded => 'F');
1126 x_return_status := fnd_api.g_ret_sts_unexp_error;
1127
1128 IF (l_debug = 1) THEN
1129 DEBUG('Return status = ' || x_return_status ||
1130 ', x_msg_data = ' || x_msg_data
1131 ,'CANCEL_MOVE_ORDER_LINE');
1132 END IF;
1133
1134 WHEN OTHERS THEN
1135 ROLLBACK TO cancelmo_sp;
1136 x_return_status := fnd_api.g_ret_sts_unexp_error;
1137
1138 --ER: CI project. Setting the fields x_msg_count and x_msg_data correctly.
1139 x_msg_count := SQLCODE;
1140 x_msg_data := SQLERRM;
1141
1142 IF (l_debug = 1) THEN
1143 DEBUG('Other error: Code = ' || SQLCODE || ' : Msg = ' || SQLERRM
1144 ,'CANCEL_MOVE_ORDER_LINE');
1145 END IF;
1146
1147 END cancel_move_order_line;
1148
1149
1150 --Procedure
1151 -- Reduce_Move_Order_Quantity
1152 --Description
1153 -- This procedure is called from Shipping when the quantity on a
1154 -- sales order line is reduced, leading to the quantity on a delivery
1155 -- detail being reduced. This procedure reduces the required_quantity
1156 -- column on the move order line by p_reduction_quantity. The required
1157 -- quantity is the quantity needed by shipping to fulfill the sales order.
1158 -- Any quantity transacted for this move order line in excess of the
1159 -- required_quantity will be moved to staging, but will not be
1160 -- reserved or shipped to the customer. Since the
1161 -- sales order line quantity has been reduced, the reservation quantity
1162 -- for the sales order should also be reduced. Some reservations are
1163 -- reduced here, and some are reduced in Finalize_Pick_Confirm
1164 -- (INVVTROB.pls).
1165 -- If WMS is installed, undispatched tasks may be deleted, since these
1166 -- tasks are no longer necessary.
1167 -- Parameters
1168 -- p_line_id: The move order line id to be reduced
1169 -- p_reduction_quantity: How much to reduce the required
1170 -- quantity by, in the UOM of the move order line
1171 -- p_txn_source_line_Id: The sales order line id. If this
1172 -- parameter is not passed in, we get it from the delivery detail.
1173 -- p_delivery_detail_id: Added for Planned Crossdocking in Release 12.0
1174 -- Shipping passes in delivery detail ID if the WDD record is pegged
1175 -- to a supply source (via reservations) and the supply has not been
1176 -- received. After receipt reductions are not allowed so this API
1177 -- should not be called to reduce qty on a putaway move order line
1178
1179 PROCEDURE reduce_move_order_quantity
1180 ( x_return_status OUT NOCOPY VARCHAR2
1181 , x_msg_count OUT NOCOPY NUMBER
1182 , x_msg_data OUT NOCOPY VARCHAR2
1183 , p_line_id IN NUMBER
1184 , p_reduction_quantity IN NUMBER
1185 , p_sec_reduction_quantity IN NUMBER DEFAULT NULL
1186 , p_txn_source_line_id IN NUMBER DEFAULT NULL
1187 , p_delivery_detail_id IN NUMBER DEFAULT NULL -- planned crossdocking project
1188 ) IS
1189 l_quantity NUMBER;
1190 l_quantity_detailed NUMBER;
1191 l_organization_id NUMBER;
1192 l_transaction_temp_id NUMBER;
1193 l_task_qty NUMBER;
1194 l_return_status VARCHAR2(1);
1195 l_deleted_quantity NUMBER;
1196 l_reservation_id NUMBER;
1197 l_primary_quantity NUMBER;
1198 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1199 l_rsv_rec2 inv_reservation_global.mtl_reservation_rec_type;
1200 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
1201 l_update_rec inv_reservation_global.mtl_reservation_rec_type;
1202 l_serial_tbl inv_reservation_global.serial_number_tbl_type;
1203 l_rsv_count NUMBER;
1204 l_rsv_index NUMBER;
1205 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
1206 l_quantity_to_delete NUMBER;
1207 l_sec_quantity_to_delete NUMBER; --INVCONV
1208 l_sec_deleted_quantity NUMBER; --INVCONV
1209 l_sec_quantity NUMBER; --INVCONV
1210 l_sec_quantity_detailed NUMBER; --INVCONV
1211 l_sec_reduction_quantity NUMBER; --INVCONV
1212 l_sec_qty NUMBER; --INVCONV
1213 l_max_delete_sec_quantity NUMBER; --INVCONV
1214 l_sec_qty_to_delete NUMBER; --INVCONV
1215 l_max_delete_quantity NUMBER;
1216 l_txn_source_line_id NUMBER;
1217 l_reduction_quantity NUMBER;
1218 l_error_code NUMBER;
1219 l_mo_uom_code VARCHAR2(3);
1220 l_primary_uom_code VARCHAR2(3);
1221 l_inventory_item_id NUMBER;
1222 l_prim_quantity_to_delete NUMBER;
1223 l_move_order_type NUMBER;
1224 l_reduc_qty_conv NUMBER;
1225 l_conv_rate NUMBER;
1226 l_sec_reduc_qty_conv NUMBER;
1227 l_prim_qty_conv NUMBER;
1228
1229 l_debug NUMBER;
1230 l_required_quantity NUMBER; --Bug#5095840.
1231 l_sec_required_quantity NUMBER; --Bug#5095840.
1232
1233 TYPE xdock_rsv_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1234 l_xdock_rsv_tbl xdock_rsv_tbl;
1235
1236 record_locked EXCEPTION;
1237 PRAGMA EXCEPTION_INIT (record_locked, -54);
1238
1239 /*Bug#5095840. In the below cursor, the columns, 'Required_quantity' and
1240 'secnodary_required_quantity' are also selected.*/
1241 CURSOR c_line_info IS
1242 SELECT quantity
1243 ,NVL(required_quantity, quantity)
1244 , NVL(quantity_detailed, 0)
1245 , secondary_quantity
1246 , NVL(secondary_required_quantity, secondary_quantity) --INVCONV
1247 , secondary_quantity_detailed --INVCONV
1248 , organization_id
1249 , inventory_item_id
1250 , uom_code
1251 , txn_source_line_id
1252 FROM mtl_txn_request_lines
1253 WHERE line_id = p_line_id
1254 FOR UPDATE;
1255
1256 CURSOR c_primary_uom IS
1257 SELECT primary_uom_code
1258 FROM mtl_system_items
1259 WHERE organization_id = l_organization_id
1260 AND inventory_item_id = l_inventory_item_id;
1261
1262 /*Bug#5095840. Added the below cursor to fetch the source line id
1263 if 'p_txn_source_line_id' is passed as NULL.*/
1264 CURSOR c_txn_source_line IS
1265 SELECT source_line_id
1266 FROM wsh_delivery_details
1267 WHERE move_order_line_id IS NOT NULL
1268 AND move_order_line_id = p_line_id
1269 AND released_status = 'S';
1270
1271 CURSOR c_reservations IS
1272 SELECT mr.reservation_id
1273 FROM mtl_reservations mr
1274 WHERE mr.demand_source_type_id IN (2,8)
1275 AND NVL(mr.staged_flag,'N') <> 'Y'
1276 AND mr.demand_source_line_id = l_txn_source_line_id
1277 AND mr.demand_source_line_detail IS NULL
1278 AND mr.primary_reservation_quantity > NVL(mr.detailed_quantity, 0);
1279
1280 CURSOR c_undispatched_tasks IS
1281 SELECT mmtt.transaction_temp_id
1282 , ABS(mmtt.transaction_quantity)
1283 , ABS(mmtt.secondary_transaction_quantity)
1284 , ABS(mmtt.primary_quantity)
1285 , mmtt.reservation_id
1286 FROM mtl_material_transactions_temp mmtt
1287 WHERE mmtt.move_order_line_id = p_line_id
1288 AND NOT EXISTS( SELECT 'Y'
1289 FROM wms_dispatched_tasks wdt
1290 WHERE wdt.transaction_temp_id
1291 = nvl( mmtt.parent_line_id
1292 , mmtt.transaction_temp_id
1293 )
1294 )
1295 ORDER BY mmtt.transaction_quantity ASC;
1296
1297 CURSOR c_xdock_rsv
1298 ( p_wdd_id IN NUMBER
1299 ) IS
1300 SELECT mr.reservation_id
1301 FROM mtl_reservations mr
1302 WHERE mr.demand_source_line_detail = p_wdd_id
1303 AND mr.demand_source_type_id IN (2,8)
1304 AND NVL(mr.crossdock_flag,'N') = 'Y'
1305 FOR UPDATE NOWAIT;
1306
1307 CURSOR c_lock_wdd
1308 ( p_wdd_id IN NUMBER
1309 ) IS
1310 SELECT wdd.delivery_detail_id
1311 , wdd.requested_quantity
1312 , wdd.requested_quantity_uom
1313 , wdd.requested_quantity2
1314 , wdd.requested_quantity_uom2
1315 FROM wsh_delivery_details wdd
1316 WHERE wdd.delivery_detail_id = p_wdd_id
1317 FOR UPDATE NOWAIT;
1318
1319 l_wdd_rec c_lock_wdd%ROWTYPE;
1320
1321 BEGIN
1322 x_return_status := fnd_api.g_ret_sts_success;
1323 SAVEPOINT reducemo_sp;
1324
1325 -- {{
1326 -- BEGIN reduce_move_order_quantity }}
1327 --
1328 IF (l_debug = 1) THEN
1329 DEBUG('Entered with parameters: '
1330 || ' p_line_id: ' || to_char(p_line_id)
1331 || ', p_reduction_quantity: ' || to_char(p_reduction_quantity)
1332 || ', p_sec_reduction_quantity: ' || to_char(p_sec_reduction_quantity)
1333 || ', p_txn_source_line_id: ' || to_char(p_txn_source_line_id)
1334 || ', p_delivery_detail_id: ' || to_char(p_delivery_detail_id)
1335 ,'Reduce_Move_Order_Quantity');
1336 END IF;
1337
1338 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1339
1340 IF p_reduction_quantity <= 0 THEN
1341 RETURN;
1342 END IF;
1343
1344 l_deleted_quantity := 0;
1345 l_sec_deleted_quantity := 0; --INVCONV
1346 l_reduction_quantity := p_reduction_quantity;
1347 l_sec_reduction_quantity := p_sec_reduction_quantity; --INVCONV
1348
1349 IF p_line_id IS NOT NULL
1350 THEN
1351 -- {
1352 -- {{
1353 -- Verify that reduce API works as before for non-putaway
1354 -- (sales/internal order) move order lines }}
1355 --
1356 BEGIN
1357 SELECT NVL(mtrh.move_order_type,0)
1358 INTO l_move_order_type
1359 FROM mtl_txn_request_lines mtrl
1360 , mtl_txn_request_headers mtrh
1361 WHERE mtrl.line_id = p_line_id
1362 AND mtrh.header_id = mtrl.header_id;
1363
1364 IF (l_debug = 1) THEN
1365 DEBUG('Move order type: ' || to_char(l_move_order_type)
1366 ,'Reduce_Move_Order_Quantity');
1367 END IF;
1368
1369 IF l_move_order_type <= 0
1370 THEN
1371 RAISE fnd_api.g_exc_unexpected_error;
1372 END IF;
1373
1374 EXCEPTION
1375 WHEN OTHERS THEN
1376 IF (l_debug = 1) THEN
1377 DEBUG('Unxexpected error querying MO type: '
1378 || 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'
1391 ,'Reduce_Move_Order_Quantity');
1392 END IF;
1393 fnd_message.set_name('WMS', 'INV_MOL_PUTAWAY_QTY_NOCHG');
1394 fnd_msg_pub.ADD;
1395 RAISE fnd_api.g_exc_error;
1396 END IF;
1397
1398 -- query mo line info
1399 OPEN c_line_info;
1400 FETCH c_line_info
1401 INTO l_quantity
1402 , l_required_quantity /*Bug#5095840*/
1403 , l_quantity_detailed
1404 , l_sec_quantity
1405 , l_sec_required_quantity /*Bug#5095840*/
1406 , l_sec_quantity_detailed
1407 , l_organization_id
1408 , l_inventory_item_id
1409 , l_mo_uom_code
1410 , l_txn_source_line_id; --INVCONV
1411
1412 IF (l_debug = 1) THEN
1413 DEBUG('Move order line details are:', 'Reduce_Move_Order_Quantity');
1414 DEBUG('l_quantity : ' || l_quantity, 'Reduce_Move_Order_Quantity');
1415 DEBUG('l_quantity_detailed : ' || l_quantity_detailed, 'Reduce_Move_Order_Quantity');
1416 DEBUG('l_organization_id : ' || l_organization_id, 'Reduce_Move_Order_Quantity');
1417 DEBUG('l_inventory_item_id : ' || l_inventory_item_id, 'Reduce_Move_Order_Quantity');
1418 DEBUG('l_mo_uom_code : ' || l_mo_uom_code, 'Reduce_Move_Order_Quantity');
1419 DEBUG('l_txn_source_line_id : ' || l_txn_source_line_id, 'Reduce_Move_Order_Quantity');
1420 END IF;
1421
1422 IF c_line_info%NOTFOUND
1423 THEN
1424 IF (l_debug = 1) THEN
1425 DEBUG('Move order line not found', 'Reduce_Move_Order_Quantity');
1426 END IF;
1427 CLOSE c_line_info;
1428 RAISE fnd_api.g_exc_error;
1429 END IF;
1430
1431 IF c_line_info%ISOPEN
1432 THEN
1433 CLOSE c_line_info;
1434 END IF;
1435
1436 -- Call Cancel MO Line when Reduction Quantity > Quantity
1437 /*Bug#5095840. In the below IF statement, added the comparision of
1438 l_reduction_quantity with l_required_quantity as well.*/
1439 IF l_reduction_quantity >= l_quantity OR
1440 l_reduction_quantity >= l_required_quantity
1441 THEN
1442 cancel_move_order_line
1443 ( x_return_status => l_return_status
1444 , x_msg_count => x_msg_count
1445 , x_msg_data => x_msg_data
1446 , p_line_id => p_line_id
1447 , p_delete_reservations => 'Y'
1448 , p_txn_source_line_id => p_txn_source_line_id
1449 );
1450
1451 IF l_return_status = fnd_api.g_ret_sts_error THEN
1452 RAISE fnd_api.g_exc_error;
1453 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1454 RAISE fnd_api.g_exc_unexpected_error;
1455 END IF;
1456 END IF;
1457
1458 --
1459 -- If not all of the move order quantity is detailed, we must reduce
1460 -- reservation quantity by the same amount that we reduce move order
1461 -- quantity. The reservations which are eligible to be updated at this
1462 -- point are the reservations where rsv quantity > detailed quantity.
1463 --
1464 IF l_quantity > l_quantity_detailed
1465 THEN
1466 -- {
1467 l_quantity_to_delete := l_quantity - l_quantity_detailed;
1468 l_sec_quantity_to_delete := l_sec_quantity
1469 - l_sec_quantity_detailed; --INVCONV
1470 IF l_reduction_quantity < l_quantity_to_delete
1471 THEN
1472 l_quantity_to_delete := l_reduction_quantity;
1473 l_sec_quantity_to_delete := l_sec_reduction_quantity; --INVCONV
1474 l_reduction_quantity := 0;
1475 l_sec_reduction_quantity := 0; --INVCONV
1476 ELSE
1477 l_reduction_quantity := l_reduction_quantity - l_quantity_to_delete;
1478 l_sec_reduction_quantity := l_sec_reduction_quantity
1479 - l_sec_quantity_to_delete; --INVCONV
1480 END IF;
1481
1482 IF (l_debug = 1) THEN
1483 DEBUG('l_reduction_quantity : ' || l_reduction_quantity, 'Reduce_Move_Order_Quantity');
1484 DEBUG('l_quantity_to_delete : ' || l_quantity_to_delete, 'Reduce_Move_Order_Quantity');
1485 END IF;
1486 l_quantity := l_quantity - l_quantity_to_delete; /*Bug#5095840*/
1487 -- find primary qty to delete
1488 OPEN c_primary_uom;
1489 FETCH c_primary_uom INTO l_primary_uom_code;
1490
1491 IF c_primary_uom%NOTFOUND THEN
1492 IF (l_debug = 1) THEN
1493 DEBUG('Item not found', 'Reduce_Move_Order_Quantity');
1494 END IF;
1495
1496 CLOSE c_primary_uom;
1497 RAISE fnd_api.g_exc_unexpected_error;
1498 END IF;
1499
1500 CLOSE c_primary_uom;
1501
1502 IF l_primary_uom_code <> l_mo_uom_code
1503 THEN
1504 l_prim_quantity_to_delete := inv_convert.inv_um_convert
1505 ( l_inventory_item_id
1506 , NULL
1507 , l_quantity_to_delete
1508 , l_mo_uom_code
1509 , l_primary_uom_code
1510 , NULL
1511 , NULL
1512 );
1513 IF (l_prim_quantity_to_delete = -99999)
1514 THEN
1515 IF (l_debug = 1) THEN
1516 DEBUG('Cannot convert uom to primary uom', 'Reduce_Move_Order_Quantity');
1517 END IF;
1518
1519 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
1520 fnd_message.set_token('UOM', l_primary_uom_code);
1521 fnd_message.set_token('ROUTINE', 'Reduce Move Order Quantity');
1522 fnd_msg_pub.ADD;
1523 RAISE fnd_api.g_exc_unexpected_error;
1524 END IF;
1525 ELSE
1526 l_prim_quantity_to_delete := l_quantity_to_delete;
1527 END IF;
1528
1529 l_sec_qty_to_delete := l_sec_quantity_to_delete; --INVCONV
1530
1531 IF (l_debug = 1) THEN
1532 DEBUG('l_prim_quantity_to_delete: ' || l_prim_quantity_to_delete, 'Reduce_Move_Order_Quantity');
1533 DEBUG('l_txn_source_line_id: ' || l_txn_source_line_id, 'Reduce_Move_Order_Quantity');
1534 END IF;
1535
1536 -- we query by the sales order line id. If that value is not
1537 -- passed in, we need to get it from shipping table
1538 IF p_txn_source_line_id IS NOT NULL THEN
1539 l_txn_source_line_id := p_txn_source_line_id;
1540 ELSE /*Bug#5095840. Added this ELSE part*/
1541 OPEN c_txn_source_line;
1542 FETCH c_txn_source_line INTO l_txn_source_line_id;
1543 IF c_txn_source_line%NOTFOUND THEN
1544 CLOSE c_txn_source_line;
1545 IF ( l_debug = 1) THEN
1546 DEBUG('Did Not Find Any Sales Order Line', 'Reduce_Move_Order_Quantity');
1547 END IF;
1548 RAISE No_Data_Found;
1549 END IF;
1550 CLOSE c_txn_source_line;
1551 END IF;
1552
1553 OPEN c_reservations;
1554 LOOP
1555 -- {
1556 EXIT WHEN l_prim_quantity_to_delete <= 0;
1557 FETCH c_reservations INTO l_reservation_id;
1558 EXIT WHEN c_reservations%NOTFOUND;
1559 l_rsv_rec.reservation_id := l_reservation_id;
1560
1561 -- query reservation
1562 inv_reservation_pvt.query_reservation
1563 ( p_api_version_number => 1.0
1564 , p_init_msg_lst => fnd_api.g_false
1565 , x_return_status => l_return_status
1566 , x_msg_count => x_msg_count
1567 , x_msg_data => x_msg_data
1568 , p_query_input => l_rsv_rec
1569 , x_mtl_reservation_tbl => l_rsv_tbl
1570 , x_mtl_reservation_tbl_count => l_rsv_count
1571 , x_error_code => l_error_code
1572 );
1573
1574 IF l_return_status = fnd_api.g_ret_sts_error THEN
1575 IF (l_debug = 1) THEN
1576 DEBUG('Query reservation returned error'
1577 ,'Reduce_Move_Order_Quantity');
1578 END IF;
1579
1580 RAISE fnd_api.g_exc_error;
1581 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
1582 THEN
1583 IF (l_debug = 1) THEN
1584 DEBUG('Query reservation returned unexpected error'
1585 ,'Reduce_Move_Order_Quantity');
1586 END IF;
1587
1588 RAISE fnd_api.g_exc_unexpected_error;
1589 END IF;
1590
1591 l_update_rec := l_rsv_tbl(1);
1592 l_update_rec.reservation_quantity := NULL;
1593
1594 -- INVCONV - Need to initialize secondary_qty
1595 l_update_rec.secondary_reservation_quantity := NULL;
1596 l_max_delete_quantity := l_update_rec.primary_reservation_quantity
1597 - l_update_rec.detailed_quantity;
1598 l_max_delete_sec_quantity := l_update_rec.secondary_reservation_quantity
1599 - l_update_rec.secondary_detailed_quantity; --INVCONV
1600
1601 IF (l_debug = 1) THEN
1602 DEBUG('l_max_delete_quantity::' || l_max_delete_quantity, 'Reduce_Move_Order_Quantity');
1603 END IF;
1604
1605 -- determine new reservation quantity
1606 IF l_max_delete_quantity > l_prim_quantity_to_delete
1607 THEN
1608 l_update_rec.primary_reservation_quantity
1609 := l_update_rec.primary_reservation_quantity
1610 - l_prim_quantity_to_delete;
1611 l_prim_quantity_to_delete := 0;
1612 l_sec_qty_to_delete := 0; --INVCONV
1613 l_update_rec.secondary_reservation_quantity
1614 := l_update_rec.secondary_reservation_quantity
1615 - l_sec_quantity_to_delete; --INVCONV
1616 ELSE
1617 l_prim_quantity_to_delete := l_prim_quantity_to_delete
1618 - l_max_delete_quantity;
1619 l_update_rec.primary_reservation_quantity
1620 := l_update_rec.primary_reservation_quantity
1621 - l_max_delete_quantity;
1622 l_sec_qty_to_delete := l_sec_qty_to_delete
1623 - l_max_delete_sec_quantity; --INVCONV
1624 l_update_rec.secondary_reservation_quantity
1625 := l_update_rec.secondary_reservation_quantity
1626 - l_max_delete_sec_quantity; --INVCONV
1627 END IF;
1628
1629 -- INVCONV - Make sure Qty2 are NULL if not present
1630 IF ( l_update_rec.secondary_uom_code IS NULL ) THEN
1631 l_update_rec.secondary_reservation_quantity := NULL;
1632 l_update_rec.secondary_detailed_quantity := NULL;
1633 END IF;
1634
1635 IF (l_debug = 1) THEN
1636 DEBUG('l_update_rec.primary_reservation_quantity::' || l_update_rec.primary_reservation_quantity, 'Reduce_Move_Order_Quantity');
1637 DEBUG('l_update_rec.detailed_quantity::' || l_update_rec.detailed_quantity, 'Reduce_Move_Order_Quantity');
1638 END IF;
1639
1640 -- update reservation
1641 inv_reservation_pub.update_reservation
1642 ( p_api_version_number => 1.0
1643 , p_init_msg_lst => fnd_api.g_false
1644 , x_return_status => l_return_status
1645 , x_msg_count => x_msg_count
1646 , x_msg_data => x_msg_data
1647 , p_original_rsv_rec => l_rsv_tbl(1)
1648 , p_to_rsv_rec => l_update_rec
1649 , p_original_serial_number => l_dummy_sn
1650 , p_to_serial_number => l_dummy_sn
1651 , p_validation_flag => fnd_api.g_true
1652 );
1653
1654 IF l_return_status = fnd_api.g_ret_sts_error THEN
1655 IF (l_debug = 1) THEN
1656 DEBUG('Update reservation returned error'
1657 ,'Reduce_Move_Order_Quantity');
1658 END IF;
1659
1660 RAISE fnd_api.g_exc_error;
1661 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
1662 THEN
1663 IF (l_debug = 1) THEN
1664 DEBUG('Update reservation returned unexpected error'
1665 ,'Reduce_Move_Order_Quantity');
1666 END IF;
1667
1668 RAISE fnd_api.g_exc_unexpected_error;
1669 END IF;
1670 -- }
1671 END LOOP;
1672
1673 CLOSE c_reservations;
1674 -- }
1675 END IF;
1676
1677 IF l_reduction_quantity > 0
1678 THEN
1679 -- {
1680 -- Check if WMS is installed
1681 IF inv_install.adv_inv_installed(l_organization_id)
1682 THEN
1683 -- {
1684 OPEN c_undispatched_tasks;
1685 LOOP
1686 -- {
1687 EXIT WHEN l_reduction_quantity <= 0;
1688 FETCH c_undispatched_tasks
1689 INTO l_transaction_temp_id
1690 , l_task_qty
1691 , l_sec_qty
1692 , l_primary_quantity
1693 , l_reservation_id;
1694 IF (l_debug = 1) THEN
1695 DEBUG('l_task_qty: ' || l_task_qty, 'Reduce_Move_Order_Quantity');
1696 DEBUG('l_transaction_temp_id: ' || l_transaction_temp_id, 'Reduce_Move_Order_Quantity');
1697 DEBUG('l_primary_quantity: ' || l_primary_quantity, 'Reduce_Move_Order_Quantity');
1698 DEBUG('l_reservation_id: ' || l_reservation_id, 'Reduce_Move_Order_Quantity');
1699 END IF;
1700 EXIT WHEN c_undispatched_tasks%NOTFOUND;
1701
1702 IF l_task_qty > l_reduction_quantity THEN
1703 l_quantity_to_delete := l_reduction_quantity;
1704 l_sec_quantity_to_delete := l_sec_reduction_quantity; --INVCONV
1705 ELSE
1706 l_quantity_to_delete := l_task_qty;
1707 l_sec_quantity_to_delete := l_sec_qty; --INVCONV
1708 END IF;
1709
1710 l_reduction_quantity := l_reduction_quantity
1711 - l_quantity_to_delete;
1712 l_sec_reduction_quantity := l_sec_reduction_quantity
1713 - l_sec_quantity_to_delete; --INVCONV
1714
1715 IF (l_debug = 1) THEN
1716 DEBUG('calling reduce_rsv_allocation with l_reduction_quantity: ' || l_reduction_quantity, 'Reduce_Move_Order_Quantity');
1717 DEBUG('l_quantity_to_delete: ' || l_quantity_to_delete, 'Reduce_Move_Order_Quantity');
1718 DEBUG('l_transaction_temp_id: ' || l_transaction_temp_id, 'Reduce_Move_Order_Quantity');
1719 END IF;
1720 -- Removing reservation and allocation for this task
1721 reduce_rsv_allocation
1722 ( x_return_status => l_return_status
1723 , x_msg_count => x_msg_count
1724 , x_msg_data => x_msg_data
1725 , p_transaction_temp_id => l_transaction_temp_id
1726 , p_quantity_to_delete => l_quantity_to_delete
1727 , p_sec_quantity_to_delete => l_sec_quantity_to_delete
1728 );
1729
1730 l_deleted_quantity := l_deleted_quantity
1731 + l_quantity_to_delete;
1732 l_sec_deleted_quantity := l_sec_deleted_quantity
1733 + l_sec_quantity_to_delete; --INCONV
1734 -- }
1735 IF (l_debug = 1) THEN
1736 DEBUG('l_deleted_quantity: ' || l_deleted_quantity, 'Reduce_Move_Order_Quantity');
1737 DEBUG('l_reduction_quantity: ' || l_reduction_quantity, 'Reduce_Move_Order_Quantity');
1738 END IF;
1739 END LOOP; -- loop through each task
1740
1741 CLOSE c_undispatched_tasks;
1742 -- }
1743 END IF; -- wms installed
1744 -- }
1745 END IF; -- allocations exists
1746
1747 --
1748 -- No matter what happens above, we want to reduce the shipping
1749 -- quantity by the original reduction quantity. We know shipping qty
1750 -- is greater than reduction quantity, since we checked that at the
1751 -- beginning of the procedure.
1752 --
1753
1754 l_required_quantity := l_required_quantity - p_reduction_quantity; /*Bug#5095840*/
1755 l_sec_required_quantity := l_sec_required_quantity - p_reduction_quantity; /*Bug#5095840*/
1756 l_quantity := l_quantity - l_deleted_quantity;/*Bug#5095840*/
1757 l_sec_quantity := l_sec_quantity - l_sec_deleted_quantity; --INVCONV/*Bug#5095840*/
1758 --Bug 5054658
1759 --Decremented quantity_detailed in addition to quantity
1760 --and update the move order line with the decremented quantity
1761 IF (NVL(l_quantity_detailed, 0) > 0) THEN
1762 IF l_quantity_detailed > p_reduction_quantity THEN
1763 l_quantity_detailed := l_quantity_detailed - p_reduction_quantity;
1764 END IF;
1765 ELSE
1766 l_quantity_detailed := 0;
1767 END IF;
1768
1769 IF (NVL(l_sec_quantity_detailed, 0) > 0) THEN
1770 IF l_sec_quantity_detailed > p_sec_reduction_quantity THEN
1771 l_sec_quantity_detailed := l_sec_quantity_detailed - p_sec_reduction_quantity;
1772 END IF;
1773 ELSE
1774 l_sec_quantity_detailed := 0;
1775 END IF;
1776
1777 IF (l_debug = 1) THEN
1778 DEBUG(' update MTRL with quantity: ' || l_quantity ||
1779 ', quantity_detailed: ' || l_quantity_detailed, 'Reduce_Move_Order_Quantity');
1780 END IF;
1781
1782 -- Update line status, quantity, and required_quantity
1783 /*Bug#5095840. Modified the below UPDATE statement to update
1784 the 'required_quantity' and 'secondary_required_quantity' with
1785 'l_required_quantity' and 'l_sec_required_quantity' respectively
1786 rather than with NULL. Also updation of 'quantity_detailed' is
1787 commented.*/
1788
1789 UPDATE mtl_txn_request_lines
1790 SET quantity = l_quantity
1791 , required_quantity = l_required_quantity
1792 , secondary_quantity = l_sec_quantity --INVCONV
1793 , secondary_required_quantity = l_sec_required_quantity --INVCONV
1794 -- , quantity_detailed = l_quantity_detailed
1795 , secondary_quantity_detailed = l_sec_quantity_detailed
1796 WHERE line_id = p_line_id;
1797
1798 -- }
1799 ELSE -- MO line is null, so process xdock reservations
1800 -- {
1801 -- {{
1802 -- Reduce quantity on a sales/internal order line
1803 -- that is pegged to material not yet received
1804 -- Reservations are reduced or deleted }}
1805 --
1806 BEGIN
1807 OPEN c_lock_wdd (p_delivery_detail_id);
1808 FETCH c_lock_wdd INTO l_wdd_rec;
1809 CLOSE c_lock_wdd;
1810 EXCEPTION
1811 WHEN record_locked THEN
1812 IF (l_debug = 1)
1813 THEN
1814 DEBUG('Unable to lock WDD: ' || to_char(p_delivery_detail_id)
1815 ,'Reduce_Move_Order_Quantity');
1816 END IF;
1817 IF c_lock_wdd%ISOPEN
1818 THEN
1819 CLOSE c_lock_wdd;
1820 END IF;
1821 fnd_message.set_name('INV', 'INV_WDD_LOCK_FAIL');
1822 fnd_msg_pub.ADD;
1823 fnd_msg_pub.count_and_get
1824 ( p_count => x_msg_count
1825 , p_data => x_msg_data
1826 , p_encoded => 'F'
1827 );
1828 RAISE fnd_api.g_exc_error;
1829 END;
1830
1831 IF (l_debug = 1)
1832 THEN
1833 DEBUG('Locked WDD: ' || to_char(p_delivery_detail_id)
1834 ,'Reduce_Move_Order_Quantity');
1835 END IF;
1836
1837 --
1838 -- Lock and fetch reservations for the WDD record
1839 -- For each record:
1840 -- If rsv qty (after conv to WDD UOM) >
1841 -- remaining qty to reduce
1842 -- Reduce qty and exit
1843 -- else
1844 -- Delete rsv, decrement remaining reduction qty
1845 --
1846 l_xdock_rsv_tbl.DELETE;
1847 BEGIN
1848 OPEN c_xdock_rsv (p_delivery_detail_id);
1849 FETCH c_xdock_rsv BULK COLLECT INTO l_xdock_rsv_tbl;
1850 CLOSE c_xdock_rsv;
1851 EXCEPTION
1852 WHEN record_locked THEN
1853 DEBUG('Unable to lock xdock rsv record(s)'
1854 ,'Reduce_Move_Order_Quantity');
1855 IF c_xdock_rsv%ISOPEN
1856 THEN
1857 CLOSE c_xdock_rsv;
1858 END IF;
1859 fnd_message.set_name('WMS', 'INV_RSV_LOCK_FAIL');
1860 fnd_msg_pub.ADD;
1861 RAISE fnd_api.g_exc_error;
1862 END;
1863
1864 wms_xdock_utils_pvt.g_demand_triggered := TRUE;
1865
1866 l_rsv_index := l_xdock_rsv_tbl.FIRST;
1867 LOOP
1868 -- {
1869 IF (l_xdock_rsv_tbl.COUNT = 0) THEN
1870 IF (l_debug = 1) THEN
1871 DEBUG('No xdock rsv records to process'
1872 ,'Reduce_Move_Order_Quantity');
1873 END IF;
1874 EXIT;
1875 END IF;
1876
1877 EXIT WHEN l_reduction_quantity <= 0;
1878
1879 l_rsv_rec.reservation_id := l_xdock_rsv_tbl(l_rsv_index);
1880
1881 IF (l_debug = 1) THEN
1882 DEBUG('Reservation ID = ' || to_char(l_rsv_rec.reservation_id)
1883 ,'Reduce_Move_Order_Quantity');
1884 END IF;
1885
1886 -- query reservation
1887 inv_reservation_pvt.query_reservation
1888 ( p_api_version_number => 1.0
1889 , p_init_msg_lst => fnd_api.g_false
1890 , x_return_status => x_return_status
1891 , x_msg_count => x_msg_count
1892 , x_msg_data => x_msg_data
1893 , p_query_input => l_rsv_rec
1894 , x_mtl_reservation_tbl => l_rsv_tbl
1895 , x_mtl_reservation_tbl_count => l_rsv_count
1896 , x_error_code => l_error_code
1897 );
1898
1899 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1900 IF (l_debug = 1) THEN
1901 DEBUG('Error: Query Reservations returned '
1902 || x_return_status
1903 ,'Reduce_Move_Order_Quantity');
1904 END IF;
1905 IF x_return_status = fnd_api.g_ret_sts_error
1906 THEN
1907 fnd_message.set_name('INV','INV_QRY_RSV_FAILED');
1908 fnd_msg_pub.ADD;
1909 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1910 p_data => x_msg_data,
1911 p_encoded => 'F');
1912 RAISE fnd_api.g_exc_error;
1913 ELSE
1914 RAISE fnd_api.g_exc_unexpected_error;
1915 END IF;
1916 END IF;
1917
1918 IF l_rsv_count <= 0 THEN
1919 IF (l_debug = 1) THEN
1920 DEBUG('Error: Query Reservations returned Reservation Count 0'
1921 ,'Reduce_Move_Order_Quantity');
1922 END IF;
1923 fnd_message.set_name('INV','INV_NO_RSVS_FOUND');
1924 fnd_msg_pub.ADD;
1925
1926 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1927 p_data => x_msg_data,
1928 p_encoded => 'F');
1929 RAISE fnd_api.g_exc_error;
1930 END IF;
1931
1932 l_rsv_rec2 := l_rsv_tbl(1);
1933
1934 -- Convert reduction qty to current RSV UOM
1935 IF l_wdd_rec.requested_quantity_uom
1936 = l_rsv_rec2.reservation_uom_code
1937 THEN
1938 l_reduc_qty_conv := l_reduction_quantity;
1939 ELSE
1940 inv_convert.inv_um_conversion
1941 ( from_unit => l_wdd_rec.requested_quantity_uom
1942 , to_unit => l_rsv_rec2.reservation_uom_code
1943 , item_id => l_rsv_rec2.inventory_item_id
1944 , uom_rate => l_conv_rate
1945 );
1946 IF (NVL(l_conv_rate,0) <= 0)
1947 THEN
1948 IF (l_debug = 1)
1949 THEN
1950 DEBUG('Invalid conversion factor: ' || l_conv_rate
1951 ,'Reduce_Move_Order_Quantity');
1952 END IF;
1953 RAISE fnd_api.g_exc_unexpected_error;
1954 ELSE
1955 l_reduc_qty_conv := ROUND( l_conv_rate * l_reduction_quantity
1956 , g_conv_precision
1957 );
1958 END IF;
1959 END IF;
1960
1961 IF l_rsv_rec2.reservation_quantity > l_reduc_qty_conv
1962 THEN
1963 -- {
1964 -- Determine primary qty to reduce
1965 IF l_wdd_rec.requested_quantity_uom
1966 = l_rsv_rec2.primary_uom_code
1967 THEN
1968 l_prim_qty_conv := l_reduction_quantity;
1969 ELSE
1970 inv_convert.inv_um_conversion
1971 ( from_unit => l_wdd_rec.requested_quantity_uom
1972 , to_unit => l_rsv_rec2.primary_uom_code
1973 , item_id => l_rsv_rec2.inventory_item_id
1974 , uom_rate => l_conv_rate
1975 );
1976 IF (NVL(l_conv_rate,0) <= 0)
1977 THEN
1978 IF (l_debug = 1)
1979 THEN
1980 DEBUG('Invalid conversion factor: ' || l_conv_rate
1981 ,'Reduce_Move_Order_Quantity');
1982 END IF;
1983 RAISE fnd_api.g_exc_unexpected_error;
1984 ELSE
1985 l_prim_qty_conv := ROUND( l_conv_rate * l_reduction_quantity
1986 , g_conv_precision
1987 );
1988 END IF;
1989 END IF;
1990
1991 -- Convert secondary qty if required
1992 IF NVL(l_sec_reduction_quantity,0) > 0
1993 AND
1994 l_wdd_rec.requested_quantity_uom2 IS NOT NULL
1995 THEN
1996 IF l_wdd_rec.requested_quantity_uom2
1997 = l_rsv_rec2.secondary_uom_code
1998 THEN
1999 l_sec_reduc_qty_conv := l_sec_reduction_quantity;
2000 ELSE
2001 inv_convert.inv_um_conversion
2002 ( from_unit => l_wdd_rec.requested_quantity_uom2
2003 , to_unit => l_rsv_rec2.secondary_uom_code
2004 , item_id => l_rsv_rec2.inventory_item_id
2005 , uom_rate => l_conv_rate
2006 );
2007 IF (NVL(l_conv_rate,0) <= 0)
2008 THEN
2009 IF (l_debug = 1)
2010 THEN
2011 DEBUG('Invalid conversion factor: ' || l_conv_rate
2012 ,'Reduce_Move_Order_Quantity');
2013 END IF;
2014 RAISE fnd_api.g_exc_unexpected_error;
2015 ELSE
2016 l_sec_reduc_qty_conv := ROUND( l_conv_rate * l_sec_reduction_quantity
2017 , g_conv_precision
2018 );
2019 END IF;
2020 END IF;
2021 END IF; -- end IF secondary UOM specified
2022
2023 l_update_rec := l_rsv_tbl(1);
2024 l_update_rec.reservation_quantity
2025 := l_update_rec.reservation_quantity - l_reduc_qty_conv;
2026 l_update_rec.primary_reservation_quantity
2027 := l_update_rec.primary_reservation_quantity - l_prim_qty_conv;
2028
2029 IF l_update_rec.secondary_uom_code IS NOT NULL
2030 THEN
2031 l_update_rec.secondary_reservation_quantity
2032 := l_update_rec.secondary_reservation_quantity
2033 - l_sec_reduc_qty_conv;
2034 END IF;
2035
2036 inv_reservation_pub.update_reservation
2037 ( p_api_version_number => 1.0
2038 , p_init_msg_lst => fnd_api.g_false
2039 , x_return_status => x_return_status
2040 , x_msg_count => x_msg_count
2041 , x_msg_data => x_msg_data
2042 , p_original_rsv_rec => l_rsv_tbl(1)
2043 , p_to_rsv_rec => l_update_rec
2044 , p_original_serial_number => l_dummy_sn
2045 , p_to_serial_number => l_dummy_sn
2046 , p_validation_flag => fnd_api.g_true
2047 );
2048
2049 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2050 IF (l_debug = 1) THEN
2051 DEBUG('Error: Update Reservations returned '
2052 || x_return_status
2053 ,'Reduce_Move_Order_Quantity');
2054 END IF;
2055 IF x_return_status = fnd_api.g_ret_sts_error
2056 THEN
2057 fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
2058 fnd_msg_pub.ADD;
2059 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2060 p_data => x_msg_data,
2061 p_encoded => 'F');
2062 RAISE fnd_api.g_exc_error;
2063 ELSE
2064 RAISE fnd_api.g_exc_unexpected_error;
2065 END IF;
2066 END IF;
2067
2068 l_reduction_quantity := 0;
2069 l_sec_reduction_quantity := 0;
2070 -- }
2071 ELSE -- RSV qty <= qty to reduce, so delete RSV
2072 -- {
2073 IF l_rsv_rec2.reservation_quantity = l_reduc_qty_conv
2074 THEN
2075 l_reduction_quantity := 0;
2076 l_sec_reduction_quantity := 0;
2077 ELSE
2078 -- {
2079 -- Convert RSV qty to WDD UOM
2080 IF l_wdd_rec.requested_quantity_uom
2081 = l_rsv_rec2.reservation_uom_code
2082 THEN
2083 l_reduction_quantity := l_reduction_quantity
2084 - l_rsv_rec2.reservation_quantity;
2085 ELSE
2086 inv_convert.inv_um_conversion
2087 ( from_unit => l_rsv_rec2.reservation_uom_code
2088 , to_unit => l_wdd_rec.requested_quantity_uom
2089 , item_id => l_rsv_rec2.inventory_item_id
2090 , uom_rate => l_conv_rate
2091 );
2092 IF (NVL(l_conv_rate,0) <= 0)
2093 THEN
2094 IF (l_debug = 1)
2095 THEN
2096 DEBUG('Invalid conversion factor: ' || l_conv_rate
2097 ,'Reduce_Move_Order_Quantity');
2098 END IF;
2099 RAISE fnd_api.g_exc_unexpected_error;
2100 ELSE
2101 l_reduction_quantity := l_reduction_quantity
2102 - ROUND( l_conv_rate
2103 * l_rsv_rec2.reservation_quantity
2104 , g_conv_precision
2105 );
2106 END IF;
2107 END IF;
2108
2109 -- Convert secondary qty if required
2110 IF NVL(l_sec_reduction_quantity,0) > 0
2111 AND
2112 l_wdd_rec.requested_quantity_uom2 IS NOT NULL
2113 THEN
2114 IF l_wdd_rec.requested_quantity_uom2
2115 = l_rsv_rec2.secondary_uom_code
2116 THEN
2117 l_sec_reduction_quantity
2118 := l_sec_reduction_quantity
2119 - l_rsv_rec2.secondary_reservation_quantity;
2120 ELSE
2121 inv_convert.inv_um_conversion
2122 ( from_unit => l_rsv_rec2.secondary_uom_code
2123 , to_unit => l_wdd_rec.requested_quantity_uom2
2124 , item_id => l_rsv_rec2.inventory_item_id
2125 , uom_rate => l_conv_rate
2126 );
2127 IF (NVL(l_conv_rate,0) <= 0)
2128 THEN
2129 IF (l_debug = 1)
2130 THEN
2131 DEBUG('Invalid conversion factor: ' || l_conv_rate
2132 ,'Reduce_Move_Order_Quantity');
2133 END IF;
2134 RAISE fnd_api.g_exc_unexpected_error;
2135 ELSE
2136 l_sec_reduction_quantity := l_sec_reduction_quantity
2137 - ROUND( l_conv_rate
2138 * l_rsv_rec2.secondary_reservation_quantity
2139 , g_conv_precision
2140 );
2141 END IF;
2142 END IF; -- end IF secondary UOMs match
2143 END IF; -- end IF secondary UOM specified
2144 -- }
2145 END IF;
2146
2147 IF (l_debug = 1) THEN
2148 DEBUG('About to delete reservation ID: '
2149 || to_char(l_rsv_rec2.reservation_id)
2150 ,'Reduce_Move_Order_Quantity');
2151 END IF;
2152
2153 inv_reservation_pub.delete_reservation
2154 ( p_api_version_number => 1.0
2155 , p_init_msg_lst => fnd_api.g_false
2156 , x_return_status => x_return_status
2157 , x_msg_count => x_msg_count
2158 , x_msg_data => x_msg_data
2159 , p_rsv_rec => l_rsv_rec2
2160 , p_serial_number => l_serial_tbl
2161 );
2162
2163 IF x_return_status <> fnd_api.g_ret_sts_success
2164 THEN
2165 IF (l_debug = 1)
2166 THEN
2167 DEBUG('inv_reservation_pub.delete_reservation returned an error status: '
2168 || x_return_status, 'Reduce_Move_Order_Quantity');
2169 END IF;
2170
2171 IF x_return_status = fnd_api.g_ret_sts_error
2172 THEN
2173 fnd_msg_pub.count_and_get
2174 ( p_count => x_msg_count
2175 , p_data => x_msg_data
2176 , p_encoded => 'F'
2177 );
2178 IF (l_debug = 1)
2179 THEN
2180 DEBUG('x_msg_data: ' || x_msg_data
2181 ,'Reduce_Move_Order_Quantity');
2182 END IF;
2183 RAISE fnd_api.g_exc_error;
2184 ELSE
2185 RAISE fnd_api.g_exc_unexpected_error;
2186 END IF;
2187 ELSE
2188 IF (l_debug = 1)
2189 THEN
2190 DEBUG('inv_reservation_pub.delete_reservation returned success'
2191 ,'Reduce_Move_Order_Quantity');
2192 END IF;
2193 END IF;
2194 -- }
2195 END IF;
2196
2197 EXIT WHEN l_rsv_index = l_xdock_rsv_tbl.LAST;
2198 l_rsv_index := l_xdock_rsv_tbl.NEXT(l_rsv_index);
2199 -- }
2200 END LOOP;
2201
2202 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
2203
2204 l_xdock_rsv_tbl.DELETE;
2205 -- }
2206 END IF;
2207
2208 -- {{
2209 -- END reduce_move_order_quantity }}
2210 --
2211
2212 EXCEPTION
2213 WHEN fnd_api.g_exc_error THEN
2214 ROLLBACK TO reducemo_sp;
2215 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
2216 x_return_status := fnd_api.g_ret_sts_error;
2217 fnd_msg_pub.count_and_get
2218 ( p_count => x_msg_count
2219 , p_data => x_msg_data
2220 , p_encoded => 'F'
2221 );
2222 IF (l_debug = 1)
2223 THEN
2224 DEBUG('x_msg_data: ' || x_msg_data
2225 ,'Reduce_Move_Order_Quantity');
2226 END IF;
2227
2228 WHEN OTHERS THEN
2229 ROLLBACK TO reducemo_sp;
2230 wms_xdock_utils_pvt.g_demand_triggered := FALSE;
2231 IF (l_debug = 1) THEN
2232 DEBUG('Others error' || SQLERRM
2233 ,'Reduce_Move_Order_Quantity');
2234 END IF;
2235 x_return_status := fnd_api.g_ret_sts_unexp_error;
2236
2237 END reduce_move_order_quantity;
2238
2239
2240 --Procedure
2241 --reduce_rsv_allocation
2242 --Description
2243 -- This procedure is called from WMSTSKUB.pls and
2244 --inv_mo_cancel_pvt.reduce_move_order_quantity .Given the
2245 --transaction_temp_id AND quantity TO DELETE it deletes/reduces allocations
2246
2247 PROCEDURE reduce_rsv_allocation(
2248 x_return_status OUT NOCOPY VARCHAR2
2249 , x_msg_count OUT NOCOPY NUMBER
2250 , x_msg_data OUT NOCOPY VARCHAR2
2251 , p_transaction_temp_id IN NUMBER
2252 , p_quantity_to_delete IN NUMBER
2253 , p_sec_quantity_to_delete IN NUMBER DEFAULT NULL --INVCONV
2254 , p_ato_serial_pick IN VARCHAR2 DEFAULT NULL --7190635 Added to check whether the call is for ATO serial picking
2255 ) IS
2256 l_reservation_id NUMBER;
2257 l_transaction_temp_id NUMBER;
2258 l_task_qty NUMBER;
2259 l_primary_quantity NUMBER;
2260 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
2261 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
2262 l_update_rec inv_reservation_global.mtl_reservation_rec_type;
2263 l_rsv_count NUMBER;
2264 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
2265 l_quantity_to_delete NUMBER;
2266 l_sec_quantity_to_delete NUMBER; --INVCONV
2267 l_sec_qty NUMBER; --INVCONV
2268 l_sec_deleted_quantity NUMBER; --INVCONV
2269 l_sec_qty_to_delete NUMBER; --INVCONV
2270 l_mo_uom_code VARCHAR2(3);
2271 l_primary_uom_code VARCHAR2(3);
2272 l_inventory_item_id NUMBER;
2273 l_prim_quantity_to_delete NUMBER;
2274 l_organization_id NUMBER;
2275 l_deleted_quantity NUMBER;
2276 l_return_status VARCHAR2(1);
2277 l_error_code NUMBER;
2278 l_ato_serial_pick VARCHAR2(1); -- Bug 7190635
2279 l_retain_ato_profile VARCHAR2(1) := NVL(fnd_profile.VALUE('WSH_RETAIN_ATO_RESERVATIONS'),'N'); --Bug 7190635
2280
2281 CURSOR c_primary_uom IS
2282 SELECT primary_uom_code
2283 FROM mtl_system_items
2284 WHERE organization_id = l_organization_id
2285 AND inventory_item_id = l_inventory_item_id;
2286
2287 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2288 BEGIN
2289 IF (l_debug = 1) THEN
2290 DEBUG('Setting Savepoint', 'reduce_rsv_allocation');
2291 END IF;
2292
2293 SAVEPOINT del_rsv_all_sp;
2294 l_transaction_temp_id := p_transaction_temp_id;
2295 l_deleted_quantity := 0;
2296 l_quantity_to_delete := p_quantity_to_delete;
2297 l_sec_deleted_quantity := 0; --INVCONV
2298 l_sec_quantity_to_delete := p_sec_quantity_to_delete; --INVCONV
2299 l_ato_serial_pick := NVL(p_ato_serial_pick,'N'); --Bug 7190635
2300
2301 IF (l_debug = 1) THEN
2302 DEBUG(' transaction_temp_id:' || l_transaction_temp_id, 'reduce_rsv_allocation');
2303 DEBUG('quantity_to_delete:' || l_quantity_to_delete, 'reduce_rsv_allocation');
2304 DEBUG('sec_quantity_to_delete:' || l_sec_quantity_to_delete, 'reduce_rsv_allocation');
2305 END IF;
2306
2307 SELECT ABS(mmtt.transaction_quantity)
2308 , ABS(mmtt.primary_quantity)
2309 , ABS(mmtt.secondary_transaction_quantity) --INVCONV
2310 , mmtt.reservation_id
2311 , mmtt.organization_id
2312 , mmtt.inventory_item_id
2313 , mtrl.uom_code
2314 -- INVCONV correcting the orders
2315 INTO l_task_qty
2316 , l_primary_quantity
2317 , l_sec_qty --INVCONV
2318 , l_reservation_id
2319 , l_organization_id
2320 , l_inventory_item_id
2321 , l_mo_uom_code
2322 FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
2323 WHERE mmtt.transaction_temp_id = l_transaction_temp_id
2324 AND mmtt.move_order_line_id = mtrl.line_id;
2325
2326 -- find quantity to delete in primary UOM
2327 OPEN c_primary_uom;
2328 FETCH c_primary_uom INTO l_primary_uom_code;
2329
2330 IF c_primary_uom%NOTFOUND THEN
2331 IF (l_debug = 1) THEN
2332 DEBUG('Move order line not found', 'reduce_rsv_allocation');
2333 END IF;
2334
2335 CLOSE c_primary_uom;
2336 RAISE fnd_api.g_exc_unexpected_error;
2337 END IF;
2338
2339 CLOSE c_primary_uom;
2340
2341 IF (l_debug = 1) THEN
2342 DEBUG('before uom check', 'reduce_rsv_allocation');
2343 END IF;
2344
2345 IF l_primary_uom_code <> l_mo_uom_code THEN
2346 l_prim_quantity_to_delete :=
2347 inv_convert.inv_um_convert(l_inventory_item_id, NULL, l_quantity_to_delete, l_mo_uom_code, l_primary_uom_code, NULL, NULL);
2348
2349 IF (l_prim_quantity_to_delete = -99999) THEN
2350 IF (l_debug = 1) THEN
2351 DEBUG('Cannot convert uom to primary uom', 'reduce_rsv_allocation');
2352 END IF;
2353
2354 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
2355 fnd_message.set_token('UOM', l_primary_uom_code);
2356 fnd_message.set_token('ROUTINE', 'reduce_rsv_allocation');
2357 fnd_msg_pub.ADD;
2358 RAISE fnd_api.g_exc_unexpected_error;
2359 END IF;
2360 ELSE
2361 l_prim_quantity_to_delete := l_quantity_to_delete;
2362 END IF;
2363 l_sec_qty_to_delete := l_sec_quantity_to_delete; --INVCONV
2364 -- if the allocation corresponds to a reservation, we need to update
2365 -- the reservation
2366 IF l_ato_serial_pick = 'N' THEN --Bug 7190635
2367 IF l_reservation_id IS NOT NULL THEN
2368 l_rsv_rec.reservation_id := l_reservation_id;
2369
2370 IF (l_debug = 1) THEN
2371 DEBUG('query reservation', 'reduce_rsv_allocation');
2372 END IF;
2373
2374 -- query reservation
2375 inv_reservation_pvt.query_reservation(
2376 p_api_version_number => 1.0
2377 , p_init_msg_lst => fnd_api.g_false
2378 , x_return_status => l_return_status
2379 , x_msg_count => x_msg_count
2380 , x_msg_data => x_msg_data
2381 , p_query_input => l_rsv_rec
2382 , x_mtl_reservation_tbl => l_rsv_tbl
2383 , x_mtl_reservation_tbl_count => l_rsv_count
2384 , x_error_code => l_error_code
2385 );
2386
2387 IF l_return_status = fnd_api.g_ret_sts_error THEN
2388 IF (l_debug = 1) THEN
2389 DEBUG('Query reservation returned error', 'reduce_rsv_allocation');
2390 END IF;
2391
2392 RAISE fnd_api.g_exc_error;
2393 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2394 IF (l_debug = 1) THEN
2395 DEBUG('Query reservation returned unexpected error', 'reduce_rsv_allocation');
2396 END IF;
2397
2398 RAISE fnd_api.g_exc_unexpected_error;
2399 END IF;
2400
2401 l_update_rec := l_rsv_tbl(1);
2402
2403 -- update detailed quantity
2404 IF l_update_rec.detailed_quantity > l_prim_quantity_to_delete THEN
2405 l_update_rec.detailed_quantity := l_update_rec.detailed_quantity - l_prim_quantity_to_delete;
2406 l_update_rec.secondary_detailed_quantity := l_update_rec.secondary_detailed_quantity - l_sec_qty_to_delete; --INCONV
2407 ELSE
2408 l_update_rec.detailed_quantity := 0;
2409 l_update_rec.secondary_detailed_quantity := 0; --INCONV
2410 END IF;
2411
2412
2413 l_update_rec.reservation_quantity := NULL;
2414
2415 --set primary reservation quantity
2416 IF l_update_rec.primary_reservation_quantity > l_prim_quantity_to_delete THEN
2417 l_update_rec.primary_reservation_quantity := l_update_rec.primary_reservation_quantity - l_prim_quantity_to_delete;
2418 l_update_rec.secondary_reservation_quantity := l_update_rec.secondary_reservation_quantity - l_sec_qty_to_delete; --INVCONV
2419 ELSE -- delete entire reservation
2420 l_update_rec.primary_reservation_quantity := 0;
2421 l_update_rec.secondary_reservation_quantity := 0; --INVCONV
2422 END IF; -- rsv qty > task qty
2423
2424 IF (l_debug = 1) THEN
2425 DEBUG('update reservation', 'reduce_rsv_allocation');
2426 END IF;
2427
2428 -- INVCONV - Make sure Qty2 are NULL if nor present
2429 IF ( l_update_rec.secondary_uom_code IS NULL ) THEN
2430 l_update_rec.secondary_reservation_quantity := NULL;
2431 l_update_rec.secondary_detailed_quantity := NULL;
2432 END IF;
2433
2434 -- update reservations
2435 inv_reservation_pub.update_reservation(
2436 p_api_version_number => 1.0
2437 , p_init_msg_lst => fnd_api.g_false
2438 , x_return_status => l_return_status
2439 , x_msg_count => x_msg_count
2440 , x_msg_data => x_msg_data
2441 , p_original_rsv_rec => l_rsv_tbl(1)
2442 , p_to_rsv_rec => l_update_rec
2443 , p_original_serial_number => l_dummy_sn
2444 , p_to_serial_number => l_dummy_sn
2445 , p_validation_flag => fnd_api.g_true
2446 );
2447
2448 IF l_return_status = fnd_api.g_ret_sts_error THEN
2449 IF (l_debug = 1) THEN
2450 DEBUG('Update reservation returned error', 'reduce_rsv_allocation');
2451 END IF;
2452
2453 RAISE fnd_api.g_exc_error;
2454 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2455 IF (l_debug = 1) THEN
2456 DEBUG('Update reservation returned unexpected error', 'reduce_rsv_allocation');
2457 END IF;
2458
2459 RAISE fnd_api.g_exc_unexpected_error;
2460 END IF;
2461 END IF; -- reservation id is not null
2462 END IF; --Bug 7190635, l_ato_serial_pick is 'N'
2463
2464 IF (l_debug = 1) THEN
2465 DEBUG('Retain ATO reservation:'||l_retain_ato_profile, 'reduce_rsv_allocation');
2466 DEBUG('Quantity to delete:'||l_quantity_to_delete, 'reduce_rsv_allocation');
2467 DEBUG('reservation id:'||l_reservation_id, 'reduce_rsv_allocation');
2468 END IF;
2469 -- If we are deleting entire allocation
2470 --Bug 7190635, we are deleting entire allocation for ATO serial picking
2471
2472 IF l_quantity_to_delete = l_task_qty
2473 OR (l_retain_ato_profile = 'Y' AND l_ato_serial_pick='Y') THEN
2474 l_deleted_quantity := l_deleted_quantity + l_task_qty;
2475 l_sec_deleted_quantity := l_sec_deleted_quantity + l_sec_qty; --INVCONV
2476
2477 inv_trx_util_pub.delete_transaction(
2478 x_return_status => l_return_status
2479 , x_msg_data => x_msg_data
2480 , x_msg_count => x_msg_count
2481 , p_transaction_temp_id => l_transaction_temp_id
2482 );
2483
2484 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2485 IF (l_debug = 1) THEN
2486 DEBUG('Error occurred while Deleting the Transaction', 'reduce_rsv_allocation');
2487 END IF;
2488
2489 RAISE fnd_api.g_exc_error;
2490 END IF;
2491 ELSE -- reduce the quantity on the allocation
2492 IF (l_debug = 1) THEN
2493 DEBUG('reducing quantity on the allocation', 'reduce_rsv_allocation');
2494 END IF;
2495
2496 l_deleted_quantity := l_deleted_quantity + l_quantity_to_delete;
2497 l_sec_deleted_quantity := l_sec_deleted_quantity + l_sec_quantity_to_delete; --INVCONV
2498 inv_mo_line_detail_util.reduce_allocation_quantity(
2499 x_return_status => l_return_status
2500 , p_transaction_temp_id => l_transaction_temp_id
2501 , p_quantity => l_quantity_to_delete
2502 , p_secondary_quantity => l_sec_quantity_to_delete --INVCONV
2503 );
2504
2505 IF l_return_status = fnd_api.g_ret_sts_error THEN
2506 IF (l_debug = 1) THEN
2507 DEBUG('Reduce allocation returned error', 'reduce_rsv_allocation');
2508 END IF;
2509
2510 RAISE fnd_api.g_exc_error;
2511 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2512 IF (l_debug = 1) THEN
2513 DEBUG('Reduce allocation returned unexpected error', 'reduce_rsv_allocation');
2514 END IF;
2515
2516 RAISE fnd_api.g_exc_unexpected_error;
2517 END IF;
2518 END IF; -- det. quantity = task qty
2519
2520 x_return_status := fnd_api.g_ret_sts_success;
2521 EXCEPTION
2522 WHEN fnd_api.g_exc_error THEN
2523 ROLLBACK TO del_rsv_all_sp;
2524 x_return_status := fnd_api.g_ret_sts_error;
2525 WHEN OTHERS THEN
2526 ROLLBACK TO del_rsv_all_sp;
2527
2528 IF (l_debug = 1) THEN
2529 DEBUG('Others error' || SQLERRM, 'reduce_rsv_allocation');
2530 END IF;
2531
2532 x_return_status := fnd_api.g_ret_sts_unexp_error;
2533 END reduce_rsv_allocation;
2534
2535
2536 /* The following procedure is called by shipping to update carton group id whenever
2537 shipping unassign a wdd line from a delivery */
2538 PROCEDURE update_mol_carton_group
2539 ( x_return_status OUT NOCOPY VARCHAR2
2540 , x_msg_cnt OUT NOCOPY NUMBER
2541 , x_msg_data OUT NOCOPY VARCHAR2
2542 , p_line_id IN NUMBER
2543 , p_carton_grouping_id IN NUMBER
2544 ) IS
2545
2546 l_debug NUMBER;
2547 l_mo_type NUMBER;
2548
2549 CURSOR c_get_mo_type
2550 ( p_mo_line_id IN NUMBER
2551 ) IS
2552 SELECT mtrh.move_order_type
2553 FROM mtl_txn_request_lines mtrl
2554 , mtl_txn_request_headers mtrh
2555 WHERE mtrl.line_id = p_mo_line_id
2556 AND mtrh.header_id = mtrl.header_id;
2557
2558 BEGIN
2559 IF (l_debug = 1) THEN
2560 DEBUG('move order line id: ' || p_line_id, 'update_mol_carton_group');
2561 DEBUG('carton group id: ' || p_carton_grouping_id, 'update_mol_carton_group');
2562 DEBUG('before update statement...', 'update_mol_carton_group');
2563 END IF;
2564
2565 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2566 x_return_status := fnd_api.g_ret_sts_success;
2567
2568 -- {{
2569 -- BEGIN update_mol_carton_group }}
2570 --
2571 OPEN c_get_mo_type(p_line_id);
2572 FETCH c_get_mo_type INTO l_mo_type;
2573 CLOSE c_get_mo_type;
2574
2575 IF l_mo_type <> INV_GLOBALS.G_MOVE_ORDER_PUT_AWAY
2576 THEN
2577 -- {{
2578 -- Unassign a pick released delivery detail from
2579 -- the delivery. Carton grouping ID must get updated }}
2580 --
2581 UPDATE mtl_txn_request_lines
2582 SET carton_grouping_id = p_carton_grouping_id
2583 WHERE line_id = p_line_id;
2584
2585 -- {{
2586 -- Unassign a cross dock pegged delivery detail from
2587 -- the delivery, with material already received.
2588 -- Carton grouping ID column stays as is }}
2589 --
2590 IF SQL%NOTFOUND THEN
2591 x_return_status := fnd_api.g_ret_sts_unexp_error;
2592
2593 IF (l_debug = 1) THEN
2594 DEBUG('can not find move order line', 'update_mol_carton_group');
2595 END IF;
2596
2597 fnd_message.set_name('INV', 'INV_PP_INPUT_LINE_NOT_FOUND');
2598 fnd_msg_pub.ADD;
2599 END IF;
2600
2601 IF (l_debug = 1) THEN
2602 DEBUG('after update statement', 'update_mol_carton_group');
2603 END IF;
2604 END IF;
2605
2606 -- {{
2607 -- END update_mol_carton_group }}
2608 --
2609
2610 EXCEPTION
2611 WHEN OTHERS THEN
2612 x_return_status := fnd_api.g_ret_sts_unexp_error;
2613
2614 IF (l_debug = 1) THEN
2615 DEBUG('Exception: ' || sqlerrm, 'update_mol_carton_group');
2616 END IF;
2617
2618 fnd_message.set_name('INV', 'FAIL_TO_UPDATE_CARTON_GROUP'); -- need new msg
2619 fnd_msg_pub.ADD;
2620 END update_mol_carton_group;
2621
2622 END inv_mo_cancel_pvt;