[Home] [Help]
PACKAGE BODY: APPS.INV_REPLENISH_DETAIL_PUB
Source
1 PACKAGE BODY inv_replenish_detail_pub AS
2 /* $Header: INVTOTXB.pls 120.17.12020000.5 2013/04/18 06:44:36 vipratap ship $ */
3
4 -- Global constant holding the package name
5
6 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_Replenish_Detail_PUB';
7 is_debug BOOLEAN := TRUE;
8 g_retain_ato_profile VARCHAR2(1) := fnd_profile.VALUE('WSH_RETAIN_ATO_RESERVATIONS');
9
10 -- Start of Comments
11 -- API name Line_Details_PUB
12 -- Type Public
13 -- Function
14 --
15 -- Pre-reqs
16 --
17 -- Parameters
18 --
19 -- Version Current version = 1.0
20 -- Initial version = 1.0
21 --
22 -- Notes Obtain parameters from the form
23 -- Put the parameters in a record
24 -- Call the autodetail API and pass it the record
25 -- Receives the quantity detailed, number of rows detailed,
26 -- detailed and serial record types
27 -- Insert information into MMTT and Serial Numbers Temp
28 -- Return the number of detailed rows and quantities, lot
29 -- info
30 --
31 -- End of Comments
32
33 PROCEDURE print_debug(p_message IN VARCHAR2) IS
34 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
35 BEGIN
36 IF (l_debug = 1) THEN
37 inv_pick_wave_pick_confirm_pub.tracelog(p_message, 'INV_REPLENISH_DETAIL');
38 END IF;
39 END;
40
41
42 PROCEDURE line_details_pub(
43 p_line_id IN NUMBER
44 , x_number_of_rows OUT NOCOPY NUMBER
45 , x_detailed_qty OUT NOCOPY NUMBER
46 , x_return_status OUT NOCOPY VARCHAR2
47 , x_msg_count OUT NOCOPY NUMBER
48 , x_msg_data OUT NOCOPY VARCHAR2
49 , x_revision OUT NOCOPY VARCHAR2
50 , x_locator_id OUT NOCOPY NUMBER
51 , x_transfer_to_location OUT NOCOPY NUMBER
52 , x_lot_number OUT NOCOPY VARCHAR2
53 , x_expiration_date OUT NOCOPY DATE
54 , x_transaction_temp_id OUT NOCOPY NUMBER
55 , p_transaction_header_id IN NUMBER
56 , p_transaction_mode IN NUMBER
57 , p_move_order_type IN NUMBER
58 , p_serial_flag IN VARCHAR2
59 , p_plan_tasks IN BOOLEAN
60 , p_auto_pick_confirm IN BOOLEAN
61 , p_commit IN BOOLEAN DEFAULT FALSE
62 , p_transaction_date IN DATE DEFAULT NULL
63 , p_period_id IN NUMBER DEFAULT NULL
64 ) IS
65 /*p_transaction_date , p_period_id are added for the Bug#14371307, with out this fix,
66 Transaction Date is showing as sysdate in Material Transaction Form even though Transaction Date in Transact Move Order Form is changed to past date.*/
67
68 x_detailed_qty2 NUMBER;
69
70 BEGIN
71
72 -- HW INVCONV - Call the overloaded procedure
73
74 line_details_pub(
75 p_line_id => p_line_id
76 , x_number_of_rows => x_number_of_rows
77 , x_detailed_qty => x_detailed_qty
78 , x_detailed_qty2 => x_detailed_qty2
79 , x_return_status => x_return_status
80 , x_msg_count => x_msg_count
81 , x_msg_data => x_msg_data
82 , x_revision => x_revision
83 , x_locator_id => x_locator_id
84 , x_transfer_to_location => x_transfer_to_location
85 , x_lot_number => x_lot_number
86 , x_expiration_date => x_expiration_date
87 , x_transaction_temp_id => x_transaction_temp_id
88 , p_transaction_header_id => p_transaction_header_id
89 , p_transaction_mode => p_transaction_mode
90 , p_move_order_type => p_move_order_type
91 , p_serial_flag => p_serial_flag
92 , p_plan_tasks => p_plan_tasks
93 , p_auto_pick_confirm => p_auto_pick_confirm
94 , p_commit => p_commit
95 , p_transaction_date => p_transaction_date
96 , p_period_id => p_period_id
97 ) ;
98
99 END line_details_pub;
100
101
102
103
104 -- HW INVCONV - Overloaded procedure to send back Qty2
105
106 PROCEDURE line_details_pub(
107 p_line_id IN NUMBER
108 , x_number_of_rows OUT NOCOPY NUMBER
109 , x_detailed_qty OUT NOCOPY NUMBER
110 , x_detailed_qty2 OUT NOCOPY NUMBER
111 , x_return_status OUT NOCOPY VARCHAR2
112 , x_msg_count OUT NOCOPY NUMBER
113 , x_msg_data OUT NOCOPY VARCHAR2
114 , x_revision OUT NOCOPY VARCHAR2
115 , x_locator_id OUT NOCOPY NUMBER
116 , x_transfer_to_location OUT NOCOPY NUMBER
117 , x_lot_number OUT NOCOPY VARCHAR2
118 , x_expiration_date OUT NOCOPY DATE
119 , x_transaction_temp_id OUT NOCOPY NUMBER
120 , p_transaction_header_id IN NUMBER
121 , p_transaction_mode IN NUMBER
122 , p_move_order_type IN NUMBER
123 , p_serial_flag IN VARCHAR2
124 , p_plan_tasks IN BOOLEAN
125 , p_auto_pick_confirm IN BOOLEAN
126 , p_commit IN BOOLEAN DEFAULT FALSE
127 , p_transaction_date IN DATE DEFAULT NULL
128 , p_period_id IN NUMBER DEFAULT NULL
129 ) IS
130 l_api_version_number CONSTANT NUMBER := 1.0;
131 l_init_msg_list VARCHAR2(255) := fnd_api.g_true;
132 l_api_name CONSTANT VARCHAR2(30) := 'Line_Details_PUB';
133 l_num_of_rows NUMBER := 0;
134 l_detailed_qty NUMBER := 0;
135 -- HW INVCONV
136 l_detailed_qty2 NUMBER := 0;
137 l_ser_index NUMBER;
138 l_expiration_date DATE;
139 x_success NUMBER;
140 l_revision VARCHAR2(3);
141 l_transfer_to_location NUMBER;
142 l_lot_number VARCHAR2(80);
143 l_locator_id NUMBER;
144 l_transaction_temp_id NUMBER;
145 l_transaction_header_id NUMBER;
146 l_subinventory_code VARCHAR2(30);
147 l_transaction_quantity NUMBER;
148 l_primary_quantity NUMBER;
149 -- HW INVCONV Added Qty2
150 l_transaction_quantity2 NUMBER;
151 l_inventory_item_id NUMBER;
152 l_temp_id NUMBER;
153 l_serial_number VARCHAR2(30);
154 l_mtl_reservation inv_reservation_global.mtl_reservation_tbl_type;
155 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
156 l_auto_pick_confirm NUMBER;
157 l_pick_release_status inv_pick_release_pub.inv_release_status_tbl_type;
158 l_return_status VARCHAR2(1);
159 l_grouping_rule_id NUMBER;
160 l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
161 l_mold_tbl_temp inv_mo_line_detail_util.g_mmtt_tbl_type;
162 l_message VARCHAR2(2000);
163 l_count NUMBER;
164 l_from_serial_number VARCHAR2(30);
165 l_to_serial_number VARCHAR2(30);
166 l_detail_rec_count NUMBER;
167 l_success NUMBER;
168 l_auto_pick_flag VARCHAR2(1);
169 l_request_number VARCHAR2(80);
170 l_commit VARCHAR2(1);
171 l_cnt_lot NUMBER;
172
173 -- HW INVCONV - Added secondary_transaction
174 CURSOR suggestions_csr IS
175 SELECT transaction_header_id
176 , transaction_temp_id
177 , inventory_item_id
178 , revision
179 , subinventory_code
180 , locator_id
181 , transaction_quantity
182 , primary_quantity
183 , secondary_transaction_quantity
184 , lot_number
185 , lot_expiration_date
186 , serial_number
187 , transfer_to_location
188 , transaction_uom
189 , item_primary_uom_code
190 , secondary_uom_code
191 , nvl(fulfillment_base, 'P')
192 , organization_id
193 FROM mtl_material_transactions_temp
194 WHERE move_order_line_id = p_line_id;
195
196 CURSOR serial_number_csr IS
197 SELECT fm_serial_number, to_serial_number
198 FROM mtl_serial_numbers_temp
199 WHERE transaction_temp_id = l_transaction_temp_id;
200
201 CURSOR c_mtrh IS
202 SELECT request_number, grouping_rule_id
203 FROM mtl_txn_request_headers
204 WHERE header_id = l_trolin_tbl(1).header_id;
205
206 --Bug 6696594
207 Cursor c_mmtt(p_move_order_line_id NUMBER)
208 IS SELECT transaction_temp_id
209 FROM mtl_material_transactions_temp
210 WHERE move_order_line_id = p_move_order_line_id;
211
212 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
213
214 --Bug 6696594
215 l_mmtt number;
216 l_transaction_id INV_LABEL.transaction_id_rec_type;
217 l_label_status VARCHAR2(500);
218 v_transaction_id INV_LABEL.transaction_id_rec_type;
219 l_honor_case_pick_count NUMBER := 0;
220 l_counter NUMBER := 1;
221
222 -- MUOM Fulfillment Project
223 cursor c_mtlt is
224 select primary_quantity, secondary_quantity, lot_number
225 from mtl_transaction_lots_temp
226 where transaction_temp_id = l_transaction_temp_id;
227
228 l_detailed_uom VARCHAR2(10);
229 l_txn_uom VARCHAR2(10);
230 l_primary_uom VARCHAR2(10);
231 l_secondary_uom VARCHAR2(10);
232 l_fulfill_base varchar2(1):='P';
233 l_mtlt_lot_num VARCHAR2(80);
234 l_mtlt_count NUMBER;
235 l_mtlt_primary_qty NUMBER;
236 l_mtlt_secondary_qty NUMBER;
237 l_org_id NUMBER;
238 l_transaction_quantity_det NUMBER;
239
240
241 BEGIN
242 l_trolin_tbl(1) := inv_trolin_util.query_row(p_line_id);
243 OPEN c_mtrh;
244 FETCH c_mtrh INTO l_request_number, l_grouping_rule_id;
245 CLOSE c_mtrh;
246
247 IF p_move_order_type = inv_globals.g_move_order_pick_wave THEN
248 IF l_debug = 1 THEN
249 print_debug('Pickwave MO. Calling INV_PICK_RELEASE_PUB.PICK_RELEASE to allocate');
250 END IF;
251
252 IF p_auto_pick_confirm IS NOT NULL AND p_auto_pick_confirm = FALSE THEN
253 l_auto_pick_flag := 'N';
254 ELSE
255 BEGIN
256 -- The parameter is for whether pick confirm is required or not,
257 -- so the auto-pick confirm flag is the opposite of this.
258 SELECT DECODE(NVL(mo_pick_confirm_required, 2), 1, 2, 2, 1, 1)
259 INTO l_auto_pick_confirm
260 FROM mtl_parameters
261 WHERE organization_id = l_trolin_tbl(1).organization_id;
262 EXCEPTION
263 WHEN NO_DATA_FOUND THEN
264 fnd_message.set_name('INV', 'INV_AUTO_PICK_CONFIRM_PARAM');
265 fnd_msg_pub.ADD;
266 RAISE fnd_api.g_exc_unexpected_error;
267 END;
268
269 BEGIN
270 SELECT auto_pick_confirm_flag INTO l_auto_pick_flag
271 FROM wsh_picking_batches
272 WHERE NAME = l_request_number;
273
274 IF (l_auto_pick_flag IS NULL) THEN
275 IF (l_auto_pick_confirm = 1) THEN
276 l_auto_pick_flag := 'Y';
277 ELSE
278 l_auto_pick_flag := 'N';
279 END IF;
280 END IF;
281
282 EXCEPTION
283 WHEN NO_DATA_FOUND THEN
284 fnd_message.set_name('INV', 'INV_AUTO_PICK_CONFIRM_PARAM');
285 fnd_msg_pub.ADD;
286 RAISE fnd_api.g_exc_unexpected_error;
287 END;
288 END IF;
289
290 SAVEPOINT inv_before_pick_release;
291
292 IF (l_debug = 1) THEN
293 print_debug('Org Level - Pick Confirm Required = ' || TO_CHAR(l_auto_pick_confirm));
294 print_debug('Picking Batch - Auto Pick Confirm = ' || l_auto_pick_flag);
295 END IF;
296
297 IF (l_auto_pick_flag = 'Y') THEN
298 l_commit := fnd_api.g_true;
299 ELSE
300 l_commit := fnd_api.g_false;
301 END IF;
302
303 inv_pick_release_pub.pick_release(
304 p_api_version => 1.0
305 , p_init_msg_list => fnd_api.g_false
306 , p_commit => l_commit
307 , x_return_status => l_return_status
308 , x_msg_data => x_msg_data
309 , x_msg_count => x_msg_count
310 , p_mo_line_tbl => l_trolin_tbl
311 , p_auto_pick_confirm => l_auto_pick_confirm
312 , p_grouping_rule_id => l_grouping_rule_id
313 , x_pick_release_status => l_pick_release_status
314 -- Bug 5948675 passing the plan_tasks parameter to pick release API
315 , p_plan_tasks => p_plan_tasks
316 );
317
318 IF (l_debug = 1) THEN
319 print_debug('l_return_status from inv_pick_release_pub.pick_release is ' || l_return_status);
320 END IF;
321
322 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
323 IF (l_debug = 1) THEN
324 print_debug('return error');
325 END IF;
326 RAISE fnd_api.g_exc_unexpected_error;
327 END IF;
328
329 IF (l_debug = 1) THEN
330 print_debug(l_pick_release_status.COUNT);
331 END IF;
332
333 IF (l_pick_release_status.COUNT > 0) THEN
334 l_detail_rec_count := l_pick_release_status(1).detail_rec_count;
335 IF (l_debug = 1) THEN
336 print_debug('detail record count is ' || l_detail_rec_count);
337 END IF;
338
339 FOR l_index IN 1 .. l_pick_release_status.COUNT LOOP
340 IF (l_pick_release_status(l_index).return_status = fnd_api.g_ret_sts_unexp_error) THEN
341 l_return_status := fnd_api.g_ret_sts_unexp_error;
342 ELSIF l_pick_release_status(l_index).return_status = fnd_api.g_ret_sts_error THEN
343 l_return_status := fnd_api.g_ret_sts_error;
344 ELSE
345 IF (l_debug = 1) THEN
346 print_debug('return status is ' || l_return_status);
347 END IF;
348 l_return_status := fnd_api.g_ret_sts_success;
349 END IF;
350 END LOOP;
351 END IF;
352
353 IF (l_debug = 1) THEN
354 print_debug('after raise error');
355 print_debug('Number of details' || l_detail_rec_count);
356 END IF;
357
358 --No rows allocated
359 IF (l_detail_rec_count <= 0 AND l_auto_pick_flag = 'Y') THEN
360 fnd_message.set_name('INV', 'INV_DETAILING_FAILED');
361 fnd_message.set_token('LINE_NUM', l_trolin_tbl(1).line_number);
362 fnd_message.set_token('MO_NUMBER', l_request_number);
363 fnd_msg_pub.ADD;
364 RAISE fnd_api.g_exc_error;
365 END IF;
366
367 /*Bug 1588529*/
368 IF (l_detail_rec_count <= 0 AND l_auto_pick_flag = 'N') THEN
369 UPDATE mtl_txn_request_lines
370 SET line_status = 5
371 WHERE line_id = l_trolin_tbl(1).line_id
372 AND NOT EXISTS (SELECT 1 FROM mtl_material_transactions_temp /*6120769Added NOT EXISTS condition*/
373 WHERE move_order_line_id = l_trolin_tbl(1).line_id
374 AND rownum<2 );
375
376 IF (p_commit) THEN -- Bug8566005: Made the commit conditional
377 COMMIT;
378 END IF;
379 END IF;
380
381 IF (l_detail_rec_count > 0 AND l_auto_pick_flag = 'Y') THEN
382 COMMIT;
383 IF (l_debug = 1) THEN
384 print_debug('auto pick confirm');
385 END IF;
386 l_mold_tbl := inv_mo_line_detail_util.query_rows(p_line_id => l_trolin_tbl(1).line_id);
387 l_mold_tbl_temp := l_mold_tbl;
388
389 IF (l_mold_tbl.COUNT = 0) THEN
390 l_return_status := fnd_api.g_ret_sts_unexp_error;
391 fnd_message.set_name('INV', 'INV_PICK_RELEASE_ERROR');
392 fnd_msg_pub.ADD;
393 RAISE fnd_api.g_exc_unexpected_error;
394 ELSE
395 IF (l_debug = 1) THEN
396 print_debug('number of mold record is ' || l_mold_tbl.COUNT);
397 print_debug('calling pick confirm');
398 END IF;
399 IF (p_transaction_date IS NOT NULL) THEN
400 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
401 SET transaction_date = p_transaction_date
402 , acct_period_id = p_period_id
403 WHERE move_order_line_id = p_line_id;
404 print_debug('in update');
405 END IF;
406 /*Added for the Bug#14371307, with out this fix, Transaction Date is showing as sysdate in
407 Material Transaction Form even though Transaction Date in Transact Move Order Form is changed to past date. */
408
409 inv_pick_wave_pick_confirm_pub.pick_confirm(
410 p_api_version_number => 1.0
411 , p_init_msg_list => fnd_api.g_false
412 , p_commit => fnd_api.g_true
413 , x_return_status => l_return_status
414 , x_msg_count => x_msg_count
415 , x_msg_data => x_msg_data
416 , p_move_order_type => p_move_order_type
417 , p_transaction_mode => 1
418 , p_trolin_tbl => l_trolin_tbl
419 , p_mold_tbl => l_mold_tbl
420 , x_mmtt_tbl => l_mold_tbl
421 , x_trolin_tbl => l_trolin_tbl
422 );
423
424 IF (l_debug = 1) THEN
425 print_debug('after pick confirm with return status = ' || l_return_status);
426 print_debug('l_return_status = ' || l_return_status);
427 END IF;
428
429 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
430 l_success := 0;
431 IF (l_debug = 1) THEN
432 print_debug('rollback changes');
433 print_debug('l_mold_tbl_temp.count = ' || l_mold_tbl_temp.COUNT);
434 END IF;
435
436 FOR l_index IN 1 .. l_mold_tbl_temp.COUNT LOOP
437 IF (l_debug = 1) THEN
438 print_debug('calling delete details');
439 END IF;
440
441 -- HW INVCONV - Pass secondary_qty
442 inv_replenish_detail_pub.delete_details(
443 p_transaction_temp_id => l_mold_tbl_temp(l_index).transaction_temp_id
444 , p_move_order_line_id => l_mold_tbl_temp(l_index).move_order_line_id
445 , p_reservation_id => l_mold_tbl_temp(l_index).reservation_id
446 , p_transaction_quantity => l_mold_tbl_temp(l_index).transaction_quantity
447 , p_transaction_quantity2 => l_mold_tbl_temp(l_index).secondary_transaction_quantity
448 , p_primary_trx_qty => l_mold_tbl_temp(l_index).primary_quantity
449 , x_return_status => l_return_status
450 , x_msg_data => x_msg_data
451 , x_msg_count => x_msg_count
452 );
453 IF (l_debug = 1) THEN
454 print_debug('after detele details with return status ' || l_return_status);
455 END IF;
456
457 IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
458 RAISE fnd_api.g_exc_unexpected_error;
459 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
460 RAISE fnd_api.g_exc_error;
461 END IF;
462
463 -- HW INVCONV - Update Qty2 fields
464 UPDATE mtl_txn_request_lines
465 SET quantity_detailed = quantity_detailed - l_mold_tbl_temp(l_index).transaction_quantity
466 , quantity_delivered = quantity_delivered - l_mold_tbl_temp(l_index).transaction_quantity
467 , secondary_quantity_detailed = decode(secondary_quantity_detailed,FND_API.G_MISS_NUM, NULL, secondary_quantity_detailed - l_mold_tbl_temp(l_index).secondary_transaction_quantity)
468 , secondary_quantity_delivered =decode(secondary_quantity_delivered,FND_API.G_MISS_NUM, NULL, secondary_quantity_delivered - l_mold_tbl_temp(l_index).secondary_transaction_quantity)
469 WHERE line_id = l_mold_tbl_temp(l_index).move_order_line_id;
470 END LOOP;
471
472 UPDATE mtl_txn_request_lines
473 SET line_status = 7
474 WHERE line_id = p_line_id;
475
476 COMMIT;
477 ELSE
478 l_success := 1;
479 END IF;
480
481 x_return_status := l_return_status;
482
483 IF (l_debug = 1) THEN
484 print_debug('return status is ' || l_return_status);
485 END IF;
486 END IF;
487 x_return_status := l_return_status;
488 END IF;
489 ELSIF p_move_order_type = inv_globals.g_move_order_mfg_pick THEN
490 IF l_debug = 1 THEN
491 print_debug('Mfg MO. Calling INV_WIP_PICKING_PVT.PICK_RELEASE to allocate');
492 END IF;
493
494 -- HW INVCONV - Need to investigate what inv_wip_picking_pvt.pick_release does
495 -- since qty2s are being passed from l_trolin_tbl
496 inv_wip_picking_pvt.pick_release(
497 x_return_status => x_return_status
498 , x_msg_count => x_msg_count
499 , x_msg_data => x_msg_data
500 , p_commit => fnd_api.g_false
501 , p_init_msg_lst => fnd_api.g_false
502 , p_mo_line_tbl => l_trolin_tbl
503 , p_allow_partial_pick => fnd_api.g_true
504 , p_grouping_rule_id => l_grouping_rule_id
505 , p_plan_tasks => p_plan_tasks
506 , p_call_wip_api => FALSE
507 );
508
509 IF x_return_status = fnd_api.g_ret_sts_error THEN
510 RAISE fnd_api.g_exc_error;
511 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
512 RAISE fnd_api.g_exc_unexpected_error;
513 END IF;
514 ELSE
515 IF l_debug = 1 THEN
516 print_debug('Other types of MO. Calling INV_PPENGINE_PVT.CREATE_SUGGESTIONS to allocate');
517 END IF;
518
519 inv_ppengine_pvt.create_suggestions(
520 p_api_version => l_api_version_number
521 , p_init_msg_list => l_init_msg_list
522 , p_commit => fnd_api.g_false
523 , p_validation_level => fnd_api.g_valid_level_full
524 , x_return_status => l_return_status
525 , x_msg_count => x_msg_count
526 , x_msg_data => x_msg_data
527 , p_transaction_temp_id => p_line_id
528 , p_reservations => l_mtl_reservation
529 , p_suggest_serial => p_serial_flag
530 , p_plan_tasks => p_plan_tasks
531 );
532
533 IF l_return_status = fnd_api.g_ret_sts_error THEN
534 RAISE fnd_api.g_exc_error;
535 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
536 RAISE fnd_api.g_exc_unexpected_error;
537 END IF;
538
539 --Start Bug 6696594
540 IF INV_CONTROL.G_CURRENT_RELEASE_LEVEL >= 120001 THEN
541 --Call label printing API
542
543 FOR v_mmtt in c_mmtt (p_line_id)
544 LOOP
545 IF (l_debug = 1) THEN
546 print_debug('v_mmtt.transaction_temp_id ' || v_mmtt.transaction_temp_id);
547 END IF;
548
549 l_transaction_id(l_counter) := v_mmtt.transaction_temp_id;
550 l_counter := l_counter + 1;
551 END LOOP;
552
553 BEGIN
554 l_counter := 1;
555 FOR b in l_transaction_id.first..l_transaction_id.last
556 LOOP
557 /*SELECT wutta. honor_case_pick_flag into honor_case_pick
558 FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta
559 WHERE mmtt.standard_operation_id = wutta.user_task_type_id
560 AND mmtt.organization_id = wutta.organization_id
561 AND mmtt.transaction_temp_id = l_transaction_id(b);*/
562
563 SELECT count (*) into l_honor_case_pick_count
564 FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta
565 WHERE mmtt.standard_operation_id = wutta.user_task_type_id
566 AND mmtt.organization_id = wutta.organization_id
567 AND mmtt.transaction_temp_id = l_transaction_id(b)
568 AND honor_case_pick_flag = 'Y';
569
570
571 IF (l_debug = 1) THEN
572 print_debug('l_counter' || l_counter);
573 END IF;
574
575 IF l_honor_case_pick_count > 0 THEN
576 v_transaction_id(l_counter) := l_transaction_id(b);
577 l_counter := l_counter + 1;
578 END IF;
579 END LOOP;
580
581 IF l_counter > 1 THEN
582 l_return_status := fnd_api.g_ret_sts_success;
583
584 inv_label.print_label (
585 x_return_status => x_return_status
586 , x_msg_count => x_msg_count
587 , x_msg_data => x_msg_data
588 , x_label_status => l_label_status
589 , p_api_version => 1.0
590 , p_print_mode => 1
591 , p_business_flow_code => 42 --Business Flow Pick Release
592 , p_transaction_id => v_transaction_id);
593
594 IF ( l_return_status <> fnd_api.g_ret_sts_success ) THEN
595 IF (l_debug = 1) THEN
596 print_debug('failed to print labels');
597 END IF;
598 fnd_message.set_name('WMS', 'WMS_PRINT_LABEL_FAIL');
599 fnd_msg_pub.ADD;
600 END IF;
601 END IF;
602 EXCEPTION
603 WHEN OTHERS THEN
604 IF (l_debug = 1) THEN
605 print_debug('Exception occured while calling print_label');
606 END IF;
607 fnd_message.set_name('WMS', 'WMS_PRINT_LABEL_FAIL');
608 fnd_msg_pub.ADD;
609 END;
610 END IF;
611 --End Bug 6696594
612
613 IF (inv_install.adv_inv_installed(l_trolin_tbl(1).organization_id) = TRUE) THEN
614 IF (l_debug = 1) THEN
615 inv_pick_wave_pick_confirm_pub.tracelog('about to call wms_rule_pvt.assignTTs', 'INVTOTXB');
616 inv_pick_wave_pick_confirm_pub.tracelog('header_id is ' || l_trolin_tbl(1).header_id, 'INVTOTXB');
617 END IF;
618
619 wms_cartnzn_pub.cartonize(
620 p_api_version => 1.0
621 , p_init_msg_list => fnd_api.g_false
622 , p_commit => fnd_api.g_false
623 , p_validation_level => fnd_api.g_valid_level_full
624 , x_return_status => l_return_status
625 , x_msg_count => x_msg_count
626 , x_msg_data => x_msg_data
627 , p_out_bound => 'Y'
628 , p_org_id => l_trolin_tbl(1).organization_id
629 , p_move_order_header_id => l_trolin_tbl(1).header_id
630 , p_disable_cartonization => 'Y'
631 );
632
633 IF l_return_status = fnd_api.g_ret_sts_error THEN
634 IF (l_debug = 1) THEN
635 print_debug('Error from WMS_CARTNZN_PUB.CARTONIZE');
636 END IF;
637
638 RAISE fnd_api.g_exc_error;
639 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
640 IF (l_debug = 1) THEN
641 print_debug('error from WMS_CARTNZN_PUB.CARTONIZE');
642 END IF;
643
644 RAISE fnd_api.g_exc_unexpected_error;
645 END IF;
646 END IF;
647 END IF;
648
649 IF (p_move_order_type <> 3 OR (p_move_order_type = 3 AND l_auto_pick_flag = 'N')) THEN
650 l_num_of_rows := 0;
651 -- insert the records INV_AUTODETAIL.detail_row returns
652 -- into the mtl_material_transactions_temp table
653 --
654 -- Added Bug 3633141
655 -- Considering the Delivered Quantity for the move order line.
656 --
657 /* IF (p_move_order_type <> 3) THEN
658 -- HW INVCONV -Added Qty2
659 SELECT quantity_delivered, secondary_quantity_delivered
660 INTO l_detailed_qty, l_detailed_qty2
661 FROM mtl_txn_request_lines
662 WHERE line_id = p_line_id;
663
664 IF l_detailed_qty IS NULL THEN
665 l_detailed_qty := 0;
666 -- HW INVCONV -Added Qty2
667 l_detailed_qty2 := 0;
668 END IF;
669 END IF; */ --commented against bug : 4155230
670 --Bug fix 3633141 ends
671 OPEN suggestions_csr;
672 LOOP
673 -- HW INVCONV - Added secondary_transaction_quantity
674 FETCH suggestions_csr INTO l_transaction_header_id
675 , l_transaction_temp_id
676 , l_inventory_item_id
677 , l_revision
678 , l_subinventory_code
679 , l_locator_id
680 , l_transaction_quantity
681 , l_primary_quantity
682 , l_transaction_quantity2
683 , l_lot_number
684 , l_expiration_date
685 , l_serial_number
686 , l_transfer_to_location
687 , l_txn_uom
688 , l_primary_uom
689 , l_secondary_uom
690 , l_fulfill_base
691 , l_org_id;
692 EXIT WHEN suggestions_csr%NOTFOUND;
693 assign_expenditure_org(l_transaction_temp_id);
694 OPEN serial_number_csr;
695
696 LOOP
697 FETCH serial_number_csr INTO l_from_serial_number, l_to_serial_number;
698 EXIT WHEN serial_number_csr%NOTFOUND;
699 END LOOP;
700
701 CLOSE serial_number_csr;
702 l_num_of_rows := l_num_of_rows + 1;
703
704 IF (l_debug = 1) THEN
705 print_debug('number of rows = from mmtt ' || l_num_of_rows);
706 END IF;
707
708 -- MUOM Fulfillment Project latent issue
709 SELECT uom_code INTO l_detailed_uom
710 FROM mtl_txn_request_lines
711 WHERE line_id = p_line_id;
712
713 IF l_txn_uom = l_detailed_uom THEN
714 l_detailed_qty := l_detailed_qty + ABS(l_transaction_quantity);
715 ELSIF l_detailed_uom = l_primary_uom THEN
716 l_detailed_qty := l_detailed_qty + ABS(l_primary_quantity);
717 ELSIF l_detailed_uom = l_secondary_uom THEN
718 l_detailed_qty := l_detailed_qty + ABS(l_transaction_quantity2);
719 ELSE
720 SELECT Count(*) INTO l_mtlt_count
721 FROM mtl_transaction_lots_temp WHERE
722 transaction_temp_id = l_transaction_temp_id;
723
724 IF l_mtlt_count > 0 THEN
725 l_transaction_quantity := 0;
726 OPEN c_mtlt;
727 LOOP
728 FETCH c_mtlt INTO l_mtlt_primary_qty, l_mtlt_secondary_qty, l_mtlt_lot_num;
729 EXIT WHEN c_mtlt%NOTFOUND;
730 IF l_fulfill_base = 'S' THEN
731 l_transaction_quantity_det:=
732 inv_convert.inv_um_convert(
733 item_id => l_inventory_item_id
734 , lot_number => l_mtlt_lot_num
735 , organization_id => l_org_id
736 , precision => null
737 , from_quantity => l_mtlt_secondary_qty
738 , from_unit => l_secondary_uom
739 , to_unit => l_detailed_uom
740 , from_name => null
741 , to_name => null
742 );
743 ELSE
744 l_transaction_quantity_det :=
745 inv_convert.inv_um_convert(
746 item_id => l_inventory_item_id
747 , lot_number => l_mtlt_lot_num
748 , organization_id => l_org_id
749 , precision => null
750 , from_quantity => l_mtlt_primary_qty
751 , from_unit => l_primary_uom
752 , to_unit => l_detailed_uom
753 , from_name => null
754 , to_name => null
755 );
756 END IF; -- if l_fulfill_base = 'S'
757 l_transaction_quantity := l_transaction_quantity + l_transaction_quantity_det;
758 END LOOP;
759 ELSE -- IF l_mtlt_count > 0
760 IF l_fulfill_base = 'S' THEN
761 l_transaction_quantity :=
762 inv_convert.inv_um_convert(
763 item_id => l_inventory_item_id
764 , PRECISION => NULL
765 , from_quantity => l_transaction_quantity2
766 , from_unit => l_secondary_uom
767 , to_unit => l_detailed_uom
768 , from_name => NULL
769 , to_name => NULL
770 );
771 ELSE
772 l_transaction_quantity :=
773 inv_convert.inv_um_convert(
774 item_id => l_inventory_item_id
775 , PRECISION => NULL
776 , from_quantity => l_primary_quantity
777 , from_unit => l_primary_uom
778 , to_unit => l_detailed_uom
779 , from_name => NULL
780 , to_name => NULL
781 );
782 END IF;
783
784 END IF; -- if l_mtlt_count > 0
785 l_detailed_qty := l_detailed_qty + ABS(l_transaction_quantity);
786 END IF;
787 -- END MUOM Fulfillment Project latent issue
788 -- HW INVCONV - Added Qty2
789 l_detailed_qty2 := l_detailed_qty2 + ABS(l_transaction_quantity2);
790
791 IF l_transaction_quantity < 0 THEN
792 -- HW INVCONV - Added qty2
793 UPDATE mtl_material_transactions_temp
794 SET transaction_quantity = ABS(transaction_quantity)
795 , primary_quantity = ABS(primary_quantity)
796 , secondary_transaction_quantity = ABS(secondary_transaction_quantity)
797 WHERE transaction_temp_id = l_transaction_temp_id;
798 END IF;
799 END LOOP; -- Detail loop
800
801 CLOSE suggestions_csr;
802 /*Bug#5140639. Added the below code to update distribution_account_id and
803 ship_to_location columns of the table MMTT*/
804 IF ( l_trolin_tbl(1).to_account_id IS NOT NULL) THEN
805 IF (l_debug = 1) THEN
806 print_debug('Updating distribution_account_id and ship_to_location_id in MMTT');
807 END IF;
808
809 Update MTL_MATERIAL_TRANSACTIONS_TEMP
810 SET distribution_account_id = l_trolin_tbl(1).to_account_id,
811 ship_to_location = Nvl(l_trolin_tbl(1).ship_to_location_id, ship_to_location)
812 WHERE move_order_line_id = l_trolin_tbl(1).line_id;
813
814 IF (l_debug = 1) THEN
815 print_debug('Number of rows updated:'||SQL%ROWCOUNT);
816 END IF;
817 END IF;
818
819
820 IF (l_debug = 1) THEN
821 print_debug('after the loop');
822 END IF;
823
824 -- set output variables
825 IF p_move_order_type <> 3 THEN
826 BEGIN
827 SELECT COUNT(*) INTO l_cnt_lot
828 FROM mtl_transaction_lots_temp
829 WHERE transaction_temp_id = l_transaction_temp_id;
830
831 IF l_cnt_lot = 1 THEN
832 SELECT lot_number
833 INTO l_lot_number
834 FROM mtl_transaction_lots_temp
835 WHERE transaction_temp_id = l_transaction_temp_id;
836 END IF;
837 EXCEPTION
838 WHEN OTHERS THEN
839 NULL;
840 END;
841 END IF;
842
843 x_number_of_rows := l_num_of_rows;
844
845 IF (l_debug = 1) THEN
846 print_debug('number of rows = ' || l_num_of_rows);
847 END IF;
848
849 x_detailed_qty := l_detailed_qty;
850 -- HW INVCONV - Added qty2
851 x_detailed_qty2 := l_detailed_qty2;
852 -- rev, fm/to locator can be set as follows because
853 -- we will use them when there is only one queried record
854 x_revision := l_revision;
855 x_locator_id := l_locator_id;
856 x_transfer_to_location := l_transfer_to_location;
857 x_lot_number := l_lot_number;
858 x_expiration_date := l_expiration_date;
859 x_transaction_temp_id := l_transaction_temp_id;
860 x_return_status := fnd_api.g_ret_sts_success;
861
862 IF ( p_commit ) THEN
863 COMMIT;
864 END IF;
865
866 ELSIF(p_move_order_type = 3 AND l_auto_pick_confirm = 1) THEN
867 IF (l_success = 1) THEN
868 x_return_status := fnd_api.g_ret_sts_success;
869 ELSE
870 RAISE fnd_api.g_exc_error;
871 END IF;
872 END IF;
873 EXCEPTION
874 WHEN fnd_api.g_exc_error THEN
875 x_return_status := fnd_api.g_ret_sts_error;
876 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
877 WHEN fnd_api.g_exc_unexpected_error THEN
878 x_return_status := fnd_api.g_ret_sts_unexp_error;
879 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
880 WHEN OTHERS THEN
881 x_return_status := fnd_api.g_ret_sts_unexp_error;
882
883 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
884 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Line_Details_PUB');
885 END IF;
886
887 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
888 END line_details_pub;
889
890 PROCEDURE assign_expenditure_org(p_transaction_temp_id NUMBER) IS
891 l_transaction_type_id NUMBER;
892 l_transaction_action_id NUMBER;
893 l_install_status VARCHAR2(1);
894 l_msg_count NUMBER;
895 l_msg_data VARCHAR2(2000);
896 l_project_related VARCHAR2(1);
897 l_expenditure_type VARCHAR2(30);
898 l_project_id NUMBER;
899 l_task_id NUMBER;
900 l_organization_id NUMBER;
901 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
902 BEGIN
903 SELECT transaction_type_id
904 , transaction_action_id
905 INTO l_transaction_type_id
906 , l_transaction_action_id
907 FROM mtl_material_transactions_temp
908 WHERE transaction_temp_id = p_transaction_temp_id;
909
910
911
912 SELECT DECODE(type_class, 1, 'Y', 'N')
913 INTO l_project_related
914 FROM mtl_transaction_types
915 WHERE transaction_type_id = l_transaction_type_id;
916
917 IF l_project_related = 'Y' THEN
918 --copy the project and task info to source_project and source_task info in the MMTT table;
919 -- or the corresponding block or pl/sql table that eventually populates into MMTT.
920
921 fnd_profile.get('CSE_PA_EXP_TYPE', l_expenditure_type);
922
923 SELECT organization_id
924 INTO l_organization_id
925 FROM mtl_material_transactions_temp
926 WHERE transaction_temp_id = p_transaction_temp_id;
927
928 IF (l_transaction_action_id = 1) THEN
929 --fnd_message.debug('copying project_id and task_id from tolines_blk to mtl_Trx_line');
930
931 SELECT project_id
932 , task_id
933 INTO l_project_id
934 , l_task_id
935 FROM mtl_txn_request_lines
936 WHERE line_id = (SELECT move_order_line_id
937 FROM mtl_material_transactions_temp
938 WHERE transaction_temp_id = p_transaction_temp_id);
939 END IF;
940
941 UPDATE mtl_material_transactions_temp
942 SET source_project_id = l_project_id
943 , source_task_id = l_task_id
944 , pa_expenditure_org_id = l_organization_id
945 , expenditure_type = l_expenditure_type
946 WHERE transaction_temp_id = p_transaction_temp_id;
947 END IF;
948 END;
949
950 --------------------------------------------------------------------------
951 -- Start of Comments
952 -- API name Delete_Details
953 -- Type Public
954 -- Function
955 --
956 -- Pre-reqs
957 --
958 -- Parameters
959 --
960 -- Version Current version = 1.0
961 -- Initial version = 1.0
962 --
963 -- Notes Delete from MMTT those with process_flag = N
964 -- Delete from Serial Numbers Temp the records related to the deleted
965 -- records in MMTT
966 -- Return number of MMTT records being deleted
967 --
968 -- End of Comments
969
970 -- HW INVCONV - Pass Qty2
971 PROCEDURE delete_details(
972 p_transaction_temp_id IN NUMBER
973 , p_move_order_line_id IN NUMBER
974 , p_reservation_id IN NUMBER
975 , p_transaction_quantity IN NUMBER
976 , p_transaction_quantity2 IN NUMBER default FND_API.G_MISS_NUM
977 , p_primary_trx_qty IN NUMBER
978 , x_return_status OUT NOCOPY VARCHAR2
979 , x_msg_count OUT NOCOPY NUMBER
980 , x_msg_data OUT NOCOPY VARCHAR2
981 , p_delete_temp_records IN BOOLEAN default TRUE /*Bug#5505709.*/
982 ) IS
983 l_reservation_id NUMBER := p_reservation_id;
984 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
985 l_mtl_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
986 l_mtl_reservation_tbl_count NUMBER := 0;
987 l_return_status VARCHAR2(1);
988 l_original_serial_number inv_reservation_global.serial_number_tbl_type;
989 l_to_serial_number inv_reservation_global.serial_number_tbl_type;
990 l_error_code NUMBER;
991 l_count NUMBER;
992 l_success BOOLEAN;
993 l_umconvert_trans_quantity NUMBER := 0;
994 l_mmtt_rec inv_mo_line_detail_util.g_mmtt_rec;
995 l_primary_uom VARCHAR2(10);
996 l_ato_item NUMBER := 0;
997 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
998 BEGIN
999 --null;
1000 --inv_debug.message('l_reservation_id = ' || p_reservation_id);
1001 --inv_debug.message('l_transaction_temp_id = ' || p_transaction_temp_id);
1002 --inv_debug.message('l_line_id = ' || p_move_order_line_id);
1003 --inv_debug.message('l_transaction_quantity = ' || p_transaction_quantity);
1004 IF l_reservation_id IS NOT NULL THEN
1005 l_mtl_reservation_rec.reservation_id := l_reservation_id;
1006 inv_reservation_pub.query_reservation(
1007 p_api_version_number => 1.0
1008 , x_return_status => l_return_status
1009 , x_msg_count => x_msg_count
1010 , x_msg_data => x_msg_data
1011 , p_query_input => l_mtl_reservation_rec
1012 , x_mtl_reservation_tbl => l_mtl_reservation_tbl
1013 , x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count
1014 , x_error_code => l_error_code
1015 );
1016
1017 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1018 RAISE fnd_api.g_exc_error;
1019 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1020 RAISE fnd_api.g_exc_unexpected_error;
1021 END IF;
1022 END IF; --rsv id not null
1023
1024 IF l_mtl_reservation_tbl_count > 0 THEN
1025 -- Bug 2621481 If reservations exist check if the item is an
1026 -- ato item if the profile WSH_RETAIN_ATO_RESERVATIONS = 'Y'
1027
1028 IF g_retain_ato_profile = 'Y' THEN
1029 BEGIN
1030 SELECT 1
1031 , primary_uom_code
1032 INTO l_ato_item
1033 , l_primary_uom
1034 FROM mtl_system_items
1035 WHERE replenish_to_order_flag = 'Y'
1036 AND bom_item_type = 4
1037 AND inventory_item_id = l_mtl_reservation_tbl(1).inventory_item_id
1038 AND organization_id = l_mtl_reservation_tbl(1).organization_id;
1039 EXCEPTION
1040 WHEN OTHERS THEN
1041 l_ato_item := 0;
1042 END;
1043 END IF;
1044
1045 IF l_ato_item = 1 THEN
1046 -- If item is ato item, reduce the detailed quantity by the transaction
1047 -- quantity and retain the reservation.Convert to primary uom before
1048 -- reducing detailed quantity.
1049 l_mmtt_rec := inv_mo_line_detail_util.query_row(p_transaction_temp_id);
1050 l_umconvert_trans_quantity := p_transaction_quantity;
1051
1052 IF l_mmtt_rec.inventory_item_id IS NOT NULL
1053 AND l_mmtt_rec.transaction_uom IS NOT NULL THEN
1054 l_umconvert_trans_quantity :=
1055 inv_convert.inv_um_convert(
1056 item_id => l_mmtt_rec.inventory_item_id
1057 , PRECISION => NULL
1058 , from_quantity => p_transaction_quantity
1059 , from_unit => l_mmtt_rec.transaction_uom
1060 , to_unit => l_primary_uom
1061 , from_name => NULL
1062 , to_name => NULL
1063 );
1064 END IF;
1065
1066 l_mtl_reservation_rec := l_mtl_reservation_tbl(1);
1067 l_mtl_reservation_tbl(1).detailed_quantity := NVL(l_mtl_reservation_tbl(1).detailed_quantity, 0) - ABS(l_umconvert_trans_quantity);
1068 inv_reservation_pub.update_reservation(
1069 p_api_version_number => 1.0
1070 , x_return_status => l_return_status
1071 , x_msg_count => x_msg_count
1072 , x_msg_data => x_msg_data
1073 , p_original_rsv_rec => l_mtl_reservation_rec
1074 , p_to_rsv_rec => l_mtl_reservation_tbl(1)
1075 , p_original_serial_number => l_original_serial_number
1076 , p_to_serial_number => l_to_serial_number
1077 );
1078
1079 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1080 RAISE fnd_api.g_exc_error;
1081 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1082 RAISE fnd_api.g_exc_unexpected_error;
1083 END IF;
1084 ELSE
1085 l_mtl_reservation_rec := l_mtl_reservation_tbl(1);
1086 l_mtl_reservation_tbl(1).detailed_quantity :=
1087 NVL(l_mtl_reservation_tbl(1).detailed_quantity, 0)
1088 - ABS(p_transaction_quantity);
1089 l_mtl_reservation_tbl(1).reservation_quantity :=
1090 NVL(l_mtl_reservation_tbl(1).reservation_quantity, 0)
1091 - ABS(p_transaction_quantity);
1092 l_mtl_reservation_tbl(1).primary_reservation_quantity :=
1093 NVL(l_mtl_reservation_tbl(1).primary_reservation_quantity, 0)
1094 - ABS(p_primary_trx_qty);
1095
1096 -- HW INVCONV - Update Qty2s if they are present
1097
1098 IF ( p_transaction_quantity2 <> FND_API.G_MISS_NUM) THEN
1099 -- No need to use NVL similar to Qty1
1100 l_mtl_reservation_tbl(1).secondary_detailed_quantity :=
1101 l_mtl_reservation_tbl(1).secondary_detailed_quantity
1102 - ABS(p_transaction_quantity2);
1103 l_mtl_reservation_tbl(1).secondary_reservation_quantity :=
1104 l_mtl_reservation_tbl(1).secondary_reservation_quantity
1105 - ABS(p_transaction_quantity2);
1106 END IF;
1107
1108 inv_reservation_pub.update_reservation(
1109 p_api_version_number => 1.0
1110 , x_return_status => l_return_status
1111 , x_msg_count => x_msg_count
1112 , x_msg_data => x_msg_data
1113 , p_original_rsv_rec => l_mtl_reservation_rec
1114 , p_to_rsv_rec => l_mtl_reservation_tbl(1)
1115 , p_original_serial_number => l_original_serial_number
1116 , p_to_serial_number => l_to_serial_number
1117 );
1118
1119 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1120 RAISE fnd_api.g_exc_error;
1121 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1122 RAISE fnd_api.g_exc_unexpected_error;
1123 END IF;
1124 END IF; -- ato item check
1125 END IF; --reservation count >0
1126
1127 /*inv_reservation_pub.query_reservation(
1128 p_api_version_number => 1.0,
1129 x_return_status => l_return_status,
1130 x_msg_count => x_msg_count,
1131 x_msg_data => x_msg_data,
1132 p_query_input => l_mtl_reservation_rec,
1133 x_mtl_reservation_tbl => l_mtl_reservation_tbl,
1134 x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count,
1135 x_error_code => l_error_code);
1136
1137 ---inv_debug.message('reservation_quantity = ' || l_mtl_reservation_tbl(1).reservation_quantity);
1138 --inv_debug.message('detailed_quantity = ' || l_mtl_reservation_tbl(1).detailed_quantity);
1139 --inv_debug.message('primary_reservation_quantity = ' || l_mtl_reservation_Tbl(1).primary_reservation_quantity);
1140 */
1141 /*Bug#5505709. Put the code that deletes rows from MMTT/MSNT/MTLT inside the IF condition. This is
1142 because, if this procedure is called from the 'Transact Move Order Line Allocations' form when the user
1143 presses the DELETE button, deletion of these rows is already handled.*/
1144 /*Bug10015753,the MSN mark label not removed caused by bug5505709,
1145 since the issue ''FRM-40510: Oracle error: unable to DELETE record.' reported on bug5505709,
1146 it caused by double MMTT deleting, we only need to add the condition on calling inv_mo_line_detail_util.delete_row from the 'Transact Move Order Line Allocations' form,
1147 we should call clear_record API to remove the orphan records in MSNT/MTLT.
1148 */
1149 CLEAR_RECORD(p_transaction_temp_id, l_success);
1150
1151 IF (NOT l_success) THEN
1152 RAISE fnd_api.g_exc_error;
1153 END IF;
1154
1155 IF (p_delete_temp_records) THEN
1156 inv_mo_line_detail_util.delete_row( x_return_status => l_return_status
1157 , p_line_id => p_move_order_line_id
1158 , p_line_detail_id => p_transaction_temp_id);
1159
1160 /* select count(1) into l_count
1161 from mtl_material_transactions_temp
1162 where move_order_line_id = p_move_order_line_id;
1163
1164 --inv_debug.message('count = ' || l_count);
1165 */
1166 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1167 RAISE fnd_api.g_exc_error;
1168 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1169 RAISE fnd_api.g_exc_unexpected_error;
1170 END IF;
1171 END IF; --p_delete_temp_records
1172
1173 COMMIT;
1174 x_return_status := fnd_api.g_ret_sts_success;
1175 EXCEPTION
1176 WHEN fnd_api.g_exc_error THEN
1177 x_return_status := fnd_api.g_ret_sts_error;
1178 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1179 WHEN fnd_api.g_exc_unexpected_error THEN
1180 x_return_status := fnd_api.g_ret_sts_unexp_error;
1181 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1182 WHEN OTHERS THEN
1183 x_return_status := fnd_api.g_ret_sts_unexp_error;
1184
1185 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1186 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Line_Details_PUB');
1187 END IF;
1188 END delete_details;
1189
1190 ---------------------------------------------------
1191 PROCEDURE clear_block_cancel(p_trx_header_id IN NUMBER, p_success IN OUT NOCOPY BOOLEAN) IS
1192 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1193 BEGIN
1194 p_success := TRUE;
1195
1196 -- Delete predefined serial numbers
1197 DELETE mtl_serial_numbers
1198 WHERE group_mark_id = p_trx_header_id
1199 AND current_status = 6;
1200
1201 -- Unmark serial numbers
1202 UPDATE mtl_serial_numbers
1203 SET group_mark_id = NULL
1204 , line_mark_id = NULL
1205 , lot_line_mark_id = NULL
1206 WHERE group_mark_id = p_trx_header_id;
1207
1208 -- Delete lot and serial records from temp tables
1209 DELETE mtl_serial_numbers_temp
1210 WHERE group_header_id = p_trx_header_id;
1211
1212 DELETE mtl_transaction_lots_temp
1213 WHERE group_header_id = p_trx_header_id;
1214
1215 DELETE mtl_material_transactions_temp
1216 WHERE transaction_header_id = p_trx_header_id;
1217 EXCEPTION
1218 WHEN NO_DATA_FOUND THEN
1219 NULL;
1220 WHEN OTHERS THEN
1221 p_success := FALSE;
1222 END clear_block_cancel;
1223
1224 ---------------------------------------------------
1225 PROCEDURE CLEAR_RECORD(p_trx_tmp_id NUMBER, p_success IN OUT NOCOPY BOOLEAN) IS
1226 l_serial_temp_id NUMBER;
1227 l_lot_count NUMBER;
1228 l_serial_count NUMBER;
1229 l_header_id NUMBER := -1;
1230 unmarked_value NUMBER := -1;
1231
1232 CURSOR lot_temp_csr(trx_temp_id NUMBER) IS
1233 SELECT serial_transaction_temp_id
1234 FROM mtl_transaction_lots_temp
1235 WHERE transaction_temp_id = trx_temp_id;
1236
1237 CURSOR serial_temp_csr(trx_temp_id NUMBER) IS
1238 SELECT fm_serial_number
1239 , to_serial_number
1240 , group_header_id
1241 FROM mtl_serial_numbers_temp
1242 WHERE transaction_temp_id = trx_temp_id;
1243
1244 l_fm_serial_number VARCHAR2(30);
1245 l_to_serial_number VARCHAR2(30);
1246 l_transaction_header_id NUMBER;
1247 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1248
1249 BEGIN
1250 --inv_debug.message('ssia', 'in clear record');
1251 p_success := TRUE;
1252
1253 SELECT COUNT(*)
1254 INTO l_lot_count
1255 FROM mtl_transaction_lots_temp
1256 WHERE transaction_temp_id = p_trx_tmp_id;
1257
1258 --inv_debug.message('ssia', 'after lot_count = ' || l_lot_count);
1259 --inv_debug.message('ssia', 'p_trx_tmp_id is ' || p_trx_tmp_id);
1260
1261 print_debug('txn temp id: ' || p_trx_tmp_id);
1262 print_debug('lot count: ' || l_lot_count);
1263
1264 SELECT transaction_header_id
1265 INTO l_transaction_header_id
1266 FROM mtl_material_transactions_temp
1267 WHERE transaction_temp_id = p_trx_tmp_id;
1268
1269 --inv_debug.message('ssia', 'l_transaction_header_id is '||l_transaction_header_id);
1270 IF (l_lot_count > 0) THEN
1271 OPEN lot_temp_csr(p_trx_tmp_id);
1272
1273 LOOP
1274 FETCH lot_temp_csr INTO l_serial_temp_id;
1275 EXIT WHEN lot_temp_csr%NOTFOUND;
1276
1277 SELECT COUNT(*)
1278 INTO l_serial_count
1279 FROM mtl_serial_numbers_temp
1280 WHERE transaction_temp_id = l_serial_temp_id;
1281
1282 IF (l_serial_count > 0) THEN
1283 OPEN serial_temp_csr(l_serial_temp_id);
1284
1285 LOOP
1286 FETCH serial_temp_csr INTO l_fm_serial_number, l_to_serial_number, l_header_id;
1287 EXIT WHEN serial_temp_csr%NOTFOUND;
1288
1289 /* Bug #2798218
1290 * While allocating serials, the group_mark_id column in mtl_serial_numbers is always
1291 * populated with the value of MMTT.transaction_temp_id (for a serial controlled item)
1292 * or MTLT.serial_transaction_temp_id (for a lot and serial controlled item), whether
1293 * the serials are allocated by the system or the manually by the user in the serial entry block
1294 * While unmarking the serials, always use transaction_temp_id or serial_transaction_temp_id
1295 * and do not use mmtt.transaction_header_id in the WHERE clause of the UPDATE statement
1296 */
1297 UPDATE mtl_serial_numbers
1298 SET line_mark_id = unmarked_value
1299 , group_mark_id = unmarked_value
1300 , lot_line_mark_id = unmarked_value
1301 WHERE (group_mark_id = l_serial_temp_id OR group_mark_id = l_header_id) --Bug#6009436.
1302 AND serial_number >= NVL(l_fm_serial_number, serial_number)
1303 AND serial_number <= NVL(l_to_serial_number, NVL(l_fm_serial_number, serial_number))
1304 AND LENGTH(serial_number) = LENGTH(NVL(l_fm_serial_number, serial_number));
1305 END LOOP;
1306 END IF;
1307
1308 DELETE mtl_serial_numbers_temp
1309 WHERE transaction_temp_id = l_serial_temp_id;
1310 END LOOP;
1311
1312 CLOSE lot_temp_csr;
1313
1314 DELETE mtl_transaction_lots_temp
1315 WHERE transaction_temp_id = p_trx_tmp_id;
1316 ELSE
1317 --inv_debug.message('ssia', 'lot_count = ' || l_lot_count);
1318 SELECT COUNT(*)
1319 INTO l_serial_count
1320 FROM mtl_serial_numbers_temp
1321 WHERE transaction_temp_id = p_trx_tmp_id;
1322
1323 print_debug('serial count: ' || l_serial_count);
1324
1325 --inv_debug.message('ssia', 'serial_count = ' || l_serial_count);
1326
1327 IF (l_serial_count > 0) THEN
1328 OPEN serial_temp_csr(p_trx_tmp_id);
1329
1330 --inv_debug.message('ssia', 'inside serial');
1331 LOOP
1332 --inv_debug.message('ssia', 'inside loop');
1333 FETCH serial_temp_csr INTO l_fm_serial_number, l_to_serial_number, l_header_id;
1334 EXIT WHEN serial_temp_csr%NOTFOUND;
1335
1336 --inv_debug.message('ssia', 'l_header_id is ' || l_header_id);
1337 --inv_debug.message('ssia', 'update mtl_serial_number');
1338 UPDATE mtl_serial_numbers
1339 SET line_mark_id = unmarked_value
1340 , group_mark_id = unmarked_value
1341 , lot_line_mark_id = unmarked_value
1342 WHERE ( group_mark_id = p_trx_tmp_id OR group_mark_id = l_header_id ) --Bug#6009436
1343 AND serial_number >= NVL(l_fm_serial_number, serial_number)
1344 AND serial_number <= NVL(l_to_serial_number, NVL(l_fm_serial_number, serial_number))
1345 AND LENGTH(serial_number) = LENGTH(NVL(l_fm_serial_number, serial_number));
1346 END LOOP;
1347
1348 CLOSE serial_temp_csr;
1349 END IF;
1350
1351 DELETE mtl_serial_numbers_temp
1352 WHERE transaction_temp_id = p_trx_tmp_id;
1353 END IF;
1354 EXCEPTION
1355 WHEN NO_DATA_FOUND THEN
1356 NULL;
1357 WHEN OTHERS THEN
1358 p_success := FALSE;
1359 END CLEAR_RECORD;
1360
1361 PROCEDURE split_line_details(
1362 p_transaction_temp_id IN NUMBER
1363 , p_missing_quantity IN NUMBER
1364 , p_detailed_quantity IN NUMBER
1365 , p_transaction_quantity IN NUMBER
1366 , x_return_status OUT NOCOPY VARCHAR2
1367 , x_msg_count OUT NOCOPY NUMBER
1368 , x_msg_data OUT NOCOPY VARCHAR2
1369 ) IS
1370 l_transaction_temp_id NUMBER := p_transaction_temp_id;
1371 l_mmtt_rec inv_mo_line_detail_util.g_mmtt_rec;
1372 l_next_id NUMBER;
1373 l_return_status VARCHAR2(1);
1374 l_count NUMBER;
1375 l_primary_quantity NUMBER;
1376 l_primary_uom VARCHAR2(10);
1377 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1378 BEGIN
1379 SELECT mtl_material_transactions_s.NEXTVAL
1380 INTO l_next_id
1381 FROM DUAL;
1382
1383 l_mmtt_rec := inv_mo_line_detail_util.query_row(l_transaction_temp_id);
1384 --inv_debug.message('l_next_id ' || l_next_id);
1385 l_mmtt_rec.transaction_temp_id := l_next_id;
1386 l_mmtt_rec.transaction_quantity := p_detailed_quantity - p_transaction_quantity - p_missing_quantity;
1387
1388 IF l_mmtt_rec.inventory_item_id IS NOT NULL
1389 AND l_mmtt_rec.transaction_uom IS NOT NULL THEN
1390 SELECT primary_uom_code
1391 INTO l_primary_uom
1392 FROM mtl_system_items
1393 WHERE organization_id = l_mmtt_rec.organization_id
1394 AND inventory_item_id = l_mmtt_rec.inventory_item_id;
1395
1396 l_primary_quantity :=
1397 inv_convert.inv_um_convert(
1398 item_id => l_mmtt_rec.inventory_item_id
1399 , PRECISION => NULL
1400 , from_quantity => l_mmtt_rec.transaction_quantity
1401 , from_unit => l_mmtt_rec.transaction_uom
1402 , to_unit => l_primary_uom
1403 , from_name => NULL
1404 , to_name => NULL
1405 );
1406 l_mmtt_rec.primary_quantity := l_primary_quantity;
1407 END IF;
1408
1409 l_mmtt_rec.transaction_status := 2;
1410 l_mmtt_rec.transaction_header_id := NULL;
1411 inv_mo_line_detail_util.insert_row(x_return_status => l_return_status, p_mo_line_detail_rec => l_mmtt_rec);
1412
1413 --inv_debug.message('after insert row '|| l_return_status );
1414 SELECT COUNT(*)
1415 INTO l_count
1416 FROM mtl_material_transactions_temp
1417 WHERE move_order_line_id = l_mmtt_rec.move_order_line_id;
1418
1419 --inv_debug.message(' total record = ' || l_count ||' for move order line id ' || l_mmtt_rec.move_order_line_id);
1420 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1421 RAISE fnd_api.g_exc_error;
1422 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1423 RAISE fnd_api.g_exc_unexpected_error;
1424 END IF;
1425 EXCEPTION
1426 WHEN fnd_api.g_exc_error THEN
1427 x_return_status := fnd_api.g_ret_sts_error;
1428 fnd_message.set_name('INV', 'SPLIT_LINE_DETAIL_ERROR');
1429 fnd_msg_pub.ADD;
1430 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1431 WHEN fnd_api.g_exc_unexpected_error THEN
1432 x_return_status := fnd_api.g_ret_sts_unexp_error;
1433 fnd_message.set_name('INV', 'SPLIT_LINE_DETAIL_ERROR');
1434 fnd_msg_pub.ADD;
1435 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1436 WHEN OTHERS THEN
1437 x_return_status := fnd_api.g_ret_sts_unexp_error;
1438
1439 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1440 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Split_Line_Details');
1441 END IF;
1442 END;
1443
1444 PROCEDURE auto_pick_confirm(
1445 p_line_id IN NUMBER
1446 , p_move_order_type IN NUMBER
1447 , x_return_status OUT NOCOPY VARCHAR2
1448 , x_msg_count OUT NOCOPY NUMBER
1449 , x_msg_data OUT NOCOPY VARCHAR2
1450 ) IS
1451 l_line_id NUMBER := p_line_id;
1452 l_return_status VARCHAR2(1);
1453 l_grouping_rule_id NUMBER;
1454 l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
1455 l_message VARCHAR2(2000);
1456 l_count NUMBER;
1457 l_from_serial_number VARCHAR2(30);
1458 l_to_serial_number VARCHAR2(30);
1459 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
1460 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1461 BEGIN
1462 --inv_debug.message('auto pick confirm');
1463 --inv_debug.message('get trolin');
1464 l_trolin_tbl := inv_trolin_util.query_rows(p_line_id => l_line_id);
1465 --inv_debug.message('get mold');
1466 l_mold_tbl := inv_mo_line_detail_util.query_rows(p_line_id => l_line_id);
1467 --inv_debug.message('number of mold record is ' || l_mold_tbl.count);
1468 inv_pick_wave_pick_confirm_pub.pick_confirm(
1469 p_api_version_number => 1.0
1470 , p_init_msg_list => fnd_api.g_false
1471 , p_commit => fnd_api.g_true
1472 , x_return_status => l_return_status
1473 , x_msg_count => x_msg_count
1474 , x_msg_data => x_msg_data
1475 , p_move_order_type => p_move_order_type
1476 , p_transaction_mode => 1
1477 , p_trolin_tbl => l_trolin_tbl
1478 , p_mold_tbl => l_mold_tbl
1479 , x_mmtt_tbl => l_mold_tbl
1480 , x_trolin_tbl => l_trolin_tbl
1481 );
1482
1483 --inv_debug.message('l_return_status = ' || l_return_status);
1484 IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1485 RAISE fnd_api.g_exc_unexpected_error;
1486 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1487 RAISE fnd_api.g_exc_error;
1488 END IF;
1489
1490 --null;
1491 x_return_status := l_return_status;
1492 EXCEPTION
1493 WHEN fnd_api.g_exc_error THEN
1494 x_return_status := fnd_api.g_ret_sts_error;
1495 fnd_message.set_name('INV', 'AUTO_PICK_CONFIRM_ERROR');
1496 fnd_msg_pub.ADD;
1497 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1498 WHEN fnd_api.g_exc_unexpected_error THEN
1499 x_return_status := fnd_api.g_ret_sts_unexp_error;
1500 fnd_message.set_name('INV', 'AUTO_PICK_CONFIRM_ERROR');
1501 fnd_msg_pub.ADD;
1502 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1503 WHEN OTHERS THEN
1504 x_return_status := fnd_api.g_ret_sts_unexp_error;
1505
1506 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1507 fnd_msg_pub.add_exc_msg(g_pkg_name, 'AUTO_PICK_CONFIRM');
1508 END IF;
1509 END;
1510
1511 PROCEDURE reserve_unconfirm_qty(
1512 p_reservation_id IN NUMBER
1513 , p_missing_quantity IN NUMBER
1514 , x_return_status OUT NOCOPY VARCHAR2
1515 , x_msg_count OUT NOCOPY NUMBER
1516 , x_msg_data OUT NOCOPY VARCHAR2
1517 ) IS
1518 l_reservation_id NUMBER := p_reservation_id;
1519 l_return_status VARCHAR2(1);
1520 l_missing_quantity NUMBER := p_missing_quantity;
1521 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1522 BEGIN
1523 -- call the reserve unconfirmed quantity
1524 inv_pick_release_pub.reserve_unconfirmed_quantity(
1525 p_api_version => 1.0
1526 , x_return_status => l_return_status
1527 , x_msg_count => x_msg_count
1528 , x_msg_data => x_msg_data
1529 , p_missing_quantity => l_missing_quantity
1530 , p_reservation_id => l_reservation_id
1531 );
1532
1533 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1534 RAISE fnd_api.g_exc_error;
1535 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1536 RAISE fnd_api.g_exc_unexpected_error;
1537 END IF;
1538
1539 x_return_status := l_return_status;
1540 EXCEPTION
1541 WHEN fnd_api.g_exc_error THEN
1542 x_return_status := fnd_api.g_ret_sts_error;
1543 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1544 WHEN fnd_api.g_exc_unexpected_error THEN
1545 x_return_status := fnd_api.g_ret_sts_unexp_error;
1546 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1547 WHEN OTHERS THEN
1548 x_return_status := fnd_api.g_ret_sts_unexp_error;
1549
1550 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1551 fnd_msg_pub.add_exc_msg(g_pkg_name, 'AUTO_PICK_CONFIRM');
1552 END IF;
1553 END;
1554
1555 PROCEDURE changed_from_subinventory(
1556 p_reservation_id IN NUMBER
1557 , p_transaction_temp_id IN NUMBER
1558 , p_old_subinventory IN VARCHAR2
1559 , p_new_subinventory IN VARCHAR2
1560 , p_new_locator_id IN NUMBER
1561 , x_to_reservation_id OUT NOCOPY NUMBER
1562 , x_return_status OUT NOCOPY VARCHAR2
1563 , x_msg_count OUT NOCOPY NUMBER
1564 , x_msg_data OUT NOCOPY VARCHAR2
1565 ) IS
1566 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
1567 l_mtl_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
1568 l_mtl_reservation_tbl_count NUMBER;
1569 l_return_status VARCHAR2(1);
1570 l_msg_count NUMBER;
1571 l_msg_data VARCHAR2(2000);
1572 l_error_code NUMBER;
1573 l_mmtt_rec inv_mo_line_detail_util.g_mmtt_rec;
1574 l_mmtt_count NUMBER;
1575 l_reservation_id NUMBER := p_reservation_id;
1576 l_original_serial_number inv_reservation_global.serial_number_tbl_type;
1577 l_to_serial_number inv_reservation_global.serial_number_tbl_type;
1578 l_reservable_type NUMBER;
1579
1580 CURSOR lot_csr(l_transaction_temp_id NUMBER) IS
1581 SELECT lot_number
1582 , primary_quantity
1583 , transaction_quantity
1584 , serial_transaction_temp_id
1585 FROM mtl_transaction_lots_temp
1586 WHERE transaction_temp_id = l_transaction_temp_id;
1587
1588 CURSOR serial_csr(l_transaction_id NUMBER, l_lot_control_code NUMBER, l_serial_trx_id NUMBER) IS
1589 SELECT serial_number
1590 FROM mtl_unit_transactions
1591 WHERE transaction_id = DECODE(l_lot_control_code, 1, l_transaction_id, l_serial_trx_id);
1592
1593 l_lot_count NUMBER;
1594 l_lot_control_code NUMBER;
1595 l_serial_control_code NUMBER;
1596 l_serial_trx_id NUMBER;
1597 l_transaction_temp_id NUMBER := p_transaction_temp_id;
1598 l_lot_number VARCHAR2(80);
1599 l_lot_primary_quantity NUMBER;
1600 l_lot_transaction_quantity NUMBER;
1601 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1602 BEGIN
1603 --inv_Debug.message('ssia', 'Inside changed_from_subinventory');
1604 l_mmtt_rec := inv_mo_line_detail_util.query_row(p_transaction_temp_id);
1605
1606 --inv_Debug.message('ssia', 'transaction_temp_id is ' || p_transaction_temp_id);
1607
1608 SELECT reservable_type
1609 INTO l_reservable_type
1610 FROM mtl_secondary_inventories
1611 WHERE organization_id = l_mmtt_rec.organization_id
1612 AND secondary_inventory_name = p_new_subinventory;
1613
1614 --inv_Debug.message('ssia', 'l_reservable_type is ' || l_reservable_Type);
1615 IF (p_reservation_id IS NOT NULL
1616 OR p_reservation_id > 0) THEN
1617 l_mtl_reservation_rec.reservation_id := l_mmtt_rec.reservation_id;
1618 inv_reservation_pub.query_reservation(
1619 p_api_version_number => 1.0
1620 , x_return_status => l_return_status
1621 , x_msg_count => l_msg_count
1622 , x_msg_data => l_msg_data
1623 , p_query_input => l_mtl_reservation_rec
1624 , x_mtl_reservation_tbl => l_mtl_reservation_tbl
1625 , x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count
1626 , x_error_code => l_error_code
1627 );
1628
1629 IF l_return_status = fnd_api.g_ret_sts_error THEN
1630 RAISE fnd_api.g_exc_error;
1631 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1632 RAISE fnd_api.g_exc_unexpected_error;
1633 END IF;
1634
1635 l_mtl_reservation_rec := l_mtl_reservation_tbl(1);
1636
1637 IF (l_debug = 1) THEN
1638 print_debug('lot number in the original reservation is ' || l_mtl_reservation_rec.lot_number);
1639 END IF;
1640
1641 IF (l_mtl_reservation_rec.subinventory_code IS NOT NULL) THEN
1642 IF (l_reservable_type = 2) THEN
1643 IF (l_debug = 1) THEN
1644 print_debug('not reservable staging subinventory, delete org wide reservation');
1645 END IF;
1646
1647 l_mtl_reservation_rec := l_mtl_reservation_tbl(1);
1648 l_mtl_reservation_rec.reservation_quantity :=
1649 NVL(l_mtl_reservation_rec.reservation_quantity, 0)
1650 - ABS(l_mmtt_rec.transaction_quantity);
1651 l_mtl_reservation_rec.primary_reservation_quantity :=
1652 NVL(l_mtl_reservation_rec.primary_reservation_quantity, 0)
1653 - ABS(l_mmtt_rec.primary_quantity);
1654
1655 IF (l_debug = 1) THEN
1656 print_debug('reservation quantity is ' || l_mtl_reservation_rec.reservation_quantity);
1657 END IF;
1658
1659 inv_reservation_pub.update_reservation(
1660 p_api_version_number => 1.0
1661 , p_init_msg_lst => fnd_api.g_false
1662 , x_return_status => l_return_status
1663 , x_msg_count => x_msg_count
1664 , x_msg_data => x_msg_data
1665 , p_original_rsv_rec => l_mtl_reservation_tbl(1)
1666 , p_to_rsv_rec => l_mtl_reservation_rec
1667 , p_original_serial_number => l_original_serial_number
1668 , p_to_serial_number => l_to_serial_number
1669 , p_validation_flag => fnd_api.g_true
1670 );
1671
1672 IF (l_debug = 1) THEN
1673 print_debug('after update reservation return status is ' || l_return_status);
1674 END IF;
1675
1676 IF l_return_status = fnd_api.g_ret_sts_error THEN
1677 RAISE fnd_api.g_exc_error;
1678 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1679 RAISE fnd_api.g_exc_unexpected_error;
1680 END IF;
1681 ELSE
1682 IF (l_debug = 1) THEN
1683 print_debug('reservable staging subinventory, transfer reservation to staging');
1684 END IF;
1685
1686 SELECT COUNT(transaction_temp_id)
1687 INTO l_lot_count
1688 FROM mtl_transaction_lots_temp
1689 WHERE transaction_temp_id = l_mmtt_rec.transaction_temp_id;
1690
1691 IF (l_lot_count > 0) THEN
1692 l_transaction_temp_id := l_mmtt_rec.transaction_temp_id;
1693 OPEN lot_csr(l_transaction_temp_id);
1694
1695 LOOP
1696 FETCH lot_csr INTO l_lot_number, l_lot_primary_quantity, l_lot_transaction_quantity, l_serial_trx_id;
1697 EXIT WHEN lot_csr%NOTFOUND;
1698
1699 IF (l_debug = 1) THEN
1700 print_debug('lot number is ' || l_mtl_reservation_rec.lot_number);
1701 END IF;
1702
1703 l_mtl_reservation_rec.reservation_id := NULL;
1704 l_mtl_reservation_rec.requirement_date := SYSDATE;
1705 l_mtl_reservation_rec.primary_reservation_quantity := l_lot_primary_quantity;
1706 l_mtl_reservation_rec.reservation_quantity := l_lot_transaction_quantity;
1707 l_mtl_reservation_rec.subinventory_code := p_new_subinventory;
1708 l_mtl_reservation_rec.locator_id := p_new_locator_id;
1709 l_mtl_reservation_rec.ship_ready_flag := 1;
1710 l_mtl_reservation_rec.lot_number := l_lot_number;
1711 l_mtl_reservation_rec.revision := l_mmtt_rec.revision;
1712 inv_reservation_pub.transfer_reservation(
1713 p_api_version_number => 1.0
1714 , p_init_msg_lst => fnd_api.g_false
1715 , x_return_status => l_return_status
1716 , x_msg_count => x_msg_count
1717 , x_msg_data => x_msg_data
1718 , p_original_rsv_rec => l_mtl_reservation_tbl(1)
1719 , p_to_rsv_rec => l_mtl_reservation_rec
1720 , p_original_serial_number => l_to_serial_number
1721 , p_to_serial_number => l_to_serial_number
1722 , x_to_reservation_id => x_to_reservation_id
1723 );
1724
1725 IF (l_debug = 1) THEN
1726 print_debug('new reservation id is ' || l_reservation_id);
1727 print_debug('after create new reservation');
1728 print_debug('l_return_status is ' || l_return_status);
1729 END IF;
1730
1731 /*for l_count in 1..x_msg_count loop
1732 l_message := fnd_msg_pub.get(l_count,'T');
1733 l_message := replace(l_message,chr(0),' ');
1734 IF (l_debug = 1) THEN
1735 print_debug(l_message);
1736 END IF;
1737 end loop; */
1738 IF l_return_status = fnd_api.g_ret_sts_error THEN
1739 IF (l_debug = 1) THEN
1740 print_debug('return from transfer_reservation with error E');
1741 END IF;
1742
1743 RAISE fnd_api.g_exc_error;
1744 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1745 IF (l_debug = 1) THEN
1746 print_debug('return from transfer_reservation with error U');
1747 END IF;
1748
1749 RAISE fnd_api.g_exc_unexpected_error;
1750 END IF;
1751 END LOOP;
1752
1753 IF (l_debug = 1) THEN
1754 print_debug('after end loop');
1755 END IF;
1756
1757 CLOSE lot_csr;
1758 ELSE
1759 IF (l_debug = 1) THEN
1760 print_debug('no lot records');
1761 END IF;
1762
1763 l_mtl_reservation_rec.reservation_id := NULL;
1764 l_mtl_reservation_rec.requirement_date := SYSDATE;
1765 l_mtl_reservation_rec.primary_reservation_quantity := ABS(l_mmtt_rec.primary_quantity);
1766 l_mtl_reservation_rec.reservation_quantity := ABS(l_mmtt_rec.transaction_quantity);
1767 l_mtl_reservation_rec.subinventory_code := p_new_subinventory;
1768 l_mtl_reservation_rec.locator_id := p_new_locator_id;
1769 l_mtl_reservation_rec.ship_ready_flag := 1;
1770 l_mtl_reservation_rec.revision := l_mmtt_rec.revision;
1771 --print_debug('lot number is ' || l_mtl_reservation_rec.lot_number);
1772 inv_reservation_pub.transfer_reservation(
1773 p_api_version_number => 1.0
1774 , p_init_msg_lst => fnd_api.g_false
1775 , x_return_status => l_return_status
1776 , x_msg_count => x_msg_count
1777 , x_msg_data => x_msg_data
1778 , p_original_rsv_rec => l_mtl_reservation_tbl(1)
1779 , p_to_rsv_rec => l_mtl_reservation_rec
1780 , p_original_serial_number => l_to_serial_number
1781 , p_to_serial_number => l_to_serial_number
1782 , x_to_reservation_id => x_to_reservation_id
1783 );
1784
1785 IF (l_debug = 1) THEN
1786 print_debug('new reservation id is ' || l_reservation_id);
1787 print_debug('after create new reservation');
1788 print_debug('l_return_status is ' || l_return_status);
1789 END IF;
1790
1791 /*for l_count in 1..x_msg_count loop
1792 l_message := fnd_msg_pub.get(l_count,'T');
1793 l_message := replace(l_message,chr(0),' ');
1794 IF (l_debug = 1) THEN
1795 print_debug(l_message);
1796 END IF;
1797 end loop; */
1798 IF l_return_status = fnd_api.g_ret_sts_error THEN
1799 IF (l_debug = 1) THEN
1800 print_debug('return from transfer_reservation with error E');
1801 END IF;
1802
1803 RAISE fnd_api.g_exc_error;
1804 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1805 IF (l_debug = 1) THEN
1806 print_debug('return from transfer_reservation with error U');
1807 END IF;
1808
1809 RAISE fnd_api.g_exc_unexpected_error;
1810 END IF;
1811
1812 IF (l_debug = 1) THEN
1813 print_debug('still inside if no lot records');
1814 END IF;
1815 END IF; -- lot or not lot control
1816 END IF; -- reservable or not
1817 END IF; -- if reservation exists
1818 END IF;
1819 END;
1820
1821 --Check_Shipping_Tolerances
1822 --
1823 -- This API checks to make sure that transacting the current allocation
1824 -- does not exceed shipping tolerances.
1825 -- This procedure should only be called for Pick Wave move orders
1826 -- p_line_id : the move order line id.
1827 -- p_quantity: the quantity to be transacted
1828 -- x_allowed: 'Y' if txn is allowed, 'N' otherwise
1829 -- x_max_quantity: the maximum quantity that can be pick confirmed
1830 -- without exceeding shipping tolerances
1831
1832 PROCEDURE check_shipping_tolerances(
1833 x_return_status OUT NOCOPY VARCHAR2
1834 , x_msg_count OUT NOCOPY NUMBER
1835 , x_msg_data OUT NOCOPY VARCHAR2
1836 , x_allowed OUT NOCOPY VARCHAR2
1837 , x_max_quantity OUT NOCOPY NUMBER
1838 , x_max_quantity2 OUT NOCOPY NUMBER --MUOM Fulfillment Project
1839 , p_line_id IN NUMBER
1840 , p_quantity IN NUMBER
1841 , p_quantity2 IN NUMBER --MUOM Fulfillment Project
1842 ) IS
1843 l_allowed VARCHAR2(1);
1844 l_max_quantity2 NUMBER;
1845 l_avail_req_qty2 NUMBER;
1846 l_return_status VARCHAR2(1);
1847 l_msg_count NUMBER;
1848 l_msg_data VARCHAR2(2000);
1849 l_api_name CONSTANT VARCHAR2(30) := 'Check_Shipping_Tolerances';
1850 l_txn_source_line_id NUMBER;
1851 l_max_quantity NUMBER;
1852 l_avail_req_qty NUMBER;
1853 l_mo_quantity NUMBER;
1854 l_mo_quantity2 NUMBER;
1855 l_quantity_delivered NUMBER;
1856 l_quantity_delivered2 NUMBER;
1857 l_organization_id NUMBER;
1858 l_inventory_item_id NUMBER;
1859 l_allocation_quantity NUMBER;
1860 l_line_set_id NUMBER;
1861
1862 -- MUOM Fulfillment Project
1863 l_allocation_quantity2 NUMBER;
1864 l_fulfill_base varchar2(1):='P';
1865 l_txn_source_type_id NUMBER;
1866
1867
1868 TYPE number_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1869
1870 l_quantity_table number_table_type;
1871
1872 CURSOR c_txn_source IS
1873 SELECT txn_source_line_id, inventory_item_id, transaction_source_type_id
1874 FROM mtl_txn_request_lines
1875 WHERE line_id = p_line_id;
1876
1877 CURSOR c_source_line IS
1878 SELECT source_line_id, inventory_item_id
1879 FROM wsh_delivery_details
1880 WHERE move_order_line_id = p_line_id;
1881
1882 CURSOR c_mtrl_line_set IS
1883 SELECT quantity
1884 FROM mtl_txn_request_lines
1885 WHERE inventory_item_id = l_inventory_item_id
1886 AND line_status <> 5
1887 AND txn_source_line_id IN(SELECT line_id
1888 FROM oe_order_lines_all
1889 WHERE line_set_id = l_line_set_id)
1890 FOR UPDATE OF quantity NOWAIT;
1891
1892 CURSOR c_mtrl_line IS
1893 SELECT quantity
1894 FROM mtl_txn_request_lines
1895 WHERE inventory_item_id = l_inventory_item_id
1896 AND organization_id = l_organization_id --bug 7012974 performance issue in TMO
1897 AND line_status <> 5
1898 AND txn_source_line_id = l_txn_source_line_id
1899 FOR UPDATE OF quantity NOWAIT;
1900
1901 record_locked EXCEPTION;
1902 PRAGMA EXCEPTION_INIT(record_locked, -54);
1903 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1904 BEGIN
1905 l_return_status := fnd_api.g_ret_sts_success;
1906
1907 BEGIN
1908 SELECT organization_id
1909 INTO l_organization_id
1910 FROM mtl_txn_request_lines
1911 WHERE line_id = p_line_id;
1912 EXCEPTION
1913 WHEN NO_DATA_FOUND THEN
1914 RAISE fnd_api.g_exc_unexpected_error;
1915 END;
1916
1917 --MUOM Fulfillment Project
1918 OPEN c_txn_source;
1919 FETCH c_txn_source INTO l_txn_source_line_id, l_inventory_item_id, l_txn_source_type_id;
1920
1921 IF c_txn_source%NOTFOUND THEN
1922 RAISE fnd_api.g_exc_error;
1923 END IF;
1924
1925 CLOSE c_txn_source;
1926
1927 -- If for some reason the txn_source_line_id on the move order line is
1928 -- not yet populated, get the order line directly from the delivery
1929 -- details
1930 IF l_txn_source_line_id IS NULL THEN
1931 OPEN c_source_line;
1932 FETCH c_source_line INTO l_txn_source_line_id, l_inventory_item_id;
1933
1934 IF c_txn_source%NOTFOUND THEN
1935 l_txn_source_line_id := NULL;
1936 END IF;
1937 END IF;
1938
1939 inv_utilities.get_inv_fulfillment_base(
1940 p_source_line_id => l_txn_source_line_id,
1941 p_demand_source_type_id => l_txn_source_type_id,
1942 p_org_id => l_organization_id,
1943 x_fulfillment_base => l_fulfill_base
1944 );
1945
1946 IF (l_debug = 1) THEN
1947 print_debug('l_txn_source_line_id '||l_txn_source_line_id||'l_txn_source_type_id '||l_txn_source_type_id||' Fulfillment base '|| l_fulfill_base);
1948 END IF;
1949
1950
1951 -- If overpicking is not turned on then the maximum quanitity that is
1952 -- allowed is the move order quantity
1953
1954 IF (NVL(fnd_profile.VALUE('WSH_OVERPICK_ENABLED'), 'N') <> 'Y') THEN
1955 IF (l_debug = 1) THEN
1956 print_debug('OVPK | Overpicking is not turned on');
1957 END IF;
1958
1959 SELECT NVL(required_quantity, quantity), NVL(quantity_delivered, 0) , NVL(secondary_required_quantity, secondary_quantity) ,NVL(secondary_quantity_delivered, 0)
1960 INTO l_mo_quantity, l_quantity_delivered, l_mo_quantity2, l_quantity_delivered2
1961 FROM mtl_txn_request_lines
1962 WHERE line_id = p_line_id;
1963
1964 l_max_quantity := l_mo_quantity - l_quantity_delivered;
1965 l_max_quantity2 := l_mo_quantity2 - l_quantity_delivered2;
1966
1967 IF (l_debug = 1) THEN
1968 print_debug('OVPK | p_quantity:' || p_quantity || ' max_quantity:' || l_max_quantity);
1969 print_debug('OVPK | p_quantity:' || p_quantity2 || ' max_quantity:' || l_max_quantity2);
1970 END IF;
1971
1972 -- MUOM Fulfillment Project added l_fulfill_base = 'P'
1973 IF nvl(l_fulfill_base,'P') = 'P' THEN
1974 IF ( p_quantity > l_max_quantity) THEN
1975 l_allowed := 'N';
1976 ELSE
1977 l_allowed := 'Y';
1978 END IF;
1979 ELSE
1980 --
1981 IF ( p_quantity2 > l_max_quantity2) THEN
1982 l_allowed := 'N';
1983 ELSE
1984 l_allowed := 'Y';
1985 END IF;
1986 END IF;
1987
1988 x_max_quantity := l_max_quantity;
1989 x_max_quantity2 := l_max_quantity2; -- MUOM Fulfillment Project
1990 x_allowed := l_allowed;
1991 x_return_status := l_return_status;
1992
1993 IF (l_debug = 1) THEN
1994 print_debug('OVPK | x_max_quantity:' || x_max_quantity || ' x_allowed:' || x_allowed || ' x_return_status: ' || l_return_status);
1995 print_debug('OVPK | x_max_quantity2:' || x_max_quantity2 || ' x_allowed:' || x_allowed || ' x_return_status: ' || l_return_status);
1996 END IF;
1997
1998 RETURN;
1999 END IF;
2000
2001 -- If overpicking is allowed...
2002 -- By default, allow the transaction.
2003 l_allowed := 'Y';
2004 l_max_quantity := 1e125;
2005 -- get sales order line id from the move order line
2006 /*** --MUOM Fulfillment Project moved code up in procedure..
2007 OPEN c_txn_source;
2008 FETCH c_txn_source INTO l_txn_source_line_id, l_inventory_item_id;
2009
2010 IF c_txn_source%NOTFOUND THEN
2011 RAISE fnd_api.g_exc_error;
2012 END IF;
2013
2014 CLOSE c_txn_source;
2015
2016 -- If for some reason the txn_source_line_id on the move order line is
2017 -- not yet populated, get the order line directly from the delivery
2018 -- details
2019 IF l_txn_source_line_id IS NULL THEN
2020 OPEN c_source_line;
2021 FETCH c_source_line INTO l_txn_source_line_id, l_inventory_item_id;
2022
2023 IF c_txn_source%NOTFOUND THEN
2024 l_txn_source_line_id := NULL;
2025 END IF;
2026 END IF;
2027 **/
2028
2029 IF l_txn_source_line_id IS NOT NULL THEN
2030
2031 --Bug# 3348005
2032
2033 BEGIN
2034 FOR mtrl_line IN c_mtrl_line LOOP
2035 NULL;
2036 END LOOP;
2037 EXCEPTION
2038 WHEN record_locked THEN
2039 fnd_message.set_name('INV', 'INV_MO_LOCKED_SO');
2040 fnd_msg_pub.ADD;
2041 RAISE fnd_api.g_exc_error;
2042 END;
2043
2044 SELECT NVL(SUM(ABS(transaction_quantity)), 0), NVL(SUM(ABS(secondary_transaction_quantity)), 0)
2045 INTO l_allocation_quantity , l_allocation_quantity2
2046 FROM mtl_material_transactions_temp
2047 WHERE move_order_line_id <> p_line_id
2048 AND inventory_item_id = l_inventory_item_id
2049 AND transaction_action_id = 28
2050 AND trx_source_line_id = l_txn_source_line_id;
2051
2052 l_allocation_quantity := l_allocation_quantity + p_quantity;
2053 l_allocation_quantity2 := l_allocation_quantity2 + p_quantity2;
2054
2055 --MUOM Fulfillment Project
2056 IF(l_fulfill_base<>'S') THEN
2057 wsh_details_validations.check_quantity_to_pick(
2058 p_order_line_id => l_txn_source_line_id
2059 , p_quantity_to_pick => l_allocation_quantity
2060 , x_allowed_flag => l_allowed
2061 , x_max_quantity_allowed => l_max_quantity
2062 , x_return_status => l_return_status
2063 , x_avail_req_quantity => l_avail_req_qty
2064 );
2065 ELSE
2066
2067 IF (l_debug = 1) THEN
2068 print_debug('Fulfill Base Value '||l_fulfill_base);
2069 print_debug('Allocation Qty in Primary uom '||l_allocation_quantity);
2070 print_debug('Allocation Qty in Secondary uom '||l_allocation_quantity2);
2071 END IF;
2072
2073 wsh_details_validations.check_quantity_to_pick(
2074 p_order_line_id => l_txn_source_line_id
2075 , p_quantity_to_pick => l_allocation_quantity
2076 , p_quantity2_to_pick => l_allocation_quantity2
2077 , x_allowed_flag => l_allowed
2078 , x_max_quantity_allowed => l_max_quantity
2079 , x_max_quantity2_allowed => l_max_quantity2
2080 , x_return_status => l_return_status
2081 , x_avail_req_quantity => l_avail_req_qty
2082 , x_avail_req_quantity2 => l_avail_req_qty2
2083 );
2084 END IF;
2085
2086 IF l_return_status = fnd_api.g_ret_sts_error THEN
2087 RAISE fnd_api.g_exc_error;
2088 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2089 RAISE fnd_api.g_exc_unexpected_error;
2090 END IF;
2091 END IF;
2092
2093 x_max_quantity := l_max_quantity;
2094 x_max_quantity2 := l_max_quantity2;
2095 x_allowed := l_allowed;
2096 x_return_status := l_return_status;
2097 EXCEPTION
2098 WHEN fnd_api.g_exc_error THEN
2099 x_return_status := fnd_api.g_ret_sts_error;
2100 x_allowed := 'N';
2101 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2102 WHEN fnd_api.g_exc_unexpected_error THEN
2103 x_return_status := fnd_api.g_ret_sts_error;
2104 x_allowed := 'N';
2105 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2106 WHEN OTHERS THEN
2107 x_return_status := fnd_api.g_ret_sts_unexp_error;
2108 x_allowed := 'N';
2109
2110 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2111 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2112 END IF;
2113 END check_shipping_tolerances;
2114
2115 -- OVPK
2116 -- Get_Overpick_Qty
2117 --
2118 -- This API will take 2 input parameters
2119 -- 1. p_transaction_temp_id
2120 -- 2. p_overpicked_qty
2121 -- This API will return
2122 -- 1. x_ovpk_allowed
2123 -- 2. x_max_qty_allowed
2124 -- x_ovpk_allowed will be 0 if overpicking is not allowed
2125 -- x_ovpk_allowed will be 1 if overpicking is allowed
2126 -- x_max_qty_allowed will return the max qty that can be picked for that task
2127
2128 -- For Manufacturing Component Pick - Move Order type 5,
2129 -- Replenishment - Move Order type 2,
2130 -- Requisition - Move Order type 1
2131 -- where there is no tolerance set on the quantity that can be picked,
2132 -- this procedure will return x_max_qty_allowed as -1
2133
2134 PROCEDURE get_overpick_qty(
2135 p_transaction_temp_id IN NUMBER
2136 , p_overpicked_qty IN NUMBER
2137 , p_overpicked_qty2 IN NUMBER
2138 , x_ovpk_allowed OUT NOCOPY NUMBER
2139 , x_max_qty_allowed OUT NOCOPY NUMBER
2140 , x_max_qty_allowed2 OUT NOCOPY NUMBER
2141 , x_return_status OUT NOCOPY VARCHAR2
2142 , x_msg_count OUT NOCOPY NUMBER
2143 , x_msg_data OUT NOCOPY VARCHAR2
2144 ) IS
2145 l_org_id NUMBER;
2146 l_move_order_type NUMBER;
2147 l_transaction_temp_id NUMBER := p_transaction_temp_id;
2148 l_trx_source_line_id NUMBER;
2149 l_all_alloc NUMBER;
2150 l_other_alloc NUMBER;
2151 l_this_alloc NUMBER;
2152 l_all_alloc2 NUMBER;
2153 l_other_alloc2 NUMBER;
2154 l_this_alloc2 NUMBER;
2155
2156 l_temp VARCHAR2(1);
2157 l_new_qty NUMBER;
2158 l_new_qty2 NUMBER;
2159 --Check_Shipping_Tolerances
2160 l_msg_count NUMBER;
2161 l_msg_data VARCHAR2(2000);
2162 l_mo_line_id NUMBER;
2163 --l_ordered_quantity NUMBER := -1;
2164
2165 --Shipping
2166 l_allowed_flag VARCHAR2(20);
2167 l_max_quantity_allowed NUMBER;
2168 l_max_quantity_allowed2 NUMBER;
2169 l_avail_req_quantity NUMBER;
2170 l_return_status VARCHAR2(1);
2171 --For DB log
2172 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2173 BEGIN
2174 x_return_status := fnd_api.g_ret_sts_success;
2175
2176 --Resolve org_id and move_order_type from the passed transaction_temp_id
2177 SELECT mmtt.organization_id
2178 , mtrh.move_order_type
2179 INTO l_org_id
2180 , l_move_order_type
2181 FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
2182 WHERE mmtt.move_order_line_id = mtrl.line_id
2183 AND mtrl.header_id = mtrh.header_id
2184 AND mmtt.transaction_temp_id = p_transaction_temp_id;
2185
2186 IF (l_debug = 1) THEN
2187 print_debug('OVPK: Entered get_overpick_qty with the following params');
2188 --print_debug('OVPK: p_org_id = ' || p_org_id);
2189 --print_debug('OVPK: p_move_order_type = ' || p_move_order_type);
2190 print_debug('OVPK: p_transaction_temp_id = ' || p_transaction_temp_id);
2191 print_debug('OVPK: p_overpicked_qty = ' || p_overpicked_qty);
2192 print_debug('OVPK: p_overpicked_qty2 = ' || p_overpicked_qty2);
2193 print_debug('OVPK: Org_id = ' || l_org_id);
2194 print_debug('OVPK: Move Order Type = ' || l_move_order_type);
2195 END IF;
2196
2197 -- For a Replenishment (MO Type 2) / Requisition (MO Type 1)
2198 -- lookup the flag OVPK_TRANSFER_ORDERS_ENABLED in mtl_parameters
2199
2200 IF (l_move_order_type IN
2201 (inv_globals.g_move_order_replenishment, inv_globals.g_move_order_requisition)
2202 ) THEN
2203 SELECT OVPK_TRANSFER_ORDERS_ENABLED
2204 INTO l_temp
2205 FROM mtl_parameters
2206 WHERE organization_id = l_org_id;
2207
2208 IF (NVL(l_temp, 'Y') = 'Y') THEN
2209 x_ovpk_allowed := 1; --Overpicking is allowed
2210 ELSE
2211 x_ovpk_allowed := 0; --Overpicking is not allowed
2212 END IF;
2213
2214 x_max_qty_allowed := -1;
2215
2216 IF (l_debug = 1) THEN
2217 print_debug('OVPK: In MTL_PARAMETERS OVPK_TRANSFER_ORDERS_ENABLED = ' || l_temp);
2218 print_debug('OVPK: x_ovpk_allowed = ' || x_ovpk_allowed);
2219 print_debug('OVPK: x_max_qty_allowed = ' || x_max_qty_allowed);
2220 print_debug('OVPK: Returning from get_overpick_qty');
2221 END IF;
2222
2223 RETURN;
2224 END IF;
2225
2226 -- For a Manufacturing Component Pick(WIP) MO
2227 -- lookup the flag WIP_OVERPICK_ENABLED in mtl_parameters
2228 -- MO Type 5
2229 IF (l_move_order_type = inv_globals.g_move_order_mfg_pick) THEN
2230 SELECT wip_overpick_enabled
2231 INTO l_temp
2232 FROM mtl_parameters
2233 WHERE organization_id = l_org_id;
2234
2235 IF (NVL(l_temp, 'N') = 'Y') THEN
2236 x_ovpk_allowed := 1; --Overpicking is allowed
2237 ELSE
2238 x_ovpk_allowed := 0; --Overpicking is not allowed
2239 END IF;
2240
2241 x_max_qty_allowed := -1;
2242
2243 IF (l_debug = 1) THEN
2244 print_debug('OVPK: In MTL_PARAMETERS wip_overpick_enabled = ' || l_temp);
2245 print_debug('OVPK: x_ovpk_allowed = ' || x_ovpk_allowed);
2246 print_debug('OVPK: x_max_qty_allowed = ' || x_max_qty_allowed);
2247 print_debug('OVPK: Returning from get_overpick_qty');
2248 END IF;
2249
2250 RETURN;
2251 END IF;
2252
2253 -- For a Pick Wave MO lookup the profile WSH_OVERPICKING_ENABLED
2254 -- MO Type 3
2255
2256 IF (l_debug = 1) THEN
2257 print_debug('OVPK: fnd_profile WSH_OVERPICK_ENABLED = ' || fnd_profile.VALUE('WSH_OVERPICK_ENABLED'));
2258 END IF;
2259
2260 IF (l_move_order_type = inv_globals.g_move_order_pick_wave
2261 AND NVL(fnd_profile.VALUE('WSH_OVERPICK_ENABLED'), 'N') = 'Y') THEN
2262 -- OVPK is allowed
2263 x_ovpk_allowed := 1;
2264
2265 -- Query the TRX_SOURCE_LINE_ID from MMTT for the given transaction_temp_id
2266 -- Get the transaction_quantity for the given transaction_temp_id
2267 SELECT trx_source_line_id
2268 , transaction_quantity
2269 , move_order_line_id
2270 , secondary_transaction_quantity
2271 INTO l_trx_source_line_id
2272 , l_this_alloc
2273 , l_mo_line_id
2274 , l_this_alloc2
2275 FROM mtl_material_transactions_temp
2276 WHERE transaction_temp_id = l_transaction_temp_id;
2277
2278 IF (l_debug = 1) THEN
2279 print_debug('OVPK: l_trx_source_line_id = ' || l_trx_source_line_id);
2280 print_debug('OVPK: l_this_alloc = ' || l_this_alloc);
2281 print_debug('OVPK: l_this_alloc2 = ' || l_this_alloc2);
2282 print_debug('OVPK: l_mo_line_id = ' || l_mo_line_id);
2283 END IF;
2284
2285 -- Get the sum of all allocations for that trx_source_line_id
2286 SELECT SUM(transaction_quantity) , sum(secondary_transaction_quantity)
2287 INTO l_all_alloc , l_all_alloc2
2288 FROM mtl_material_transactions_temp
2289 WHERE trx_source_line_id = l_trx_source_line_id;
2290
2291 -- The difference will be l_other_alloc
2292 l_other_alloc := l_all_alloc - l_this_alloc;
2293 l_other_alloc2 := l_all_alloc2 - l_this_alloc2;
2294
2295 -- l_this_alloc is the suggested quantity
2296 -- To this, add p_overpicked_qty to get l_new_qty
2297 l_new_qty := l_this_alloc + p_overpicked_qty;
2298 l_new_qty2 := l_this_alloc2 + p_overpicked_qty2;
2299
2300 IF (l_debug = 1) THEN
2301 print_debug('OVPK: l_all_alloc = ' || l_all_alloc);
2302 print_debug('OVPK: l_other_alloc = l_all_alloc - l_this_alloc = ' || l_other_alloc);
2303 print_debug('OVPK: l_other_alloc2 = l_all_alloc2 - l_this_alloc2 = ' || l_other_alloc2);
2304 print_debug('OVPK: Calling inv_replenish_detail_pub.check_shipping_tolerances');
2305 print_debug('OVPK: With the following params');
2306 print_debug('OVPK: l_mo_line_id = ' || l_mo_line_id);
2307 print_debug('OVPK: p_new_qty = ' || l_new_qty);
2308 print_debug('OVPK: l_new_qty2 = ' || l_new_qty2);
2309 END IF;
2310
2311 -- Call the shipping API to get l_max_quantity_allowed for this particular SO Line
2312 IF l_trx_source_line_id IS NOT NULL THEN
2313 inv_replenish_detail_pub.check_shipping_tolerances(
2314 x_return_status => l_return_status
2315 , x_msg_count => l_msg_count
2316 , x_msg_data => l_msg_data
2317 , x_allowed => l_allowed_flag
2318 , x_max_quantity => l_max_quantity_allowed
2319 , x_max_quantity2 => l_max_quantity_allowed2
2320 , p_line_id => l_mo_line_id
2321 , p_quantity => l_new_qty
2322 , p_quantity2 => l_new_qty2
2323 );
2324
2325 IF (l_debug = 1) THEN
2326 print_debug('OVPK: Returned from check_shipping_tolerances');
2327 print_debug('OVPK: x_return_status = ' || l_return_status);
2328 print_debug('OVPK: x_max_quantity = ' || l_max_quantity_allowed);
2329 print_debug('OVPK: x_max_quantity2 = ' || l_max_quantity_allowed2);
2330 END IF;
2331
2332 IF l_return_status = fnd_api.g_ret_sts_error THEN
2333 RAISE fnd_api.g_exc_error;
2334 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2335 RAISE fnd_api.g_exc_unexpected_error;
2336 END IF;
2337 END IF;
2338
2339 /*
2340 --If the new qty is greater than l_max_quantity_allowed then overpicking is not allowed
2341 IF (l_new_qty > l_max_quantity_allowed) THEN
2342 x_ovpk_allowed := 0;
2343 ELSE
2344 x_ovpk_allowed := 1;
2345 END IF;
2346 */
2347
2348 -- Calculate the max pick qty allowed for that task
2349 -- ie
2350 x_max_qty_allowed := l_max_quantity_allowed - l_other_alloc;
2351 x_max_qty_allowed2 := l_max_quantity_allowed2 - l_other_alloc2;
2352
2353 IF (l_debug = 1) THEN
2354 print_debug('OVPK: x_ovpk_allowed = ' || x_ovpk_allowed);
2355 print_debug('OVPK: The max quantity that can be allowed for this MMTT record is');
2356 print_debug('OVPK: Qty allowed by shipping API - other allocations = ' || x_max_qty_allowed);
2357 print_debug('OVPK: Secondary Qty allowed by shipping API - other allocations = ' || x_max_qty_allowed2);
2358 END IF;
2359
2360 RETURN;
2361 ELSE
2362 x_ovpk_allowed := 0;
2363 x_max_qty_allowed := -1;
2364
2365 IF (l_debug = 1) THEN
2366 print_debug('OVPK: x_ovpk_allowed = ' || x_ovpk_allowed);
2367 print_debug('OVPK: The max quantity that can be allowed for this MMTT record is');
2368 print_debug('OVPK: Qty allowed by shipping API - other allocations = ' || x_max_qty_allowed);
2369 END IF;
2370
2371 RETURN;
2372 END IF;
2373 EXCEPTION
2374 WHEN fnd_api.g_exc_error THEN
2375 x_return_status := fnd_api.g_ret_sts_error;
2376 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2377 WHEN fnd_api.g_exc_unexpected_error THEN
2378 x_return_status := fnd_api.g_ret_sts_unexp_error;
2379 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2380 WHEN OTHERS THEN
2381 x_return_status := fnd_api.g_ret_sts_unexp_error;
2382
2383 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2384 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Get_Overpick_Qty');
2385 END IF;
2386 END get_overpick_qty;
2387
2388 -- OVPK
2389 -- Check_Overpick
2390 --
2391 -- This API will take 10 input parameters
2392 -- 1. p_transaction_temp_id
2393 -- 2. p_overpicked_qty
2394 -- 3. p_item_id
2395 -- 4. p_rev
2396 -- 5. p_lot_num
2397 -- 6. p_lot_exp_date
2398 -- 7. p_sub
2399 -- 8. p_locator_id
2400 -- 9. p_lpn_id
2401 -- 10. p_overpicked_qty2
2402 -- The procedure check_overpick will be called from the client java file
2403 -- This API check_overpick will in turn call Get_Overpick_Qty to find
2404 -- a) Is overpicking allowed for given Org, MO type and transaction_temp_id ?
2405 -- b) What is the max quantity that can be overpicked ?
2406 -- It will then log the appropriate error message if the user encounters such a state,
2407 -- such as 'Overpicking not allowed' or 'Insufficient stock' or 'Shipping Tolerance exceeded'
2408 -- Otherwise it will update QUANTITY_DETAILED in MTRL (if it is not a bulk picked task)
2409 -- and return control to the calling routine, with x_check_overpick_passed set to 'Y'
2410 -- thereby allowing him to overpick.
2411 -- This OUT param will return 1 for error INV_OVERPICK_NOT_ALLOWED
2412 -- 2 for error INV_LACK_MTRL_TO_OVERPICK
2413 -- 3 for error INV_OVERSHIP_TOLERANCE
2414 -- The procedure is overloaded for MUOm Fulfillment Project.
2415
2416 PROCEDURE check_overpick(
2417 p_transaction_temp_id IN NUMBER
2418 , p_overpicked_qty IN NUMBER
2419 , p_overpicked_qty2 IN NUMBER
2420 , p_item_id IN NUMBER
2421 , p_rev IN VARCHAR2
2422 , p_lot_num IN VARCHAR2
2423 , p_lot_exp_date IN DATE
2424 , p_sub IN VARCHAR2
2425 , p_locator_id IN NUMBER
2426 , p_lpn_id IN NUMBER
2427 , x_check_overpick_passed OUT NOCOPY VARCHAR
2428 , x_ovpk_error_code OUT NOCOPY NUMBER
2429 , x_return_status OUT NOCOPY VARCHAR2
2430 , x_msg_count OUT NOCOPY NUMBER
2431 , x_msg_data OUT NOCOPY VARCHAR2
2432 ) IS
2433 l_org_id NUMBER;
2434 l_temp NUMBER;
2435 l_op_plan_id NUMBER;
2436 l_is_wms_enabled BOOLEAN := FALSE;
2437 l_ovpk_allowed NUMBER;
2438 l_max_qty_allowed NUMBER;
2439 l_max_qty_allowed2 NUMBER;
2440 l_this_alloc NUMBER;
2441 l_this_alloc2 NUMBER;
2442 l_mo_line_id NUMBER;
2443 -- For calling query_quantities
2444 l_msg_count NUMBER;
2445 l_msg_data VARCHAR2(2000);
2446 l_ser_code NUMBER;
2447 l_lot_code NUMBER;
2448 l_rev_code NUMBER;
2449 l_is_rev_controlled BOOLEAN := FALSE;
2450 l_is_lot_controlled BOOLEAN := FALSE;
2451 l_is_ser_controlled BOOLEAN := FALSE;
2452 x_qoh NUMBER;
2453 x_att NUMBER;
2454 l_rqoh NUMBER;
2455 l_qr NUMBER;
2456 l_qs NUMBER;
2457 l_atr NUMBER;
2458 l_alloc_lpn_id mtl_material_transactions_temp.allocated_lpn_id%TYPE;
2459 --l_txn_qty NUMBER;
2460 --For DB log
2461 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2462
2463 l_fulfill_base mtl_material_transactions_temp.fulfillment_base%TYPE;
2464 l_source_line_id number;
2465 l_txn_source_type_id NUMBER;
2466 BEGIN
2467 x_return_status := fnd_api.g_ret_sts_success;
2468 x_check_overpick_passed := 'N';
2469
2470 --l_txn_qty := p_overpicked_qty;
2471 --Resolve org_id,transaction_quantity, move_order_line_id
2472 --from the transaction_temp_id
2473 SELECT organization_id
2474 , transaction_quantity
2475 , move_order_line_id
2476 , allocated_lpn_id
2477 , secondary_transaction_quantity
2478 , trx_source_line_id
2479 , transaction_source_type_id
2480 , nvl(fulfillment_base, 'P')
2481 INTO l_org_id
2482 , l_this_alloc
2483 , l_mo_line_id
2484 , l_alloc_lpn_id
2485 , l_this_alloc2
2486 , l_source_line_id
2487 , l_txn_source_type_id
2488 , l_fulfill_base
2489 FROM mtl_material_transactions_temp
2490 WHERE transaction_temp_id = p_transaction_temp_id;
2491
2492 IF (l_debug = 1) THEN
2493 print_debug('OVPK: Entered check_overpick with the following params');
2494 print_debug('OVPK: p_transaction_temp_id = ' || p_transaction_temp_id);
2495 print_debug('OVPK: l_org_id = ' || l_org_id);
2496 print_debug('OVPK: p_item_id = ' || p_item_id);
2497 print_debug('OVPK: p_overpicked_qty = ' || p_overpicked_qty);
2498 print_debug('OVPK: p_overpicked_qty2 = ' || p_overpicked_qty2);
2499 print_debug('OVPK: p_rev ' || p_rev);
2500 print_debug('OVPK: p_lot_num ' || p_lot_num);
2501 print_debug('OVPK: p_sub ' || p_sub);
2502 print_debug('OVPK: p_locator_id ' || p_locator_id);
2503 print_debug('OVPK: l_this_alloc = ' || l_this_alloc);
2504 print_debug('OVPK: p_lpn_id ' || p_lpn_id);
2505 print_debug('OVPK: l_mo_line_id = ' || l_mo_line_id);
2506 print_debug('OVPK: l_org_id = ' || l_org_id);
2507 print_debug('OVPK: l_alloc_lpn_id ' || l_alloc_lpn_id);
2508 print_debug('OVPK: Fulfill Base '||l_fulfill_base);
2509 END IF;
2510
2511 -- For a WMS Org, for a Bulk pick task this API will never get called
2512 IF inv_install.adv_inv_installed(l_org_id) THEN
2513 l_is_wms_enabled := TRUE;
2514
2515 IF (l_debug = 1) THEN
2516 print_debug('OVPK: This org is WMS enabled');
2517 END IF;
2518 END IF;
2519
2520 IF (l_debug = 1) THEN
2521 print_debug('OVPK: Calling get_overpick_qty with the following params');
2522 print_debug('OVPK: p_transaction_temp_id = ' || p_transaction_temp_id);
2523 print_debug('OVPK: p_overpicked_qty = ' || p_overpicked_qty);
2524 print_debug('OVPK: p_overpicked_qty2 = ' || p_overpicked_qty2);
2525 END IF;
2526
2527 --Call INV_Replenish_Detail_Pub.Get_Overpick_Qty
2528 --to find out if overpicking is allowed
2529 --and if allowed,to what extent
2530 inv_replenish_detail_pub.get_overpick_qty(
2531 p_transaction_temp_id => p_transaction_temp_id
2532 , p_overpicked_qty => p_overpicked_qty
2533 , p_overpicked_qty2 => p_overpicked_qty2
2534 , x_ovpk_allowed => l_ovpk_allowed
2535 , x_max_qty_allowed => l_max_qty_allowed
2536 , x_max_qty_allowed2 => l_max_qty_allowed2
2537 , x_return_status => x_return_status
2538 , x_msg_count => x_msg_count
2539 , x_msg_data => x_msg_data
2540 );
2541
2542 IF (l_debug = 1) THEN
2543 print_debug('OVPK: get_overpick_qty returned the following');
2544 print_debug('OVPK: x_return_status = ' || x_return_status);
2545 print_debug('OVPK: l_ovpk_allowed = ' || l_ovpk_allowed);
2546 print_debug('OVPK: l_max_qty_allowed = ' || l_max_qty_allowed);
2547 print_debug('OVPK: l_max_qty_allowed2 = ' || l_max_qty_allowed2);
2548 END IF;
2549
2550 IF x_return_status = fnd_api.g_ret_sts_error THEN
2551 RAISE fnd_api.g_exc_error;
2552 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2553 RAISE fnd_api.g_exc_unexpected_error;
2554 END IF;
2555
2556
2557
2558 --Overpicked quantity is passed as one of the params to this procedure
2559 --so no need of any code here to determine overpicked quantity
2560
2561 --Is overpicking allowed i.e. Is x_ovpk_allowed = 1 ?
2562 IF l_ovpk_allowed = 1 THEN
2563 --Find out if the item is lot/serial controlled in tht chosen org
2564 -- comment out
2565 /*SELECT serial_number_control_code
2566 , lot_control_code
2567 , revision_qty_control_code
2568 INTO l_ser_code
2569 , l_lot_code
2570 , l_rev_code
2571 FROM mtl_system_items
2572 WHERE inventory_item_id = p_item_id
2573 AND organization_id = l_org_id;
2574
2575 IF (l_ser_code <> 1) THEN
2576 l_is_ser_controlled := TRUE;
2577 END IF;
2578
2579 IF (l_lot_code <> 1) THEN
2580 l_is_lot_controlled := TRUE;
2581 END IF;
2582
2583 IF (l_rev_code <> 1) THEN
2584 l_is_rev_controlled := TRUE;
2585 END IF;
2586
2587 --In the same Sub/Loc/Lot/LPN find out tempQty
2588 --Call inv_quantity_tree_pub.query_quantities and get att
2589 IF (l_debug = 1) THEN
2590 print_debug('OVPK: Calling query_quantities');
2591 END IF;
2592
2593 inv_quantity_tree_pub.query_quantities(
2594 p_api_version_number => 1.0
2595 , p_init_msg_lst => 'F'
2596 , x_return_status => x_return_status
2597 , x_msg_count => l_msg_count
2598 , x_msg_data => l_msg_data
2599 , p_organization_id => l_org_id
2600 , p_inventory_item_id => p_item_id
2601 , p_tree_mode => 1
2602 , p_is_revision_control => l_is_rev_controlled
2603 , p_is_lot_control => l_is_lot_controlled
2604 , p_is_serial_control => l_is_ser_controlled
2605 , p_demand_source_type_id => NULL
2606 , p_revision => p_rev
2607 , p_lot_number => p_lot_num
2608 , p_lot_expiration_date => p_lot_exp_date
2609 , p_subinventory_code => p_sub
2610 , p_locator_id => p_locator_id
2611 , p_onhand_source => 3
2612 , p_lpn_id => p_lpn_id
2613 , x_qoh => x_qoh
2614 , x_rqoh => l_rqoh
2615 , x_qr => l_qr
2616 , x_qs => l_qs
2617 , x_att => x_att
2618 , x_atr => l_atr
2619 );
2620
2621 IF (l_debug = 1) THEN
2622 print_debug('OVPK: Returned from query_quantities');
2623 print_debug('OVPK: x_return_status = ' || x_return_status);
2624 print_debug('OVPK: x_att = ' || x_att);
2625 END IF;
2626
2627 IF x_return_status = fnd_api.g_ret_sts_error THEN
2628 RAISE fnd_api.g_exc_error;
2629 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2630 RAISE fnd_api.g_exc_unexpected_error;
2631 END IF;
2632
2633 -- Bug 3361293 There are 3 cases for a user to pick
2634 1. Allocations are for loose quantity or LPN- User is trying to pick
2635 from an unallocated LPN.
2636 2. Allocations are for LPN - User is trying to pick loose quantity
2637 3. User is picking from the allocations only (loose or packed).
2638
2639 IF
2640 picking from an unallocated lpn, when allocations are for another lpn
2641 OR picking loose when allocation is from an lpn
2642 OR picking from an lpn when allocations are from loose
2643 THEN
2644 compare x_att against picked qty
2645 ELSE
2646 compare x_att with the over_picked_quantity to proceed further
2647 END IF
2648 -- end of bug 3361293
2649
2650 IF ( (l_alloc_lpn_id IS NOT NULL AND p_lpn_id IS NOT NULL
2651 AND l_alloc_lpn_id <> p_lpn_id)
2652 OR (l_alloc_lpn_id IS NOT NULL AND p_lpn_id IS NULL)
2653 OR (l_alloc_lpn_id IS NULL AND p_lpn_id IS NOT NULL))
2654 THEN
2655 IF (l_debug = 1) THEN
2656 print_debug('OVPK: User is not picking from the allocations');
2657 END IF;
2658 l_txn_qty := l_this_alloc + p_overpicked_qty;
2659 ELSE
2660 IF l_debug =1 THEN
2661 print_debug('OVPK: User is picking from the allocations');
2662 END IF;
2663 l_txn_qty := p_overpicked_qty;
2664 END IF;
2665
2666 IF l_debug =1 THEN
2667 print_debug('OVPK: l_txn_qty ' || l_txn_qty);
2668 END IF;*/
2669
2670 -- 10/12/04 comment out the if condition and else part
2671 -- the att check does not cover all the cases. remove the att part from this API
2672 -- and only do tolerance check. Leave the att check to Java
2673 --IF (x_att >= l_txn_qty) THEN
2674 IF (l_max_qty_allowed <> -1
2675 AND (l_this_alloc + p_overpicked_qty) > l_max_qty_allowed) AND l_fulfill_base='P' THEN
2676 -- Show ERROR : pickedQty is not within overship tolerance level
2677 IF (l_debug = 1) THEN
2678 print_debug('OVPK: pickedQty is not within overship tolerance level');
2679 print_debug('OVPK: Erroring out');
2680 END IF;
2681
2682 fnd_message.set_name('INV', 'INV_OVERSHIP_TOLERANCE');
2683 x_ovpk_error_code := 3;
2684 fnd_msg_pub.ADD;
2685 RAISE fnd_api.g_exc_error;
2686 ELSIF (l_max_qty_allowed2 <> -1
2687 AND (l_this_alloc2 + p_overpicked_qty2) > l_max_qty_allowed2) AND l_fulfill_base='S' THEN
2688 -- Show ERROR : pickedQty is not within overship tolerance level
2689 IF (l_debug = 1) THEN
2690 print_debug('OVPK: pickedQty is not within overship tolerance level');
2691 print_debug('OVPK: Erroring out');
2692 END IF;
2693
2694 fnd_message.set_name('INV', 'INV_OVERSHIP_TOLERANCE');
2695 x_ovpk_error_code := 3;
2696 fnd_msg_pub.ADD;
2697 RAISE fnd_api.g_exc_error;
2698
2699 ELSE
2700 --Allow the user to overpick
2701 x_check_overpick_passed := 'Y';
2702
2703 --Bug #4762505
2704 --Commenting out the update statement below as it was causing the
2705 --quantity_detailed in MOL to be wrongly updated with the current MMTT qty
2706 --Updates to MOL quantity_detailed should be done after updating MMTT.
2707 --Update QUANTITY_DETAILED in MTRL
2708 /* UPDATE mtl_txn_request_lines
2709 SET quantity_detailed = l_this_alloc + p_overpicked_qty
2710 WHERE line_id = l_mo_line_id;
2711 */
2712
2713 IF (l_debug = 1) THEN
2714 print_debug('OVPK: x_check_overpick_passed = ' || x_check_overpick_passed);
2715 print_debug('OVPK: For MO line_id = ' || l_mo_line_id);
2716 print_debug('OVPK: Updated quantity_detailed column in MTRL to ' || l_this_alloc || '+' || p_overpicked_qty);
2717 print_debug('OVPK: Returning from check_overpick');
2718 END IF;
2719 RETURN;
2720 END IF;
2721 --ELSE
2722 --ERROR : Not enough material to pick in Sub/Loc/Lot/LPN
2723 /*IF l_debug = 1 THEN
2724 print_debug('OVPK: Not enough material to pick in Sub/Loc/Lot/LPN');
2725 print_debug('OVPK: Erroring out');
2726 END IF;
2727
2728 fnd_message.set_name('INV', 'INV_LACK_MTRL_TO_OVERPICK');
2729 x_ovpk_error_code := 2;
2730 fnd_msg_pub.ADD;
2731 RAISE fnd_api.g_exc_error;*/
2732 --END IF;
2733 ELSE
2734 --ERROR Overpicking is not allowed in this org and for this Move Order type
2735 IF l_debug = 1 THEN
2736 print_debug('OVPK: Overpicking is not allowed in this org and for this Move Order type');
2737 print_debug('OVPK: Erroring out');
2738 END IF;
2739
2740 fnd_message.set_name('INV', 'INV_OVERPICK_NOT_ALLOWED');
2741 x_ovpk_error_code := 1;
2742 fnd_msg_pub.ADD;
2743 RAISE fnd_api.g_exc_error;
2744 END IF;
2745 EXCEPTION
2746 WHEN fnd_api.g_exc_error THEN
2747 x_return_status := fnd_api.g_ret_sts_error;
2748 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2749 WHEN fnd_api.g_exc_unexpected_error THEN
2750 x_return_status := fnd_api.g_ret_sts_unexp_error;
2751 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2752 WHEN OTHERS THEN
2753 x_return_status := fnd_api.g_ret_sts_unexp_error;
2754
2755 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2756 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Check_Overpick');
2757 END IF;
2758 END check_overpick;
2759
2760
2761
2762 -- OVPK
2763 -- Check_Overpick(Overloaded procedure )
2764 --
2765 -- This API will take 10 input parameters
2766 -- 1. p_transaction_temp_id
2767 -- 2. p_overpicked_qty
2768 -- 3. p_item_id
2769 -- 4. p_rev
2770 -- 5. p_lot_num
2771 -- 6. p_lot_exp_date
2772 -- 7. p_sub
2773 -- 8. p_locator_id
2774 -- 9. p_lpn_id
2775 -- 10 p_att
2776 -- The procedure check_overpick will be called from the client java file
2777 -- This API check_overpick will in turn call Get_Overpick_Qty to find
2778 -- a) Is overpicking allowed for given Org, MO type and transaction_temp_id ?
2779 -- b) What is the max quantity that can be overpicked ?
2780 -- It will then log the appropriate error message if the user encounters such a state,
2781 -- such as 'Overpicking not allowed' or 'Insufficient stock' or 'Shipping Tolerance exceeded'
2782 -- Otherwise it will update QUANTITY_DETAILED in MTRL (if it is not a bulk picked task)
2783 -- and return control to the calling routine, with x_check_overpick_passed set to 'Y'
2784 -- thereby allowing him to overpick.
2785 -- This OUT param will return 1 for error INV_OVERPICK_NOT_ALLOWED
2786 -- 2 for error INV_LACK_MTRL_TO_OVERPICK
2787 -- 3 for error INV_OVERSHIP_TOLERANCE
2788
2789 PROCEDURE check_overpick(
2790 p_transaction_temp_id IN NUMBER
2791 , p_overpicked_qty IN NUMBER
2792 , p_item_id IN NUMBER
2793 , p_rev IN VARCHAR2
2794 , p_lot_num IN VARCHAR2
2795 , p_lot_exp_date IN DATE
2796 , p_sub IN VARCHAR2
2797 , p_locator_id IN NUMBER
2798 , p_lpn_id IN NUMBER
2799 , p_att IN NUMBER
2800 , x_check_overpick_passed OUT NOCOPY VARCHAR
2801 , x_ovpk_error_code OUT NOCOPY NUMBER
2802 , x_return_status OUT NOCOPY VARCHAR2
2803 , x_msg_count OUT NOCOPY NUMBER
2804 , x_msg_data OUT NOCOPY VARCHAR2
2805 ) IS
2806 l_org_id NUMBER;
2807 l_temp NUMBER;
2808 l_op_plan_id NUMBER;
2809 l_is_wms_enabled BOOLEAN := FALSE;
2810 l_ovpk_allowed NUMBER;
2811 l_max_qty_allowed NUMBER;
2812 l_max_qty_allowed2 NUMBER;
2813 l_this_alloc NUMBER;
2814 l_mo_line_id NUMBER;
2815 -- For calling query_quantities
2816 l_msg_count NUMBER;
2817 l_msg_data VARCHAR2(2000);
2818 l_ser_code NUMBER;
2819 l_lot_code NUMBER;
2820 l_rev_code NUMBER;
2821 l_is_rev_controlled BOOLEAN := FALSE;
2822 l_is_lot_controlled BOOLEAN := FALSE;
2823 l_is_ser_controlled BOOLEAN := FALSE;
2824 --l_rev VARCHAR2(3) := p_rev;
2825 --l_lot_num VARCHAR2(30) := p_lot_num;
2826 --l_lot_exp_date DATE := p_lot_exp_date;
2827 --l_sub VARCHAR2(10) := p_sub;
2828 --l_locator_id NUMBER := p_locator_id;
2829 --l_lpn_id NUMBER := p_lpn_id;
2830 x_qoh NUMBER;
2831 x_att NUMBER;
2832 l_rqoh NUMBER;
2833 l_qr NUMBER;
2834 l_qs NUMBER;
2835 l_atr NUMBER;
2836 --For DB log
2837 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2838 BEGIN
2839 x_return_status := fnd_api.g_ret_sts_success;
2840 x_check_overpick_passed := 'N';
2841
2842 --Resolve org_id,transaction_quantity, move_order_line_id
2843 --from the transaction_temp_id
2844 SELECT organization_id
2845 , transaction_quantity
2846 , move_order_line_id
2847 INTO l_org_id
2848 , l_this_alloc
2849 , l_mo_line_id
2850 FROM mtl_material_transactions_temp
2851 WHERE transaction_temp_id = p_transaction_temp_id;
2852
2853
2854 IF (l_debug = 1) THEN
2855 print_debug('OVPK: Entered check_overpick with the following params');
2856 print_debug('OVPK: p_transaction_temp_id = ' || p_transaction_temp_id);
2857 print_debug('OVPK: l_org_id = ' || l_org_id);
2858 print_debug('OVPK: p_item_id = ' || p_item_id);
2859 print_debug('OVPK: p_overpicked_qty = ' || p_overpicked_qty);
2860 print_debug('OVPK: p_rev ' || p_rev);
2861 print_debug('OVPK: p_lot_num ' || p_lot_num);
2862 print_debug('OVPK: p_sub ' || p_sub);
2863 print_debug('OVPK: p_locator_id ' || p_locator_id);
2864 print_debug('OVPK: l_this_alloc = ' || l_this_alloc);
2865 print_debug('OVPK: p_lpn_id ' || p_lpn_id);
2866 print_debug('OVPK: l_mo_line_id = ' || l_mo_line_id);
2867 print_debug('OVPK: p_att = ' || p_att);
2868 END IF;
2869
2870 -- For a WMS Org, this API should never get called for a Bulk Task
2871 IF inv_install.adv_inv_installed(l_org_id) THEN
2872 l_is_wms_enabled := TRUE;
2873
2874 IF (l_debug = 1) THEN
2875 print_debug('OVPK: This org is WMS enabled');
2876 END IF;
2877 END IF;
2878
2879 IF (l_debug = 1) THEN
2880 print_debug('OVPK: Calling get_overpick_qty with the following params');
2881 print_debug('OVPK: p_transaction_temp_id = ' || p_transaction_temp_id);
2882 print_debug('OVPK: p_overpicked_qty = ' || p_overpicked_qty);
2883 END IF;
2884
2885 --Call INV_Replenish_Detail_Pub.Get_Overpick_Qty
2886 --to find out if overpicking is allowed
2887 --and if allowed,to what extent
2888 inv_replenish_detail_pub.get_overpick_qty(
2889 p_transaction_temp_id => p_transaction_temp_id
2890 , p_overpicked_qty => p_overpicked_qty
2891 , x_ovpk_allowed => l_ovpk_allowed
2892 , x_max_qty_allowed => l_max_qty_allowed
2893 , x_max_qty_allowed2 => l_max_qty_allowed2
2894 , x_return_status => x_return_status
2895 , x_msg_count => x_msg_count
2896 , x_msg_data => x_msg_data
2897 );
2898
2899 IF (l_debug = 1) THEN
2900 print_debug('OVPK: get_overpick_qty returned the following');
2901 print_debug('OVPK: x_return_status = ' || x_return_status);
2902 print_debug('OVPK: l_ovpk_allowed = ' || l_ovpk_allowed);
2903 print_debug('OVPK: l_max_qty_allowed = ' || l_max_qty_allowed);
2904 END IF;
2905
2906 IF x_return_status = fnd_api.g_ret_sts_error THEN
2907 RAISE fnd_api.g_exc_error;
2908 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2909 RAISE fnd_api.g_exc_unexpected_error;
2910 END IF;
2911
2912 --Overpicked quantity is passed as one of the params to this procedure
2913 --so no need of any code here to determine overpicked quantity
2914
2915 --Is overpicking allowed i.e. Is x_ovpk_allowed = 1 ?
2916 IF l_ovpk_allowed = 1 THEN
2917 --Find out if the item is lot/serial controlled in tht chosen org
2918 SELECT serial_number_control_code
2919 , lot_control_code
2920 , revision_qty_control_code
2921 INTO l_ser_code
2922 , l_lot_code
2923 , l_rev_code
2924 FROM mtl_system_items
2925 WHERE inventory_item_id = p_item_id
2926 AND organization_id = l_org_id;
2927
2928 IF (l_ser_code <> 1) THEN
2929 l_is_ser_controlled := TRUE;
2930 END IF;
2931
2932 IF (l_lot_code <> 1) THEN
2933 l_is_lot_controlled := TRUE;
2934 END IF;
2935
2936 IF (l_rev_code <> 1) THEN
2937 l_is_rev_controlled := TRUE;
2938 END IF;
2939
2940 /* --In the same Sub/Loc/Lot/LPN find out tempQty
2941 --Call inv_quantity_tree_pub.query_quantities and get att
2942 IF (l_debug = 1) THEN
2943 print_debug('OVPK: Calling query_quantities');
2944 END IF;
2945
2946 inv_quantity_tree_pub.query_quantities(
2947 p_api_version_number => 1.0
2948 , p_init_msg_lst => 'F'
2949 , x_return_status => x_return_status
2950 , x_msg_count => l_msg_count
2951 , x_msg_data => l_msg_data
2952 , p_organization_id => l_org_id
2953 , p_inventory_item_id => p_item_id
2954 , p_tree_mode => 1
2955 , p_is_revision_control => l_is_rev_controlled
2956 , p_is_lot_control => l_is_lot_controlled
2957 , p_is_serial_control => l_is_ser_controlled
2958 , p_demand_source_type_id => NULL
2959 , p_revision => p_rev
2960 , p_lot_number => p_lot_num
2961 , p_lot_expiration_date => p_lot_exp_date
2962 , p_subinventory_code => p_sub
2963 , p_locator_id => p_locator_id
2964 , p_onhand_source => 3
2965 , p_lpn_id => p_lpn_id
2966 , x_qoh => x_qoh
2967 , x_rqoh => l_rqoh
2968 , x_qr => l_qr
2969 , x_qs => l_qs
2970 , x_att => x_att
2971 , x_atr => l_atr
2972 );
2973 */
2974
2975 x_att:= p_att;
2976
2977 IF (l_debug = 1) THEN
2978 print_debug('OVPK: Returned from query_quantities');
2979 print_debug('OVPK: x_return_status = ' || x_return_status);
2980 print_debug('OVPK: x_att = ' || x_att);
2981 END IF;
2982
2983 IF x_return_status = fnd_api.g_ret_sts_error THEN
2984 RAISE fnd_api.g_exc_error;
2985 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2986 RAISE fnd_api.g_exc_unexpected_error;
2987 END IF;
2988
2989 IF (x_att >= p_overpicked_qty) THEN
2990 IF (l_max_qty_allowed <> -1
2991 AND (l_this_alloc + p_overpicked_qty) > l_max_qty_allowed) THEN
2992 -- Show ERROR : pickedQty is not within overship tolerance level
2993 IF (l_debug = 1) THEN
2994 print_debug('OVPK: pickedQty is not within overship tolerance level');
2995 print_debug('OVPK: Erroring out');
2996 END IF;
2997
2998 fnd_message.set_name('INV', 'INV_OVERSHIP_TOLERANCE');
2999 x_ovpk_error_code := 3;
3000 fnd_msg_pub.ADD;
3001 RAISE fnd_api.g_exc_error;
3002 ELSE
3003 --Allow the user to overpick
3004 x_check_overpick_passed := 'Y';
3005
3006 --Bug #4762505
3007 --Commenting out the update statement below as it was causing the
3008 --quantity_detailed in MOL to be wrongly updated with the current MMTT qty
3009 --Updates to MOL quantity_detailed should be done after updating MMTT.
3010 --Update QUANTITY_DETAILED in MTRL
3011 /* UPDATE mtl_txn_request_lines
3012 SET quantity_detailed = l_this_alloc + p_overpicked_qty
3013 WHERE line_id = l_mo_line_id;
3014 */
3015
3016 IF (l_debug = 1) THEN
3017 print_debug('OVPK: x_check_overpick_passed = ' || x_check_overpick_passed);
3018 print_debug('OVPK: For MO line_id = ' || l_mo_line_id);
3019 print_debug('OVPK: Updated quantity_detailed column in MTRL to ' || l_this_alloc || '+' || p_overpicked_qty);
3020 print_debug('OVPK: Returning from check_overpick');
3021 END IF;
3022 RETURN;
3023 END IF;
3024 ELSE
3025 --ERROR : Not enough material to pick in Sub/Loc/Lot/LPN
3026 IF l_debug = 1 THEN
3027 print_debug('OVPK: Not enough material to pick in Sub/Loc/Lot/LPN');
3028 print_debug('OVPK: Erroring out');
3029 END IF;
3030
3031 fnd_message.set_name('INV', 'INV_LACK_MTRL_TO_OVERPICK');
3032 x_ovpk_error_code := 2;
3033 fnd_msg_pub.ADD;
3034 RAISE fnd_api.g_exc_error;
3035 END IF;
3036 ELSE
3037 --ERROR Overpicking is not allowed in this org and for this Move Order type
3038 IF l_debug = 1 THEN
3039 print_debug('OVPK: Overpicking is not allowed in this org and for this Move Order type');
3040 print_debug('OVPK: Erroring out');
3041 END IF;
3042
3043 fnd_message.set_name('INV', 'INV_OVERPICK_NOT_ALLOWED');
3044 x_ovpk_error_code := 1;
3045 fnd_msg_pub.ADD;
3046 RAISE fnd_api.g_exc_error;
3047 END IF;
3048 EXCEPTION
3049 WHEN fnd_api.g_exc_error THEN
3050 x_return_status := fnd_api.g_ret_sts_error;
3051 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3052 WHEN fnd_api.g_exc_unexpected_error THEN
3053 x_return_status := fnd_api.g_ret_sts_unexp_error;
3054 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3055 WHEN OTHERS THEN
3056 x_return_status := fnd_api.g_ret_sts_unexp_error;
3057
3058 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3059 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Check_Overpick');
3060 END IF;
3061 END check_overpick;
3062
3063
3064 -- OVPK
3065 -- Check_Overpick
3066 --
3067 -- This API will take 9 input parameters
3068 -- 1. p_transaction_temp_id
3069 -- 2. p_overpicked_qty
3070 -- 3. p_item_id
3071 -- 4. p_rev
3072 -- 5. p_lot_num
3073 -- 6. p_lot_exp_date
3074 -- 7. p_sub
3075 -- 8. p_locator_id
3076 -- 9. p_lpn_id
3077 -- The procedure check_overpick will be called from the client java file
3078 -- This API check_overpick will in turn call Get_Overpick_Qty to find
3079 -- a) Is overpicking allowed for given Org, MO type and transaction_temp_id ?
3080 -- b) What is the max quantity that can be overpicked ?
3081 -- It will then log the appropriate error message if the user encounters such a state,
3082 -- such as 'Overpicking not allowed' or 'Insufficient stock' or 'Shipping Tolerance exceeded'
3083 -- Otherwise it will update QUANTITY_DETAILED in MTRL (if it is not a bulk picked task)
3084 -- and return control to the calling routine, with x_check_overpick_passed set to 'Y'
3085 -- thereby allowing him to overpick.
3086 -- This OUT param will return 1 for error INV_OVERPICK_NOT_ALLOWED
3087 -- 2 for error INV_LACK_MTRL_TO_OVERPICK
3088 -- 3 for error INV_OVERSHIP_TOLERANCE
3089 -- The procedure check_overpick_lot is overloaded for MUOM Fulfillment Project.
3090
3091 PROCEDURE check_overpick(
3092 p_transaction_temp_id IN NUMBER
3093 , p_overpicked_qty IN NUMBER
3094 , p_item_id IN NUMBER
3095 , p_rev IN VARCHAR2
3096 , p_lot_num IN VARCHAR2
3097 , p_lot_exp_date IN DATE
3098 , p_sub IN VARCHAR2
3099 , p_locator_id IN NUMBER
3100 , p_lpn_id IN NUMBER
3101 , x_check_overpick_passed OUT NOCOPY VARCHAR
3102 , x_ovpk_error_code OUT NOCOPY NUMBER
3103 , x_return_status OUT NOCOPY VARCHAR2
3104 , x_msg_count OUT NOCOPY NUMBER
3105 , x_msg_data OUT NOCOPY VARCHAR2
3106 ) IS
3107 l_overpicked_qty2 NUMBER ;
3108 BEGIN
3109
3110 check_overpick(
3111 p_transaction_temp_id => p_transaction_temp_id
3112 , p_overpicked_qty => p_overpicked_qty
3113 , p_overpicked_qty2 => l_overpicked_qty2
3114 , p_item_id => p_item_id
3115 , p_rev => p_rev
3116 , p_lot_num => p_lot_num
3117 , p_lot_exp_date => p_lot_exp_date
3118 , p_sub => p_sub
3119 , p_locator_id => p_locator_id
3120 , p_lpn_id => p_lpn_id
3121 , x_check_overpick_passed => x_check_overpick_passed
3122 , x_ovpk_error_code => x_ovpk_error_code
3123 , x_return_status => x_return_status
3124 , x_msg_count => x_msg_count
3125 , x_msg_data => x_msg_data
3126 );
3127
3128 END check_overpick;
3129
3130 -- OVPK - APL
3131 -- This API is similar to get_overpick_qty
3132 -- But this API will also do the MTLT level check for overpicking
3133 -- which is not there in get_overpick_qty
3134 -- The additional IN parameter needed here is p_lot_num
3135 PROCEDURE get_overpick_qty_lot(
3136 p_transaction_temp_id IN NUMBER
3137 , p_overpicked_qty IN NUMBER
3138 , p_overpicked_qty2 IN NUMBER
3139 , p_lot_num IN VARCHAR2
3140 , x_ovpk_allowed OUT NOCOPY NUMBER
3141 , x_max_qty_allowed OUT NOCOPY NUMBER
3142 , x_max_qty_allowed2 OUT NOCOPY NUMBER
3143 , x_other_mtlt OUT NOCOPY NUMBER
3144 , x_other_mtlt2 OUT NOCOPY NUMBER
3145 , x_return_status OUT NOCOPY VARCHAR2
3146 , x_msg_count OUT NOCOPY NUMBER
3147 , x_msg_data OUT NOCOPY VARCHAR2
3148 ) IS
3149 l_org_id NUMBER;
3150 l_move_order_type NUMBER;
3151 l_transaction_temp_id NUMBER := p_transaction_temp_id;
3152 l_trx_source_line_id NUMBER;
3153 l_all_alloc NUMBER;
3154 l_all_alloc2 NUMBER; -- MUOM Fulfillment Project
3155 l_other_alloc NUMBER;
3156 l_other_alloc2 NUMBER; -- MUOM Fulfillment Project
3157 l_lot_num VARCHAR2(30) := p_lot_num;
3158 l_all_mtlt NUMBER;
3159 l_all_mtlt2 NUMBER; -- MUOM Fulfillment Project
3160 l_this_mtlt NUMBER;
3161 l_this_mtlt2 NUMBER; -- MUOM Fulfillment Project
3162 l_other_mtlt NUMBER;
3163 l_other_mtlt2 NUMBER; -- MUOM Fulfillment Project
3164 l_this_alloc NUMBER;
3165 l_this_alloc2 NUMBER; -- MUOM Fulfillment Project
3166 l_temp VARCHAR2(1);
3167 l_new_qty NUMBER;
3168 l_new_qty2 NUMBER;
3169 --Check_Shipping_Tolerances
3170 l_msg_count NUMBER;
3171 l_msg_data VARCHAR2(2000);
3172 l_mo_line_id NUMBER;
3173 --l_ordered_quantity NUMBER := -1;
3174
3175 --Shipping
3176 l_allowed_flag VARCHAR2(20);
3177 l_max_quantity_allowed NUMBER;
3178 l_max_quantity_allowed2 NUMBER;
3179 l_avail_req_quantity NUMBER;
3180 l_return_status VARCHAR2(1);
3181 --For DB log
3182 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3183 BEGIN
3184 x_return_status := fnd_api.g_ret_sts_success;
3185
3186 --Resolve org_id and move_order_type from the passed transaction_temp_id
3187 SELECT mmtt.organization_id
3188 , mtrh.move_order_type
3189 INTO l_org_id
3190 , l_move_order_type
3191 FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
3192 WHERE mmtt.move_order_line_id = mtrl.line_id
3193 AND mtrl.header_id = mtrh.header_id
3194 AND mmtt.transaction_temp_id = p_transaction_temp_id;
3195
3196 IF (l_debug = 1) THEN
3197 print_debug('OVPK: Entered get_overpick_qty with the following params');
3198 --print_debug('OVPK: p_org_id = ' || p_org_id);
3199 --print_debug('OVPK: p_move_order_type = ' || p_move_order_type);
3200 print_debug('OVPK: p_transaction_temp_id = ' || p_transaction_temp_id);
3201 print_debug('OVPK: p_overpicked_qty = ' || p_overpicked_qty);
3202 print_debug('OVPK: p_lot_num = ' || p_lot_num);
3203 print_debug('OVPK: Org_id = ' || l_org_id);
3204 print_debug('OVPK: Move Order Type = ' || l_move_order_type);
3205 END IF;
3206
3207 -- For a Replenishment (MO Type 2) / Requisition (MO Type 1)
3208 -- lookup the flag OVPK_TRANSFER_ORDERS_ENABLED in mtl_parameters
3209
3210 IF (l_move_order_type IN
3211 (inv_globals.g_move_order_replenishment, inv_globals.g_move_order_requisition)
3212 ) THEN
3213 SELECT OVPK_TRANSFER_ORDERS_ENABLED
3214 INTO l_temp
3215 FROM mtl_parameters
3216 WHERE organization_id = l_org_id;
3217
3218 IF (NVL(l_temp, 'Y') = 'Y') THEN
3219 x_ovpk_allowed := 1; --Overpicking is allowed
3220 ELSE
3221 x_ovpk_allowed := 0; --Overpicking is not allowed
3222 END IF;
3223
3224 x_max_qty_allowed := -1;
3225
3226 IF (l_debug = 1) THEN
3227 print_debug('OVPK: In MTL_PARAMETERS OVPK_TRANSFER_ORDERS_ENABLED = ' || l_temp);
3228 print_debug('OVPK: x_ovpk_allowed = ' || x_ovpk_allowed);
3229 print_debug('OVPK: x_max_qty_allowed = ' || x_max_qty_allowed);
3230 print_debug('OVPK: Returning from get_overpick_qty');
3231 END IF;
3232
3233 RETURN;
3234 END IF;
3235
3236 -- For a Manufacturing Component Pick(WIP) MO
3237 -- lookup the flag WIP_OVERPICK_ENABLED in mtl_parameters
3238 -- MO Type 5
3239 IF (l_move_order_type = inv_globals.g_move_order_mfg_pick) THEN
3240 SELECT wip_overpick_enabled
3241 INTO l_temp
3242 FROM mtl_parameters
3243 WHERE organization_id = l_org_id;
3244
3245 IF (NVL(l_temp, 'N') = 'Y') THEN
3246 x_ovpk_allowed := 1; --Overpicking is allowed
3247 ELSE
3248 x_ovpk_allowed := 0; --Overpicking is not allowed
3249 END IF;
3250
3251 x_max_qty_allowed := -1;
3252
3253 IF (l_debug = 1) THEN
3254 print_debug('OVPK: In MTL_PARAMETERS wip_overpick_enabled = ' || l_temp);
3255 print_debug('OVPK: x_ovpk_allowed = ' || x_ovpk_allowed);
3256 print_debug('OVPK: x_max_qty_allowed = ' || x_max_qty_allowed);
3257 print_debug('OVPK: Returning from get_overpick_qty');
3258 END IF;
3259
3260 RETURN;
3261 END IF;
3262
3263 -- For a Pick Wave MO lookup the profile WSH_OVERPICKING_ENABLED
3264 -- MO Type 3
3265
3266 IF (l_debug = 1) THEN
3267 print_debug('OVPK: fnd_profile WSH_OVERPICK_ENABLED = ' || fnd_profile.VALUE('WSH_OVERPICK_ENABLED'));
3268 END IF;
3269
3270 IF (l_move_order_type = inv_globals.g_move_order_pick_wave
3271 AND NVL(fnd_profile.VALUE('WSH_OVERPICK_ENABLED'), 'N') = 'Y') THEN
3272 -- OVPK is allowed
3273 x_ovpk_allowed := 1;
3274
3275 -- Query the TRX_SOURCE_LINE_ID from MMTT for the given transaction_temp_id
3276 -- Get the transaction_quantity for the given transaction_temp_id
3277 SELECT trx_source_line_id
3278 , transaction_quantity
3279 , move_order_line_id
3280 , secondary_transaction_quantity -- MUOM Fulfillment Project
3281 INTO l_trx_source_line_id
3282 , l_this_alloc
3283 , l_mo_line_id
3284 , l_this_alloc2
3285 FROM mtl_material_transactions_temp
3286 WHERE transaction_temp_id = l_transaction_temp_id;
3287
3288 IF (l_debug = 1) THEN
3289 print_debug('OVPK: l_trx_source_line_id = ' || l_trx_source_line_id);
3290 print_debug('OVPK: l_this_alloc = ' || l_this_alloc);
3291 print_debug('OVPK: l_mo_line_id = ' || l_mo_line_id);
3292 print_debug('OVPK: l_this_alloc2 = ' || l_this_alloc2);
3293 END IF;
3294
3295 -- Get the sum of all allocations for that trx_source_line_id
3296 SELECT SUM(transaction_quantity), SUM(SECONDARY_TRANSACTION_QUANTITY)
3297 INTO l_all_alloc, l_all_alloc2
3298 FROM mtl_material_transactions_temp
3299 WHERE trx_source_line_id = l_trx_source_line_id;
3300
3301 -- The difference will be l_other_alloc
3302 l_other_alloc := l_all_alloc - l_this_alloc;
3303 l_other_alloc2 := l_all_alloc2 - l_this_alloc2; -- MUOM Fulfillment Project
3304
3305 -- l_this_alloc is the suggested quantity
3306 -- To this, add p_overpicked_qty to get l_new_qty
3307 l_new_qty := l_this_alloc + p_overpicked_qty;
3308 l_new_qty2 := l_this_alloc2 + p_overpicked_qty2; -- MUOM Fulfillment Project
3309
3310 IF (l_debug = 1) THEN
3311 print_debug('OVPK: l_all_alloc = ' || l_all_alloc);
3312 print_debug('OVPK: l_all_alloc2 = ' || l_all_alloc2);
3313 print_debug('OVPK: l_other_alloc = l_all_alloc - l_this_alloc = ' || l_other_alloc);
3314 print_debug('OVPK: l_other_alloc2 = l_all_alloc2 - l_this_alloc2 = ' || l_other_alloc2);
3315 print_debug('OVPK: Calling inv_replenish_detail_pub.check_shipping_tolerances');
3316 print_debug('OVPK: With the following params');
3317 print_debug('OVPK: l_mo_line_id = ' || l_mo_line_id);
3318 print_debug('OVPK: p_new_qty = ' || l_new_qty);
3319 print_debug('OVPK: p_new_qty2 = ' || l_new_qty2);
3320 END IF;
3321
3322 -- Call the shipping API to get l_max_quantity_allowed for this particular SO Line
3323 IF l_trx_source_line_id IS NOT NULL THEN
3324 inv_replenish_detail_pub.check_shipping_tolerances(
3325 x_return_status => l_return_status
3326 , x_msg_count => l_msg_count
3327 , x_msg_data => l_msg_data
3328 , x_allowed => l_allowed_flag
3329 , x_max_quantity => l_max_quantity_allowed
3330 , x_max_quantity2 => l_max_quantity_allowed2
3331 , p_line_id => l_mo_line_id
3332 , p_quantity => l_new_qty
3333 , p_quantity2 => l_new_qty2
3334 );
3335
3336 IF (l_debug = 1) THEN
3337 print_debug('OVPK: Returned from check_shipping_tolerances');
3338 print_debug('OVPK: x_return_status = ' || l_return_status);
3339 print_debug('OVPK: x_max_quantity = ' || l_max_quantity_allowed);
3340 print_debug('OVPK: x_max_quantity2 = ' || l_max_quantity_allowed2);
3341 END IF;
3342
3343 IF l_return_status = fnd_api.g_ret_sts_error THEN
3344 RAISE fnd_api.g_exc_error;
3345 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3346 RAISE fnd_api.g_exc_unexpected_error;
3347 END IF;
3348 END IF;
3349
3350 /*
3351 --If the new qty is greater than l_max_quantity_allowed then overpicking is not allowed
3352 IF (l_new_qty > l_max_quantity_allowed) THEN
3353 x_ovpk_allowed := 0;
3354 ELSE
3355 x_ovpk_allowed := 1;
3356 END IF;
3357 */
3358
3359 -- Calculate the max pick qty allowed for that task
3360 -- ie
3361 x_max_qty_allowed := l_max_quantity_allowed - l_other_alloc;
3362 x_max_qty_allowed2 := l_max_quantity_allowed2 - l_other_alloc2; -- MUOM Fulfillment Project
3363
3364 -- For lot item deduct the other MTLT's
3365 IF (l_lot_num IS NOT NULL) THEN
3366 IF l_debug = 1 THEN
3367 print_debug('OVPK: l_lot_num = ' || l_lot_num);
3368 END IF;
3369 -- Get sum of txn qty of all mtlt's for this mmtt
3370 SELECT nvl(SUM(transaction_quantity),0), nvl(SUM(SECONDARY_QUANTITY),0)
3371 INTO l_all_mtlt, l_all_mtlt2
3372 FROM mtl_transaction_lots_temp
3373 WHERE transaction_temp_id = l_transaction_temp_id;
3374
3375 --/* Bug 9448490 Lot Substitution Project */ start
3376 BEGIN
3377 -- Get this mtlt
3378 SELECT nvl(SUM(transaction_quantity),0), nvl(SUM(SECONDARY_QUANTITY),0)
3379 INTO l_this_mtlt, l_this_mtlt2
3380 FROM mtl_transaction_lots_temp
3381 WHERE transaction_temp_id = l_transaction_temp_id
3382 AND lot_number = l_lot_num;
3383 EXCEPTION
3384 WHEN OTHERS THEN
3385 null;
3386 END;
3387
3388 --/* Bug 9448490 Lot Substitution Project */ end
3389
3390
3391 l_other_mtlt := l_all_mtlt - l_this_mtlt;
3392 l_other_mtlt2 := l_all_mtlt2 - l_this_mtlt2; -- MUOM Fulfillment Project
3393
3394 IF l_debug = 1 THEN
3395 print_debug('OVPK: l_all_mtlt = ' || l_all_mtlt);
3396 print_debug('OVPK: l_this_mtlt = ' || l_this_mtlt);
3397 print_debug('OVPK: l_other_mtlt = ' || l_other_mtlt);
3398 print_debug('OVPK: l_all_mtlt2 = ' || l_all_mtlt2);
3399 print_debug('OVPK: l_this_mtlt2 = ' || l_this_mtlt2);
3400 print_debug('OVPK: l_other_mtlt2 = ' || l_other_mtlt2);
3401 END IF;
3402
3403 x_max_qty_allowed := x_max_qty_allowed - l_other_mtlt;
3404 x_max_qty_allowed2 := x_max_qty_allowed2 - l_other_mtlt2; -- MUOM Fulfillment Project
3405 x_other_mtlt := l_other_mtlt;
3406 x_other_mtlt2 := l_other_mtlt2; -- MUOM Fulfillment Project
3407 END IF;
3408
3409 IF (l_debug = 1) THEN
3410 print_debug('OVPK: x_ovpk_allowed = ' || x_ovpk_allowed);
3411 print_debug('OVPK: The max quantity that can be allowed for this MMTT record is');
3412 print_debug('OVPK: Qty allowed by shipping API - other allocations - other MTLT''s = ' || x_max_qty_allowed);
3413 print_debug('OVPK: Qty2 allowed by shipping API - other allocations - other MTLT''s = ' || x_max_qty_allowed2);
3414 END IF;
3415
3416 RETURN;
3417 ELSE
3418 x_ovpk_allowed := 0;
3419 x_max_qty_allowed := -1;
3420
3421 IF (l_debug = 1) THEN
3422 print_debug('OVPK: x_ovpk_allowed = ' || x_ovpk_allowed);
3423 print_debug('OVPK: The max quantity that can be allowed for this MMTT record is');
3424 print_debug('OVPK: Qty allowed by shipping API - other allocations = ' || x_max_qty_allowed);
3425 END IF;
3426
3427 RETURN;
3428 END IF;
3429 EXCEPTION
3430 WHEN fnd_api.g_exc_error THEN
3431 x_return_status := fnd_api.g_ret_sts_error;
3432 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3433 WHEN fnd_api.g_exc_unexpected_error THEN
3434 x_return_status := fnd_api.g_ret_sts_unexp_error;
3435 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3436 WHEN OTHERS THEN
3437 x_return_status := fnd_api.g_ret_sts_unexp_error;
3438
3439 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3440 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Get_Overpick_Qty');
3441 END IF;
3442 END get_overpick_qty_lot;
3443
3444 -- MUOM Fulfillment Project overloaded the procedure
3445
3446 PROCEDURE get_overpick_qty_lot(
3447 p_transaction_temp_id IN NUMBER
3448 , p_overpicked_qty IN NUMBER
3449 , p_lot_num IN VARCHAR2
3450 , x_ovpk_allowed OUT NOCOPY NUMBER
3451 , x_max_qty_allowed OUT NOCOPY NUMBER
3452 , x_other_mtlt OUT NOCOPY NUMBER
3453 , x_return_status OUT NOCOPY VARCHAR2
3454 , x_msg_count OUT NOCOPY NUMBER
3455 , x_msg_data OUT NOCOPY VARCHAR2
3456 ) IS
3457
3458 l_overpicked_qty2 NUMBER;
3459 l_max_qty_allowed2 NUMBER;
3460 l_other_mtlt2 NUMBER;
3461
3462 BEGIN
3463 get_overpick_qty_lot(
3464 p_transaction_temp_id => p_transaction_temp_id
3465 , p_overpicked_qty => p_overpicked_qty
3466 , p_overpicked_qty2 => l_overpicked_qty2
3467 , p_lot_num => p_lot_num
3468 , x_ovpk_allowed => x_ovpk_allowed
3469 , x_max_qty_allowed => x_max_qty_allowed
3470 , x_max_qty_allowed2 => l_max_qty_allowed2
3471 , x_other_mtlt => x_other_mtlt
3472 , x_other_mtlt2 => l_other_mtlt2
3473 , x_return_status => x_return_status
3474 , x_msg_count => x_msg_count
3475 , x_msg_data => x_msg_data
3476 ) ;
3477
3478 END get_overpick_qty_lot;
3479
3480
3481
3482 PROCEDURE check_overpick_lot(
3483 p_transaction_temp_id IN NUMBER
3484 , p_overpicked_qty IN NUMBER
3485 , p_overpicked_qty2 IN NUMBER
3486 , p_item_id IN NUMBER
3487 , p_rev IN VARCHAR2
3488 , p_lot_num IN VARCHAR2
3489 , p_lot_exp_date IN DATE
3490 , p_sub IN VARCHAR2
3491 , p_locator_id IN NUMBER
3492 , p_lpn_id IN NUMBER
3493 , x_check_overpick_passed OUT NOCOPY VARCHAR
3494 , x_ovpk_error_code OUT NOCOPY NUMBER
3495 , x_return_status OUT NOCOPY VARCHAR2
3496 , x_msg_count OUT NOCOPY NUMBER
3497 , x_msg_data OUT NOCOPY VARCHAR2
3498 ) IS
3499 l_org_id NUMBER;
3500 l_temp NUMBER;
3501 l_op_plan_id NUMBER;
3502 l_is_wms_enabled BOOLEAN := FALSE;
3503 l_ovpk_allowed NUMBER;
3504 l_max_qty_allowed NUMBER;
3505 l_max_qty_allowed2 NUMBER; -- MUOM Fulfillment Project
3506 l_this_alloc NUMBER;
3507 l_this_alloc2 NUMBER; -- MUOM Fulfillment Project
3508 l_mo_line_id NUMBER;
3509 l_other_mtlt NUMBER := 0;
3510 l_other_mtlt2 NUMBER := 0; -- MUOM Fulfillment Project
3511 -- For calling query_quantities
3512 l_msg_count NUMBER;
3513 l_msg_data VARCHAR2(2000);
3514 l_ser_code NUMBER;
3515 l_lot_code NUMBER;
3516 l_rev_code NUMBER;
3517 l_is_rev_controlled BOOLEAN := FALSE;
3518 l_is_lot_controlled BOOLEAN := FALSE;
3519 l_is_ser_controlled BOOLEAN := FALSE;
3520 x_qoh NUMBER;
3521 x_att NUMBER;
3522 l_rqoh NUMBER;
3523 l_qr NUMBER;
3524 l_qs NUMBER;
3525 l_atr NUMBER;
3526 --For DB log
3527 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3528 --MUOM Fulfillemnt Project
3529 l_fulfill_base mtl_material_transactions_temp.fulfillment_base%TYPE;
3530
3531 BEGIN
3532 x_return_status := fnd_api.g_ret_sts_success;
3533 x_check_overpick_passed := 'N';
3534
3535 --Resolve org_id,transaction_quantity, move_order_line_id
3536 --from the transaction_temp_id
3537 SELECT organization_id
3538 , transaction_quantity
3539 , move_order_line_id
3540 , secondary_transaction_quantity -- MUOM Fulfillment Project
3541 , nvl(fulfillment_base, 'P')
3542 INTO l_org_id
3543 , l_this_alloc
3544 , l_mo_line_id
3545 , l_this_alloc2
3546 , l_fulfill_base
3547 FROM mtl_material_transactions_temp
3548 WHERE transaction_temp_id = p_transaction_temp_id;
3549
3550 IF (l_debug = 1) THEN
3551 print_debug('OVPK: Entered check_overpick with the following params');
3552 --print_debug('OVPK: p_move_order_type = ' || p_move_order_type);
3553 print_debug('OVPK: p_transaction_temp_id = ' || p_transaction_temp_id);
3554 print_debug('OVPK: l_org_id = ' || l_org_id);
3555 print_debug('OVPK: p_item_id = ' || p_item_id);
3556 print_debug('OVPK: p_overpicked_qty = ' || p_overpicked_qty);
3557 print_debug('OVPK: l_this_alloc = ' || l_this_alloc);
3558 print_debug('OVPK: l_this_alloc2 = ' || l_this_alloc2);
3559 print_debug('OVPK: l_mo_line_id = ' || l_mo_line_id);
3560 print_debug('OVPK: Fulfill Base '||l_fulfill_base);
3561 END IF;
3562
3563 -- For a WMS Org, find if the task is a bulk pick task
3564 IF inv_install.adv_inv_installed(l_org_id) THEN
3565 l_is_wms_enabled := TRUE;
3566
3567 IF (l_debug = 1) THEN
3568 print_debug('OVPK: This org is WMS enabled');
3569 END IF;
3570 END IF;
3571
3572 IF (l_debug = 1) THEN
3573 print_debug('OVPK: Calling get_overpick_qty with the following params');
3574 --print_debug('OVPK: p_org_id = ' || p_org_id);
3575 --print_debug('OVPK: p_move_order_type = ' || p_move_order_type);
3576 print_debug('OVPK: p_transaction_temp_id = ' || p_transaction_temp_id);
3577 print_debug('OVPK: p_overpicked_qty = ' || p_overpicked_qty);
3578 print_debug('OVPK: p_lot_num = ' || p_lot_num);
3579 END IF;
3580
3581 --Call INV_Replenish_Detail_Pub.Get_Overpick_Qty_Lot
3582 --to find out if overpicking is allowed
3583 --and if allowed,to what extent
3584 inv_replenish_detail_pub.get_overpick_qty_lot(
3585 p_transaction_temp_id => p_transaction_temp_id
3586 , p_overpicked_qty => p_overpicked_qty
3587 , p_overpicked_qty2 => p_overpicked_qty2
3588 , p_lot_num => p_lot_num
3589 , x_ovpk_allowed => l_ovpk_allowed
3590 , x_max_qty_allowed => l_max_qty_allowed
3591 , x_max_qty_allowed2 => l_max_qty_allowed2
3592 , x_other_mtlt => l_other_mtlt
3593 , x_other_mtlt2 => l_other_mtlt2
3594 , x_return_status => x_return_status
3595 , x_msg_count => x_msg_count
3596 , x_msg_data => x_msg_data
3597 );
3598
3599 IF (l_debug = 1) THEN
3600 print_debug('OVPK: get_overpick_qty returned the following');
3601 print_debug('OVPK: x_return_status = ' || x_return_status);
3602 print_debug('OVPK: l_ovpk_allowed = ' || l_ovpk_allowed);
3603 print_debug('OVPK: l_max_qty_allowed = ' || l_max_qty_allowed);
3604 print_debug('OVPK: x_other_mtlt = ' || l_other_mtlt);
3605 print_debug('OVPK: l_max_qty_allowed2 = ' || l_max_qty_allowed2);
3606 print_debug('OVPK: x_other_mtlt2 = ' || l_other_mtlt2);
3607 END IF;
3608
3609 IF x_return_status = fnd_api.g_ret_sts_error THEN
3610 RAISE fnd_api.g_exc_error;
3611 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3612 RAISE fnd_api.g_exc_unexpected_error;
3613 END IF;
3614
3615 --Overpicked quantity is passed as one of the params to this procedure
3616 --so no need of any code here to determine overpicked quantity
3617
3618 --Is overpicking allowed i.e. Is x_ovpk_allowed = 1 ?
3619 IF l_ovpk_allowed = 1 THEN
3620 --Find out if the item is lot/serial controlled in tht chosen org
3621 /* SELECT serial_number_control_code
3622 , lot_control_code
3623 , revision_qty_control_code
3624 INTO l_ser_code
3625 , l_lot_code
3626 , l_rev_code
3627 FROM mtl_system_items
3628 WHERE inventory_item_id = p_item_id
3629 AND organization_id = l_org_id;
3630
3631 IF (l_ser_code <> 1) THEN
3632 l_is_ser_controlled := TRUE;
3633 END IF;
3634
3635 IF (l_lot_code <> 1) THEN
3636 l_is_lot_controlled := TRUE;
3637 END IF;
3638
3639 IF (l_rev_code <> 1) THEN
3640 l_is_rev_controlled := TRUE;
3641 END IF;
3642
3643 --In the same Sub/Loc/Lot/LPN find out tempQty
3644 --Call inv_quantity_tree_pub.query_quantities and get att
3645 IF (l_debug = 1) THEN
3646 print_debug('OVPK: Calling query_quantities');
3647 END IF;
3648
3649 inv_quantity_tree_pub.query_quantities(
3650 p_api_version_number => 1.0
3651 , p_init_msg_lst => 'F'
3652 , x_return_status => x_return_status
3653 , x_msg_count => l_msg_count
3654 , x_msg_data => l_msg_data
3655 , p_organization_id => l_org_id
3656 , p_inventory_item_id => p_item_id
3657 , p_tree_mode => 1
3658 , p_is_revision_control => l_is_rev_controlled
3659 , p_is_lot_control => l_is_lot_controlled
3660 , p_is_serial_control => l_is_ser_controlled
3661 , p_demand_source_type_id => NULL
3662 , p_revision => p_rev
3663 , p_lot_number => p_lot_num
3664 , p_lot_expiration_date => p_lot_exp_date
3665 , p_subinventory_code => p_sub
3666 , p_locator_id => p_locator_id
3667 , p_onhand_source => 3
3668 , p_lpn_id => p_lpn_id
3669 , x_qoh => x_qoh
3670 , x_rqoh => l_rqoh
3671 , x_qr => l_qr
3672 , x_qs => l_qs
3673 , x_att => x_att
3674 , x_atr => l_atr
3675 );
3676
3677 IF (l_debug = 1) THEN
3678 print_debug('OVPK: Returned from query_quantities');
3679 print_debug('OVPK: x_return_status = ' || x_return_status);
3680 print_debug('OVPK: x_att = ' || x_att);
3681 END IF;
3682
3683 IF x_return_status = fnd_api.g_ret_sts_error THEN
3684 RAISE fnd_api.g_exc_error;
3685 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3686 RAISE fnd_api.g_exc_unexpected_error;
3687 END IF;*/
3688
3689 --comment out the if condition and else part since system do att check already
3690 --IF (x_att >= p_overpicked_qty) THEN
3691 -- MUOM Fulfillment Project modified to add l_fulfill_base
3692 IF (l_max_qty_allowed <> -1
3693 AND (l_this_alloc + p_overpicked_qty - l_other_mtlt) > l_max_qty_allowed) AND l_fulfill_base='P' THEN
3694 -- Show ERROR : pickedQty is not within overship tolerance level
3695 IF (l_debug = 1) THEN
3696 print_debug('OVPK: pickedQty is not within overship tolerance level');
3697 print_debug('OVPK: Erroring out');
3698 END IF;
3699
3700 fnd_message.set_name('INV', 'INV_OVERSHIP_TOLERANCE');
3701 x_ovpk_error_code := 3;
3702 fnd_msg_pub.ADD;
3703 RAISE fnd_api.g_exc_error;
3704 ELSIF (l_max_qty_allowed2 <> -1
3705 AND (l_this_alloc2 + p_overpicked_qty2 - l_other_mtlt2) > l_max_qty_allowed2) AND l_fulfill_base='S' THEN
3706 -- Show ERROR : pickedQty is not within overship tolerance level
3707 IF (l_debug = 1) THEN
3708 print_debug('OVPK: pickedQty2 is not within overship tolerance level');
3709 print_debug('OVPK: Erroring out');
3710 END IF;
3711
3712 fnd_message.set_name('INV', 'INV_OVERSHIP_TOLERANCE');
3713 x_ovpk_error_code := 3;
3714 fnd_msg_pub.ADD;
3715 RAISE fnd_api.g_exc_error;
3716 ELSE
3717 --Allow the user to overpick
3718 x_check_overpick_passed := 'Y';
3719
3720 --Bug #4762505
3721 --Commenting out the update statement below as it was causing the
3722 --quantity_detailed in MOL to be wrongly updated with the current MMTT qty
3723 --Updates to MOL quantity_detailed should be done after updating MMTT.
3724 --Update QUANTITY_DETAILED in MTRL
3725 /* UPDATE mtl_txn_request_lines
3726 SET quantity_detailed = l_this_alloc + p_overpicked_qty
3727 WHERE line_id = l_mo_line_id;
3728 */
3729
3730 IF (l_debug = 1) THEN
3731 print_debug('OVPK: x_check_overpick_passed = ' || x_check_overpick_passed);
3732 print_debug('OVPK: For MO line_id = ' || l_mo_line_id);
3733 print_debug('OVPK: Updated quantity_detailed column in MTRL to ' || l_this_alloc || '+' || p_overpicked_qty);
3734 print_debug('OVPK: Updated quantity_detailed2 column in MTRL to ' || l_this_alloc2 || '+' || p_overpicked_qty2);
3735 print_debug('OVPK: Returning from check_overpick');
3736 END IF;
3737 RETURN;
3738 END IF;
3739 /*ELSE
3740 --ERROR : Not enough material to pick in Sub/Loc/Lot/LPN
3741 IF l_debug = 1 THEN
3742 print_debug('OVPK: Not enough material to pick in Sub/Loc/Lot/LPN');
3743 print_debug('OVPK: Erroring out');
3744 END IF;
3745
3746 fnd_message.set_name('INV', 'INV_LACK_MTRL_TO_OVERPICK');
3747 x_ovpk_error_code := 2;
3748 fnd_msg_pub.ADD;
3749 RAISE fnd_api.g_exc_error;
3750 END IF;*/
3751 ELSE
3752 --ERROR Overpicking is not allowed in this org and for this Move Order type
3753 IF l_debug = 1 THEN
3754 print_debug('OVPK: Overpicking is not allowed in this org and for this Move Order type');
3755 print_debug('OVPK: Erroring out');
3756 END IF;
3757
3758 fnd_message.set_name('INV', 'INV_OVERPICK_NOT_ALLOWED');
3759 x_ovpk_error_code := 1;
3760 fnd_msg_pub.ADD;
3761 RAISE fnd_api.g_exc_error;
3762 END IF;
3763 EXCEPTION
3764 WHEN fnd_api.g_exc_error THEN
3765 x_return_status := fnd_api.g_ret_sts_error;
3766 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3767 WHEN fnd_api.g_exc_unexpected_error THEN
3768 x_return_status := fnd_api.g_ret_sts_unexp_error;
3769 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3770 WHEN OTHERS THEN
3771 x_return_status := fnd_api.g_ret_sts_unexp_error;
3772
3773 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3774 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Check_Overpick');
3775 END IF;
3776 END check_overpick_lot;
3777
3778 -- The procedure check_overpick_lot is overloaded for MUOM Fulfillment Project.
3779
3780 PROCEDURE check_overpick_lot(
3781 p_transaction_temp_id IN NUMBER
3782 , p_overpicked_qty IN NUMBER
3783 , p_item_id IN NUMBER
3784 , p_rev IN VARCHAR2
3785 , p_lot_num IN VARCHAR2
3786 , p_lot_exp_date IN DATE
3787 , p_sub IN VARCHAR2
3788 , p_locator_id IN NUMBER
3789 , p_lpn_id IN NUMBER
3790 , x_check_overpick_passed OUT NOCOPY VARCHAR
3791 , x_ovpk_error_code OUT NOCOPY NUMBER
3792 , x_return_status OUT NOCOPY VARCHAR2
3793 , x_msg_count OUT NOCOPY NUMBER
3794 , x_msg_data OUT NOCOPY VARCHAR2
3795 ) IS
3796 l_overpicked_qty2 NUMBER;
3797 BEGIN
3798
3799 check_overpick_lot(
3800 p_transaction_temp_id => p_transaction_temp_id
3801 , p_overpicked_qty => p_overpicked_qty
3802 , p_overpicked_qty2 => l_overpicked_qty2
3803 , p_item_id => p_item_id
3804 , p_rev => p_rev
3805 , p_lot_num => p_lot_num
3806 , p_lot_exp_date => p_lot_exp_date
3807 , p_sub => p_sub
3808 , p_locator_id => p_locator_id
3809 , p_lpn_id => p_lpn_id
3810 , x_check_overpick_passed => x_check_overpick_passed
3811 , x_ovpk_error_code => x_ovpk_error_code
3812 , x_return_status => x_return_status
3813 , x_msg_count => x_msg_count
3814 , x_msg_data => x_msg_data
3815 );
3816 END check_overpick_lot;
3817
3818 ---------------------------------------------------
3819 END inv_replenish_detail_pub;