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