1 PACKAGE BODY wms_device_confirmation_pub AS
2 /* $Header: WMSDEVCB.pls 120.8.12010000.3 2008/10/08 10:27:19 pbonthu ship $ */
3
4 -- TASK status IN WDT
5 l_g_task_loaded CONSTANT NUMBER := 4;
6 l_g_task_active CONSTANT NUMBER := 9;
7
8
9 TYPE lpn_lot_quantity_rec IS RECORD
10 ( lpn_id NUMBER,
11 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
12 lot_number VARCHAR2(80),
13 qty NUMBER
14 );
15
16 TYPE lpn_lot_quantity_tbl IS TABLE OF lpn_lot_quantity_rec INDEX BY BINARY_INTEGER;
17 -- PL/SQL TABLE used to store lot_number and qty for passed in lpn_id
18 t_lpn_lot_qty_table lpn_lot_quantity_tbl;
19
20
21 -----------------------------------------------------
22 -- trace
23 -----------------------------------------------------
24 PROCEDURE trace(p_msg IN VARCHAR2) IS
25 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
26 BEGIN
27 inv_trx_util_pub.trace(p_msg, 'WMS_DEVICE_CONFIRMATION_PUB', 9);
28 END trace;
29
30
31 --this api does not get called when p_transaction_quantity ==0
32 PROCEDURE validate_child_record(p_relation_id IN NUMBER,
33 p_error_on_lot_serial_null IN VARCHAR2,
34 p_lot_code IN NUMBER,
35 p_serial_code IN NUMBER,
36 p_txn_temp_id IN NUMBER,
37 P_qty_disc_flag IN NUMBER,
38 p_transaction_quantity IN NUMBER,--of main record
39 --total qty, might be null in case
40 --user picked all suggested and did
41 --NOT pass quantity
42 x_return_status OUT NOCOPY VARCHAR2) IS
43
44 l_lot_code NUMBER:= p_lot_code;
45 l_serial_code NUMBER := p_serial_code;
46 l_lot_cnt NUMBER :=0 ;
47 l_serial_cnt NUMBER;
48 l_orig_lot_qty NUMBER;--per record in MTLT
49 l_count_child_rec NUMBER := 0;
50 l_total_lot_qty NUMBER :=0;--for all record in MTLT corresponding to MMTT
51
52 CURSOR child_rec_cursor IS
53 SELECT lot_number,lot_qty,serial_number
54 FROM wms_device_requests
55 WHERE relation_id = p_relation_id
56 AND task_id = p_txn_temp_id
57 AND business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
58 AND task_summary = 'N';
59
60 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
61
62 BEGIN
63 IF (l_debug = 1) THEN
64 trace('Inside validate_child_record');
65 END IF;
66
67 SELECT COUNT(*),SUM(lot_qty) INTO l_count_child_rec,l_total_lot_qty
68 FROM wms_device_requests
69 WHERE relation_id = p_relation_id
70 AND task_id = p_txn_temp_id
71 AND business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
72 AND task_summary = 'N';
73
74 IF (l_debug = 1) THEN
75 trace('l_count_temp::'||l_count_child_rec||' total_lot_qty::'||l_total_lot_qty);
76 END IF;
77
78 --in case of qty discrepancy for lot/serial item , child records must exist
79 IF l_count_child_rec = 0 AND p_qty_disc_flag <> 0 THEN
80 IF (l_debug = 1) THEN
81 trace('Error: qty discrepancy and no child rec found');
82 END IF;
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85
86 --make sure that the sum of lot_qty is equal to the transaction_quantiy
87 IF l_count_child_rec <> 0 AND p_transaction_quantity IS NOT NULL
88 AND p_transaction_quantity <> l_total_lot_qty THEN
89 IF (l_debug = 1) THEN
90 trace('Error: Total lot_qty is greater than transaction_quantity');
91 END IF;
92 RAISE FND_API.G_EXC_ERROR;
93
94 END IF;
95
96 FOR l_child_rec IN child_rec_cursor LOOP--child records
97
98 IF (l_lot_code >1) THEN--means LOT CONTROLLED
99
100 IF l_child_rec.lot_number IS NOT NULL THEN
101 SELECT 1 INTO l_lot_cnt FROM dual WHERE exists
102 (SELECT lot_number FROM mtl_transaction_lots_temp
103 WHERE transaction_temp_id = p_txn_temp_id
104 AND lot_number = l_child_rec.lot_number );
105
106 IF l_lot_cnt = 1 then
107 SELECT TRANSACTION_QUANTITY INTO l_orig_lot_qty
108 FROM mtl_transaction_lots_temp
109 WHERE transaction_temp_id = p_txn_temp_id
110 AND lot_number = l_child_rec.lot_number;
111 END IF;
112 --make sure that lot_qty is not more than the allocated for
113 --specific lot
114 IF ( l_lot_cnt = 0 OR l_child_rec.lot_qty > l_orig_lot_qty ) THEN
115 IF (l_debug = 1) THEN
116 trace('Invalid Lot:Allocated Lot is not chosen or qty is greater');
117 END IF;
118 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LOT');
119 FND_MSG_PUB.ADD;
120 RAISE FND_API.G_EXC_ERROR;
121 END IF;
122 ELSIF p_error_on_lot_serial_null = 'Y' THEN--l_rec.LOT_NUMBER is null
123 IF (l_debug = 1) THEN
124 trace('Erroring out:Lot info NOT provided in child record');
125 END IF;
126 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LOT');
127 FND_MSG_PUB.ADD;
128 RAISE FND_API.G_EXC_ERROR;
129 END IF;
130
131 /* -- Serial Not supported, Will be done later
132 IF (l_serial_code >1 AND l_serial_code<>6) THEN --both lot and serial controlled
133
134 IF l_child_rec.serial_number IS NOT NULL THEN
135 SELECT 1 INTO l_serial_cnt FROM dual WHERE exists
136 (SELECT fm_serial_number--What if RANGE serials ??
137 FROM mtl_serial_numbers_temp msnt,
138 mtl_transaction_lots_temp mtlt
139 WHERE mtlt.transaction_temp_id = p_txn_temp_id
140 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
141 AND msnt.fm_serial_number = l_child_rec.serial_number);
142
143 IF (l_serial_cnt = 0) THEN
144 IF (l_debug = 1) THEN
145 trace('Invalid Serial:Either allocate_serial_flag IS off FOR the org OR Allocated Serial is not chosen');
146 END IF;
147 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SER');
148 FND_MSG_PUB.ADD;
149 RAISE FND_API.G_EXC_ERROR;
150 END IF;
151 ELSIF p_error_on_lot_serial_null = 'Y' THEN --l_child_rec.serial_number IS NULL
152 IF (l_debug = 1) THEN
153 trace('Erroring out:serial info NOT provided in child record');
154 END IF;
155 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SER');
156 FND_MSG_PUB.ADD;
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159 END IF;
160
161 ELSIF (l_serial_code >1 AND l_serial_code<>6) THEN --serial controlled only
162
163 IF l_child_rec.serial_number IS NOT NULL THEN
164 SELECT 1 INTO l_serial_cnt FROM dual WHERE exists
165 (SELECT fm_serial_number
166 FROM mtl_serial_numbers_temp msnt
167 WHERE msnt.transaction_temp_id = p_txn_temp_id
168 AND msnt.fm_serial_number = l_child_rec.serial_number);
169
170 IF (l_serial_cnt = 0) THEN
171 IF (l_debug = 1) THEN
172 trace('Invalid Serial:Either allocate_serial_flag IS off FOR the org OR Allocated Serial is not chosen');
173 END IF;
174 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SER');
175 FND_MSG_PUB.ADD;
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178 ELSIF p_error_on_lot_serial_null = 'Y' THEN --l_child_rec.serial_number IS NULL
179 IF (l_debug = 1) THEN
180 trace('Erroring out:serial info NOT provided in child record');
181 END IF;
182 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SER');
183 FND_MSG_PUB.ADD;
184 RAISE FND_API.G_EXC_ERROR;
185 END IF;
186
187 */
188
189
190 END IF;--means LOT CONTROLED
191
192 END LOOP;--for child records
193
194 x_return_status:=FND_API.G_RET_STS_SUCCESS;
195
196 EXCEPTION
197 WHEN FND_API.g_exc_error THEN
198 x_return_status := FND_API.G_RET_STS_ERROR;
199
200 WHEN OTHERS THEN
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202
203 END validate_child_record;
204
205 ---************
206
207
208 PROCEDURE populate_history(x_request_id OUT NOCOPY NUMBER)IS
209
210 l_request_id NUMBER;
211 l_request_date DATE;
212 l_device_id NUMBER;
213 l_requested_by NUMBER;
214 l_CREATION_DATE DATE;
215 l_CREATED_BY NUMBER;
216 l_LAST_UPDATE_DATE DATE;
217 l_resp_application_id NUMBER;
218 l_resp_id NUMBER;
219 l_last_updated_by NUMBER ;
220 l_status_code VARCHAR2(1);
221
222 l_parent_rec_cnt NUMBER :=0;
223
224 CURSOR tsk_confirm_cursor IS
225 SELECT relation_id -- all following are passed by WCS
226 , task_id
227 , task_summary
228 , business_event_id
229 , transaction_quantity
230 , transfer_sub_code
231 , transfer_loc_id
232 , lpn_id
233 , xfer_lpn_id
234 , device_status
235 , reason_id
236 , organization_id--Its NOT NULL Column
237 , status_code
238 , status_msg
239 , lot_number
240 , lot_qty
241 , serial_number
242 , device_id
243 FROM wms_device_requests
244 WHERE business_event_id IN (wms_device_integration_pvt.wms_be_load_confirm,wms_device_integration_pvt.wms_be_task_confirm)
245 ORDER BY relation_id ASC,task_id ASC, task_summary desc;
246
247 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
248 BEGIN
249
250 SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
251 IF (l_debug = 1) THEN
252 trace('Inside populate_History: New request_id for all records:'||l_request_id);
253 END IF;
254
255
256 FOR l_rec IN tsk_confirm_cursor LOOP
257
258 --get all values for NOT NULL columns of WDRH from hist table, it will
259 --remain same for child records AS well
260
261 --get these value only once for the main record which will remain
262 --same FOR the child RECORD AS well, since the cursor is ordered by
263 --task_summary desc for each relation id and task_id,first will be the main
264 --record then will be child records
265
266 IF l_rec.task_summary = 'Y' THEN
267
268 l_parent_rec_cnt := l_parent_rec_cnt+1;
269 BEGIN
270 SELECT
271 DEVICE_ID
272 ,responsibility_application_id
273 ,responsibility_id
274 INTO
275 l_device_id
276 ,l_resp_application_id
277 ,l_resp_id
278 FROM wms_device_requests_hist
279 WHERE request_id = nvl(l_rec.relation_id,-1)
280 --nvl,to handle ERROR: when WCS does NOT pass relation_id or task_id
281 AND task_id = nvl(l_rec.task_id,-1)
282 AND task_summary = 'Y'
283 AND business_event_id IN (wms_device_integration_pvt.wms_be_pick_release,wms_device_integration_pvt.wms_be_wip_pick_release,wms_device_integration_pvt.wms_be_mo_task_alloc)
284 AND ROWNUM<2;
285 EXCEPTION
286 WHEN no_data_found THEN
287
288 l_device_id := nvl(l_rec.device_id,-1);
289 l_resp_application_id := -1;
290 l_resp_id := -1;
291 IF (l_debug = 1) THEN
292 trace('Invalid txn_temp_id ::'||l_rec.task_id||'or relation_id::'||l_rec.relation_id);
293 END IF;
294 END;
295
296 END IF;
297
298 IF (l_rec.task_id IS NULL) OR (l_rec.relation_id IS NULL) THEN
299 l_rec.status_code := 'E';
300 l_rec.status_msg := 'Error:Null parent_task_id or task_id';
301 END IF;
302
303
304 INSERT INTO wms_device_requests_hist(request_id
305 , relation_id -- parent_request_id
306 , task_id
307 , business_event_id
308 , transaction_quantity
309 , transfer_sub_code
310 , transfer_loc_id
311 , lpn_id
312 , xfer_lpn_id
313 , device_status
314 , reason_id
315 , task_summary
316 , organization_id
317 , device_id
318 , request_date
319 , requested_by
320 , status_code
321 , status_msg
322 , responsibility_application_id
323 , responsibility_id
324 , creation_date
325 , created_by
326 , last_update_date
327 , last_updated_by
328 , lot_number
329 , lot_qty
330 , serial_number
331 ) VALUES (l_request_id
332 , l_rec.relation_id
333 , l_rec.task_id
334 , l_rec.business_event_id
335 , l_rec.transaction_quantity
336 , l_rec.transfer_sub_code
337 , l_rec.transfer_loc_id
338 , l_rec.lpn_id
339 , l_rec.xfer_lpn_id
340 , l_rec.device_status
341 , l_rec.reason_id
342 , l_rec.task_summary
343 , l_rec.organization_id
344 , l_device_id
345 , SYSDATE
346 , fnd_global.USER_ID
347 , nvl(l_rec.status_code,'E') --Bug#4535546.Added nvl
348 , l_rec.status_msg
349 , l_resp_application_id
350 , l_resp_id
351 , SYSDATE
352 , fnd_global.USER_ID
353 , SYSDATE
354 , fnd_global.USER_ID
355 , l_rec.lot_number
356 , l_rec.lot_qty
357 , l_rec.serial_number
358 );
359
360
361
362 END LOOP;
363 x_request_id :=l_request_id;
364 IF (l_debug = 1) THEN
365 trace('populate_History:Total parent rec processed::'||l_parent_rec_cnt);
366 END IF;
367
368 END populate_history;
369
370 ---------------------------------------------------------
371 -- Retrieve err message from the message stack
372 ---------------------------------------------------------
373 FUNCTION GET_MSG_STACK RETURN VARCHAR2 IS
374 l_msg_count number;
375 l_msg_data varchar2(240);
376 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
377 BEGIN
378 fnd_msg_pub.count_And_get(
379 p_count => l_msg_count,
380 p_data => l_msg_data,
381 p_encoded => 'F'
382 );
383 IF (l_debug = 1) THEN
384 trace('get message stack, count='||l_msg_count);
385 END IF;
386 IF l_msg_count = 0 THEN
387 l_msg_data := '';
388 ELSIF l_msg_count =1 THEN
389 null;
390 ELSE
391 l_msg_data := fnd_msg_pub.get(l_msg_count,'F');
392
393 END IF;
394 FND_MSG_PUB.initialize;
395 RETURN l_msg_data;
396 END GET_MSG_STACK;
397
398
399 PROCEDURE update_wdr_for_error_rec(p_task_id IN NUMBER
400 ,p_relation_id IN NUMBER) IS
401 l_status_msg VARCHAR2(240);
402
403 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
404 BEGIN
405 l_status_msg := get_msg_stack;--only last message
406 --IN the stack
407 UPDATE wms_device_requests
408 SET status_code = 'E',
409 status_msg = l_status_msg
410 WHERE business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
411 AND task_id = p_task_id
412 AND relation_id = p_relation_id;
413
414 IF (l_debug = 1) THEN
415 trace('ERROR: Current record completed with Error:task_id:'||p_task_id||'::relation_id:'||p_relation_id);
416 END IF;
417 END update_wdr_for_error_rec;
418
419
420
421
422 --this api checks for items, lot/serial, qty in picked/allocated LPN and returns appropriate
423 --value for LPN match
424 /*
425 The following table gives the conditions checked by LPN Match
426 and its return values
427
428 Condition x_match x_return_status
429 =================================================================
430 LPN already picked 7 E
431 LPN location is invalid 6 E
432 LPN SUB is null 10 E
433 LPN already staged for another SO 12 E
434 Item/Lot/Revision is not in LPN 5 E
435 LPN has multiple items 2 S
436
437 LPN has requested item but quantity is 4 S
438 more that the allocated quantity
439
440 Serial number is not valid for this 11 E
441 transaction.
442 LPN has requested item with sufficient 8 E
443 quantity but LPN content status is
444 invalid
445 Serial Allocation was requested for the 9 E
446 item but it is not allowed/there
447 Everything allright and exact quantity 1 S
448 match.LPN has only this item
449 Everything allright and quantity in LPN 3 S
450 is less than requested quantity.LPN has only
451 this item
452
453 Although x_match is being set even for error conditions
454 it is used by the calling code ONLY in case of success
455
456 */
457
458 PROCEDURE get_lpn_match
459 ( p_lpn IN NUMBER
460 , p_org_id IN NUMBER
461 , p_item_id IN NUMBER
462 , p_rev IN VARCHAR2
463 , p_lot IN VARCHAR2
464 , p_qty IN NUMBER
465 , p_uom IN VARCHAR2
466 , x_match OUT NOCOPY NUMBER
467 , x_sub OUT NOCOPY VARCHAR2
468 , x_loc OUT NOCOPY VARCHAR2
469 , x_qty OUT NOCOPY NUMBER
470 , x_return_status OUT NOCOPY VARCHAR2
471 , x_msg_count OUT NOCOPY NUMBER
472 , x_msg_data OUT NOCOPY VARCHAR2
473 , p_temp_id IN NUMBER
474 , p_wms_installed IN VARCHAR2
475 , p_transaction_type_id IN NUMBER
476 , p_cost_group_id IN NUMBER
477 , p_is_sn_alloc IN VARCHAR2
478 , p_action IN NUMBER
479 , x_temp_id OUT NOCOPY NUMBER
480 , x_loc_id OUT NOCOPY NUMBER
481 , x_lpn_lot_vector OUT NOCOPY VARCHAR2
482 )
483
484 IS
485
486 l_msg_cnt NUMBER;
487 l_msg_data VARCHAR2(2000);
488 l_return_status VARCHAR2(240);
489
490 l_exist_qty NUMBER;
491 l_item_cnt NUMBER;
492 l_rev_cnt NUMBER;
493 l_lot_cnt NUMBER;
494 l_item_cnt2 NUMBER;
495 l_cg_cnt NUMBER;
496
497 l_sub VARCHAR2(60);
498 l_loc VARCHAR2(60);
499 l_loaded NUMBER := 0;
500 l_allocate_serial_flag NUMBER := 0;
501 l_temp_serial_trans_temp NUMBER := 0;
502 l_serial_number VARCHAR2(50);
503
504 l_lpn_qty NUMBER;
505 l_lpn_uom VARCHAR2(3);
506
507 l_txn_uom VARCHAR2(3);
508
509 l_primary_uom VARCHAR2(3);
510 l_lot_code NUMBER;
511 l_serial_code NUMBER;
512 l_mmtt_qty NUMBER;
513
514 l_out_temp_id NUMBER:=0;
515
516 l_serial_exist_cnt NUMBER:=0;
517 l_total_serial_cnt NUMBER:=0;
518 l_so_cnt NUMBER:=0;
519 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
520 l_mtlt_lot_number VARCHAR2(80);
521 l_mtlt_primary_qty NUMBER;
522 l_wlc_quantity NUMBER;
523 l_wlc_uom_code VARCHAR2(3);
524 l_lot_match NUMBER;
525 l_ok_to_process VARCHAR2(5);
526 l_is_revision_control VARCHAR2(5);
527 l_is_lot_control VARCHAR2(5);
528 l_is_serial_control VARCHAR2(5);
529 b_is_revision_control BOOLEAN;
530 b_is_lot_control BOOLEAN;
531 b_is_serial_control BOOLEAN;
532 l_from_lpn VARCHAR2(30);
533 l_loc_id NUMBER;
534 l_lpn_context NUMBER;
535
536 l_lpn_exists NUMBER;
537 l_qoh NUMBER;
538 l_rqoh NUMBER;
539 l_qr NUMBER;
540 l_qs NUMBER;
541 l_att NUMBER;
542 l_atr NUMBER;
543 l_allocated_lpn_id NUMBER;
544 l_table_index NUMBER := 0;
545 l_table_total NUMBER := 0;
546 l_table_count NUMBER;
547 l_lpn_include_lpn NUMBER;
548 l_xfr_sub_code VARCHAR2(30);
549 l_sub_active NUMBER := 0;
550 l_loc_active NUMBER := 0;
551
552
553 CURSOR ser_csr IS
554 SELECT serial_number
555 FROM mtl_serial_numbers
556 WHERE lpn_id = p_lpn
557 AND inventory_item_id = p_item_id
558 AND Nvl(lot_number,-999) = Nvl(p_lot,-999);
559
560 CURSOR lot_csr IS
561 SELECT
562 mtlt.primary_quantity,
563 mtlt.lot_number
564 FROM
565 mtl_transaction_lots_temp mtlt
566 WHERE mtlt.transaction_temp_id = p_temp_id;
567
568 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
569 BEGIN
570
571 IF (l_debug = 1) THEN
572 trace('lpn_match: In lpn Match');
573 END IF;
574
575 l_lpn_qty := p_qty;
576
577 x_return_status:= FND_API.G_RET_STS_SUCCESS;
578
579 l_lpn_exists := 0;
580
581 --clear the PL/SQL table each time come in
582 t_lpn_lot_qty_table.delete;
583
584 BEGIN
585
586 SELECT 1,
587 lpn_context
588 INTO l_lpn_exists,
589 l_lpn_context
590 FROM wms_license_plate_numbers wlpn
591 WHERE wlpn.organization_id = p_org_id
592 AND wlpn.lpn_id = p_lpn;
593
594 EXCEPTION
595
596 WHEN no_data_found THEN
597
598 IF (l_debug = 1) THEN
599 trace('lpn_match: lpn does not exist in org');
600 END IF;
601 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_INVALID_LPN');
602 FND_MSG_PUB.ADD;
603 RAISE FND_API.G_EXC_ERROR;
604 END;
605
606 IF l_lpn_exists = 0 OR
607 p_lpn = 0 OR
608 l_lpn_context <> wms_container_pub.lpn_context_inv THEN
609
610 IF (l_debug = 1) THEN
611 trace('lpn_match: lpn does not exist in org');
612 END IF;
613 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_INVALID_LPN');
614 FND_MSG_PUB.ADD;
615 RAISE FND_API.G_EXC_ERROR;
616
617 END IF;
618
619 IF (l_debug = 1) THEN
620 trace('lpn_match: Checking if lpn has been picked already');
621 END IF;
622
623 x_match := 0;
624
625 BEGIN
626
627 SELECT 1
628 INTO l_loaded
629 FROM dual
630 WHERE exists
631 ( SELECT 1
632 from mtl_material_transactions_temp
633 where transaction_header_id
634 =(SELECT transaction_header_id
635 from mtl_material_transactions_temp
636 WHERE transaction_temp_id=p_temp_id)
637 AND (transfer_lpn_id=p_lpn OR content_lpn_id=p_lpn)
638 AND cost_group_id = p_cost_group_id);
639
640 EXCEPTION
641
642 WHEN NO_DATA_FOUND THEN
643 l_loaded:=0;
644
645 END;
646
647 IF l_loaded > 0 THEN
648
649 x_match := 7;
650 FND_MESSAGE.SET_NAME('WMS','WMS_LOADED_ERROR');
651 FND_MSG_PUB.ADD;
652 RAISE FND_API.G_EXC_ERROR;
653
654 END IF;
655
656 -- Check if locator is valid
657 IF (l_debug = 1) THEN
658 trace('lpn_match: Fetch sub/loc for LPN ');
659 END IF;
660
661 BEGIN
662 -- WMS PJM Integration, Selecting the resolved concatenated segments instead of concatenated segments
663 SELECT
664 w.subinventory_code,
665 INV_PROJECT.GET_LOCSEGS(w.locator_id, w.organization_id),
666 w.license_plate_number,
667 w.locator_id,
668 w.lpn_context
669 INTO
670 l_sub,
671 l_loc,
672 l_from_lpn,
673 l_loc_id,
674 l_lpn_context
675 FROM
676 wms_license_plate_numbers w
677 WHERE
678 w.lpn_id = p_lpn
679 AND w.locator_id is not null;
680
681 IF l_sub IS NULL THEN
682
683 -- The calling java code treats this condition as an error
684
685 x_match := 10;
686 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_INVALID_SUB');
687 FND_MSG_PUB.ADD;
688 RAISE FND_API.G_EXC_ERROR;
689
690 END IF;
691
692 -- bug 2398247
693 -- verify if sub is active
694 SELECT COUNT(*)
695 INTO l_sub_active
696 FROM mtl_secondary_inventories
697 WHERE Nvl(disable_date, Sysdate+1) > Sysdate
698 AND organization_id = p_org_id
699 AND secondary_inventory_name = l_sub;
700
701 IF l_sub_active = 0 THEN
702 x_match := 10;
703 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_INVALID_SUB');
704 FND_MSG_PUB.ADD;
705 RAISE FND_API.G_EXC_ERROR;
706 END IF;
707
708 -- verify if locator is active
709 SELECT COUNT(*)
710 INTO l_loc_active
711 FROM mtl_item_locations_kfv
712 WHERE Nvl(disable_date, sysdate+1) > sysdate
713 AND organization_id = p_org_id
714 AND subinventory_code = l_sub
715 AND inventory_location_id = l_loc_id;
716
717 IF l_loc_active = 0 THEN
718
719 x_match := 10;
720 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_INVALID_LOC');
721 FND_MSG_PUB.ADD;
722 RAISE FND_API.G_EXC_ERROR;
723
724 END IF;
725
726 x_sub := l_sub;
727 x_loc := l_loc;
728 x_loc_id := l_loc_id;
729
730 EXCEPTION
731
732 WHEN NO_DATA_FOUND THEN
733 x_match := 6;
734 FND_MESSAGE.SET_NAME('WMS','WMS_TD_LPN_LOC_NOT_FOUND');
735 FND_MSG_PUB.ADD;
736 RAISE FND_API.G_EXC_ERROR;
737
738 END;
739
740 IF (l_debug = 1) THEN
741 trace('lpn_match: sub is '||l_sub);
742 trace('lpn_match: loc is '||l_loc);
743 END IF;
744
745 -- Check if LPN has already been allocated for any Sales order
746 -- If LPN has been picked for a sales order then it cannot be picked
747
748 IF (l_debug = 1) THEN
749 trace('lpn_match: Checking SO for lpn');
750 END IF;
751
752 BEGIN
753
754 SELECT 1
755 INTO l_so_cnt
756 FROM dual
757 WHERE exists
758 (SELECT 1
759 FROM wsh_delivery_details
760 WHERE lpn_id=p_lpn
761 AND organization_id=p_org_id
762 );
763
764 EXCEPTION
765
766 WHEN NO_DATA_FOUND THEN
767 l_so_cnt := 0;
768
769 END;
770
771 IF l_so_cnt > 0 THEN
772
773 x_match := 12;
774 FND_MESSAGE.SET_NAME('WMS','WMS_LPN_STAGED');
775 FND_MSG_PUB.ADD;
776 RAISE FND_API.G_EXC_ERROR;
777
778 END IF;
779
780 SELECT
781 primary_uom_code,
782 lot_control_code,
783 serial_number_control_code
784 INTO
785 l_primary_uom,
786 l_lot_code,
787 l_serial_code
788 FROM mtl_system_items
789 WHERE organization_id = p_org_id
790 AND inventory_item_id = p_item_id;
791
792
793 SELECT mmtt.transfer_subinventory
794 INTO l_xfr_sub_code
795 FROM mtl_material_transactions_temp mmtt
796 WHERE mmtt.transaction_temp_id = p_temp_id;
797
798
799 -- Check to see if the item is in the LPN
800 IF (l_debug = 1) THEN
801 trace('lpn_match: Checking to see if required item,cg,rev,lot exist in lpn..');
802 END IF;
803
804 l_item_cnt := 0;
805 IF (l_debug = 1) THEN
806 trace('lpn_match: item'||p_item_id||'LPN'||p_lpn || 'Org'||p_org_id||' lot'||p_lot||' Rev'||p_rev);
807 END IF;
808
809 BEGIN
810
811 SELECT 1 INTO l_item_cnt FROM DUAL WHERE exists
812 ( SELECT 1
813 FROM wms_lpn_contents wlc
814 WHERE wlc.parent_lpn_id = p_lpn
815 AND wlc.organization_id = p_org_id
816 AND wlc.inventory_item_id = p_item_id
817 AND Nvl(wlc.revision,'-999') = Nvl(p_rev,Nvl(wlc.revision,'-999'))); --bug 2495592
818
819 EXCEPTION
820
821 -- Item/lot/rev combo does not exist in LPN
822
823 WHEN NO_DATA_FOUND THEN
824
825 IF (l_debug = 1) THEN
826 trace('lpn_match: item lot rev combo does not exist');
827 END IF;
828 x_match := 5;
829 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_INVALID_LPN');
830 FND_MSG_PUB.ADD;
831 RAISE FND_API.G_EXC_ERROR;
832
833 END;
834
835 IF l_item_cnt > 0 AND l_lot_code > 1 THEN
836 --Do this only for lot controlled items
837
838 BEGIN
839
840 SELECT 1 INTO l_item_cnt FROM DUAL WHERE exists
841 ( SELECT 1
842 FROM wms_lpn_contents wlc,
843 mtl_transaction_lots_temp mtlt
844 WHERE wlc.parent_lpn_id = p_lpn
845 AND wlc.organization_id = p_org_id
846 AND wlc.inventory_item_id = p_item_id
847 AND Nvl(wlc.revision,'-999') = Nvl(p_rev,Nvl(wlc.revision,'-999'))
848 AND (mtlt.transaction_temp_id = p_temp_id
849 AND mtlt.lot_number = wlc.lot_number));
850
851 EXCEPTION
852
853 -- Item/lot/rev combo does not exist in LPN
854
855 WHEN NO_DATA_FOUND THEN
856
857 IF (l_debug = 1) THEN
858 trace('lpn_match:lot rev combo for the item does not exist');
859 END IF;
860 x_match := 5;
861 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_INVALID_LOT_LPN');
862 FND_MSG_PUB.ADD;
863 RAISE FND_API.G_EXC_ERROR;
864
865 END;
866
867 END IF;
868
869 -- Item with the correct lot/revision exists in LPN
870 IF p_is_sn_alloc = 'Y' AND p_action = 4 THEN
871 b_is_serial_control := TRUE;
872 l_is_serial_control := 'true';
873 ELSE
874 b_is_serial_control := FALSE;
875 l_is_serial_control := 'false';
876 END IF;
877
878 IF l_lot_code > 1 THEN
879 b_is_lot_control := TRUE;
880 l_is_lot_control := 'true';
881 ELSE
882 b_is_lot_control := FALSE;
883 l_is_lot_control := 'false';
884 END IF;
885
886 IF p_rev IS NULL THEN
887 b_is_revision_control := FALSE;
888 l_is_revision_control := 'false';
889 ELSE
890 b_is_revision_control := TRUE;
891 l_is_revision_control := 'true';
892 END IF;
893
894 IF (l_debug = 1) THEN
895 trace('lpn_match: is_serial_control:' || l_is_serial_control);
896 trace('lpn_match: is_lot_control:' || l_is_lot_control);
897 trace('lpn_match: is_revision_control:' || l_is_revision_control);
898 END IF;
899
900 BEGIN
901 select allocated_lpn_id
902 into l_allocated_lpn_id
903 from mtl_material_transactions_temp
904 where transaction_temp_id = p_temp_id;
905 EXCEPTION
906 WHEN no_data_found then
907 IF (l_debug = 1) THEN
908 trace ('lpn_match: transaction does not exist in mmtt');
909 END IF;
910 FND_MESSAGE.SET_NAME('INV','INV_INVALID_TRANSACTION');
911 FND_MSG_PUB.ADD;
912 RAISE FND_API.G_EXC_ERROR;
913 END;
914
915 -- clear quantity cache before we create qty tree.
916 inv_quantity_tree_pub.clear_quantity_cache;
917
918 -- Check if LPN has items other than the one requested
919
920 IF (l_debug = 1) THEN
921 trace('lpn_match: lpn has the requested item ');
922 END IF;
923
924 l_item_cnt2 := 0;
925 l_lot_cnt := 0;
926 l_rev_cnt := 0;
927 l_cg_cnt := 0;
928
929 l_item_cnt2 := 0;
930 l_lot_cnt := 0;
931 l_rev_cnt := 0;
932 l_cg_cnt := 0;
933
934 l_lpn_include_lpn := 0;
935
936 SELECT count( distinct inventory_item_id ),
937 count( distinct lot_number ),
938 count( distinct revision ) ,
939 count( distinct cost_group_id )
940 INTO l_item_cnt2,
941 l_lot_cnt,
942 l_rev_cnt,
943 l_cg_cnt
944 FROM wms_lpn_contents
945 WHERE parent_lpn_id = p_lpn
946 AND organization_id = p_org_id;
947
948 select count(*)
949 into l_lpn_include_lpn
950 from wms_license_plate_numbers
951 where outermost_lpn_id = p_lpn
952 and organization_id = p_org_id;
953
954
955 IF l_item_cnt2 > 1 OR l_rev_cnt > 1 OR l_lpn_include_lpn > 1 THEN
956
957 -- LPN has multiple items
958 -- Such LPN's can be picked but in such cases the user has to
959 -- manually confirm the LPN.
960 -- No validation for LPN contents in such a case.
961
962 IF (l_debug = 1) THEN
963 trace('lpn_match: lpn has items other than requested item ');
964 END IF;
965
966 x_match := 2;
967
968 IF l_lot_code > 1 THEN
969
970 l_lpn_qty := 0;
971
972 OPEN lot_csr;
973 LOOP
974 FETCH lot_csr
975 INTO
976 l_mtlt_primary_qty,
977 l_mtlt_lot_number;
978
979 EXIT WHEN lot_csr%notfound;
980
981 IF (l_debug = 1) THEN
982 trace('l_mtlt_lot_number : ' || l_mtlt_lot_number);
983 trace('l_mtlt_primary_qty: ' || l_mtlt_primary_qty);
984 END IF;
985
986
987 IF nvl(l_allocated_lpn_id, 0) = p_lpn THEN
988 --from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
989 -- in order to get correct att.
990 inv_quantity_tree_pub.update_quantities
991 ( p_api_version_number => 1.0
992 , p_init_msg_lst => fnd_api.g_false
993 , x_return_status => l_return_status
994 , x_msg_count => l_msg_cnt
995 , x_msg_data => l_msg_data
996 , p_organization_id => p_org_id
997 , p_inventory_item_id => p_item_id
998 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
999 , p_is_revision_control => b_is_revision_control
1000 , p_is_lot_control => TRUE
1001 , p_is_serial_control => b_is_serial_control
1002 , p_revision => nvl(p_rev, NULL)
1003 , p_lot_number => l_mtlt_lot_number
1004 , p_subinventory_code => l_sub
1005 , p_locator_id => l_loc_id
1006 , p_primary_quantity => -l_mtlt_primary_qty
1007 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1008 , x_qoh => l_qoh
1009 , x_rqoh => l_rqoh
1010 , x_qr => l_qr
1011 , x_qs => l_qs
1012 , x_att => l_att
1013 , x_atr => l_atr
1014 , p_lpn_id => p_lpn
1015 , p_transfer_subinventory_code => l_xfr_sub_code
1016 );
1017 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1018 IF (l_debug = 1) THEN
1019 trace('lpn_match: after update qty tree for lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
1020 END IF;
1021 ELSE
1022 IF (l_debug = 1) THEN
1023 trace('lpn_match: calling update qty tree with lpn 1st time failed ');
1024 END IF;
1025 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1026 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1027 FND_MSG_PUB.ADD;
1028 RAISE FND_API.G_EXC_ERROR;
1029 END IF;
1030 ELSE
1031 inv_quantity_tree_pub.update_quantities
1032 ( p_api_version_number => 1.0
1033 , p_init_msg_lst => fnd_api.g_false
1034 , x_return_status => l_return_status
1035 , x_msg_count => l_msg_cnt
1036 , x_msg_data => l_msg_data
1037 , p_organization_id => p_org_id
1038 , p_inventory_item_id => p_item_id
1039 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1040 , p_is_revision_control => b_is_revision_control
1041 , p_is_lot_control => TRUE
1042 , p_is_serial_control => b_is_serial_control
1043 , p_revision => nvl(p_rev, NULL)
1044 , p_lot_number => l_mtlt_lot_number
1045 , p_subinventory_code => l_sub
1046 , p_locator_id => l_loc_id
1047 , p_primary_quantity => -l_mtlt_primary_qty
1048 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1049 , x_qoh => l_qoh
1050 , x_rqoh => l_rqoh
1051 , x_qr => l_qr
1052 , x_qs => l_qs
1053 , x_att => l_att
1054 , x_atr => l_atr
1055 -- , p_lpn_id => p_lpn withour lpn_id, only to locator level
1056 , p_transfer_subinventory_code => l_xfr_sub_code
1057 );
1058 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1059 IF (l_debug = 1) THEN
1060 trace('lpn_match: after update qty tree without lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
1061 END IF;
1062 ELSE
1063 IF (l_debug = 1) THEN
1064 trace('lpn_match: calling update qty tree back without lpn 1st time failed ');
1065 END IF;
1066 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1067 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1068 FND_MSG_PUB.ADD;
1069 RAISE FND_API.G_EXC_ERROR;
1070 END IF;
1071
1072 END IF;
1073
1074 inv_quantity_tree_pub.query_quantities
1075 ( p_api_version_number => 1.0
1076 , p_init_msg_lst => fnd_api.g_false
1077 , x_return_status => l_return_status
1078 , x_msg_count => l_msg_cnt
1079 , x_msg_data => l_msg_data
1080 , p_organization_id => p_org_id
1081 , p_inventory_item_id => p_item_id
1082 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1083 , p_is_revision_control => b_is_revision_control
1084 , p_is_lot_control => TRUE
1085 , p_is_serial_control => b_is_serial_control
1086 , p_demand_source_type_id => -9999
1087 , p_revision => nvl(p_rev, NULL)
1088 , p_lot_number => l_mtlt_lot_number
1089 , p_subinventory_code => l_sub
1090 , p_locator_id => l_loc_id
1091 , x_qoh => l_qoh
1092 , x_rqoh => l_rqoh
1093 , x_qr => l_qr
1094 , x_qs => l_qs
1095 , x_att => l_att
1096 , x_atr => l_atr
1097 , p_lpn_id => p_lpn
1098 , p_transfer_subinventory_code => l_xfr_sub_code
1099 );
1100
1101 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1102
1103 IF (l_att > 0) THEN
1104
1105 l_table_index := l_table_index + 1;
1106
1107 IF (l_mtlt_primary_qty >= l_att) THEN
1108
1109 IF (l_debug = 1) THEN
1110 trace('lpn_match: l_table_index:'||l_table_index||' lot_number:'||l_mtlt_lot_number||' qty: '||l_att);
1111 END IF;
1112 l_lpn_qty := l_lpn_qty + l_att;
1113
1114 t_lpn_lot_qty_table(l_table_index).lpn_id := p_lpn;
1115 t_lpn_lot_qty_table(l_table_index).lot_number := l_mtlt_lot_number;
1116 t_lpn_lot_qty_table(l_table_index).qty := l_att;
1117
1118 ELSE
1119 IF (l_debug = 1) THEN
1120 trace('lpn_match: l_table_index:'||l_table_index||' lot_number:'||l_mtlt_lot_number||' qty: '||l_mtlt_primary_qty);
1121 END IF;
1122 l_lpn_qty := l_lpn_qty + l_mtlt_primary_qty;
1123
1124 t_lpn_lot_qty_table(l_table_index).lpn_id := p_lpn;
1125 t_lpn_lot_qty_table(l_table_index).lot_number := l_mtlt_lot_number;
1126 t_lpn_lot_qty_table(l_table_index).qty := l_mtlt_primary_qty;
1127
1128 END IF;
1129
1130 ELSE
1131
1132 IF (l_debug = 1) THEN
1133 trace('lpn_match: LPN does not have lot ' || l_mtlt_lot_number);
1134 END IF;
1135 /*trace('lpn_match: l_table_index:'||l_table_index||' lot_number:'||l_mtlt_lot_number||' qty: 0 ');
1136 t_lpn_lot_qty_table(l_table_index).lpn_id := p_lpn;
1137 t_lpn_lot_qty_table(l_table_index).lot_number := l_mtlt_lot_number;
1138 t_lpn_lot_qty_table(l_table_index).qty := l_mtlt_primary_qty;*/
1139
1140 END IF;
1141
1142 ELSE
1143
1144 IF (l_debug = 1) THEN
1145 trace('lpn_match: calling qty tree 1st time failed ');
1146 END IF;
1147 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1148 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1149 FND_MSG_PUB.ADD;
1150 RAISE FND_API.G_EXC_ERROR;
1151
1152 END IF;
1153
1154 IF nvl(l_allocated_lpn_id, 0) = p_lpn THEN
1155 --from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
1156 -- in order to get correct att.
1157 inv_quantity_tree_pub.update_quantities
1158 ( p_api_version_number => 1.0
1159 , p_init_msg_lst => fnd_api.g_false
1160 , x_return_status => l_return_status
1161 , x_msg_count => l_msg_cnt
1162 , x_msg_data => l_msg_data
1163 , p_organization_id => p_org_id
1164 , p_inventory_item_id => p_item_id
1165 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1166 , p_is_revision_control => b_is_revision_control
1167 , p_is_lot_control => TRUE
1168 , p_is_serial_control => b_is_serial_control
1169 , p_revision => nvl(p_rev, NULL)
1170 , p_lot_number => l_mtlt_lot_number
1171 , p_subinventory_code => l_sub
1172 , p_locator_id => l_loc_id
1173 , p_primary_quantity => l_mtlt_primary_qty
1174 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1175 , x_qoh => l_qoh
1176 , x_rqoh => l_rqoh
1177 , x_qr => l_qr
1178 , x_qs => l_qs
1179 , x_att => l_att
1180 , x_atr => l_atr
1181 , p_lpn_id => p_lpn
1182 , p_transfer_subinventory_code => l_xfr_sub_code
1183 );
1184 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1185 IF (l_debug = 1) THEN
1186 trace('lpn_match: after update qty tree back for lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
1187 END IF;
1188 ELSE
1189 IF (l_debug = 1) THEN
1190 trace('lpn_match: calling update qty tree back with lpn 1st time failed ');
1191 END IF;
1192 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1193 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1194 FND_MSG_PUB.ADD;
1195 RAISE FND_API.G_EXC_ERROR;
1196 END IF;
1197 ELSE
1198 inv_quantity_tree_pub.update_quantities
1199 ( p_api_version_number => 1.0
1200 , p_init_msg_lst => fnd_api.g_false
1201 , x_return_status => l_return_status
1202 , x_msg_count => l_msg_cnt
1203 , x_msg_data => l_msg_data
1204 , p_organization_id => p_org_id
1205 , p_inventory_item_id => p_item_id
1206 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1207 , p_is_revision_control => b_is_revision_control
1208 , p_is_lot_control => TRUE
1209 , p_is_serial_control => b_is_serial_control
1210 , p_revision => nvl(p_rev, NULL)
1211 , p_lot_number => l_mtlt_lot_number
1212 , p_subinventory_code => l_sub
1213 , p_locator_id => l_loc_id
1214 , p_primary_quantity => l_mtlt_primary_qty
1215 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1216 , x_qoh => l_qoh
1217 , x_rqoh => l_rqoh
1218 , x_qr => l_qr
1219 , x_qs => l_qs
1220 , x_att => l_att
1221 , x_atr => l_atr
1222 -- , p_lpn_id => p_lpn withour lpn_id, only to locator level
1223 , p_transfer_subinventory_code => l_xfr_sub_code
1224 );
1225 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1226 IF (l_debug = 1) THEN
1227 trace('lpn_match: after update qty tree back without lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
1228 END IF;
1229 ELSE
1230 IF (l_debug = 1) THEN
1231 trace('lpn_match: calling update qty tree back without lpn 1st time failed ');
1232 END IF;
1233 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1234 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1235 FND_MSG_PUB.ADD;
1236 RAISE FND_API.G_EXC_ERROR;
1237 END IF;
1238 END IF;
1239
1240 END LOOP;
1241 CLOSE lot_csr;
1242
1243 ELSIF p_is_sn_alloc = 'Y' AND p_action = 4 THEN
1244
1245 IF (l_debug = 1) THEN
1246 trace('lpn_match: SN control and SN allocation on');
1247 END IF;
1248
1249 SELECT COUNT(fm_serial_number)
1250 INTO l_serial_exist_cnt
1251 FROM mtl_serial_numbers_temp msnt
1252 WHERE msnt.transaction_temp_id = p_temp_id
1253 AND msnt.fm_serial_number IN
1254 ( SELECT serial_number
1255 FROM mtl_serial_numbers
1256 WHERE lpn_id = p_lpn
1257 AND inventory_item_id = p_item_id
1258 AND Nvl(revision, '-999') = Nvl(p_rev, '-999')
1259 );
1260
1261 IF (l_debug = 1) THEN
1262 trace('lpn_match: SN exist count'||l_serial_exist_cnt);
1263 END IF;
1264
1265 IF ( l_serial_exist_cnt = 0 ) THEN
1266 IF (l_debug = 1) THEN
1267 trace('lpn_match: LPN does not have the allocated serials ');
1268 END IF;
1269 -- Serial numbers missing for the transaction
1270 x_match := 9;
1271 FND_MESSAGE.SET_NAME('INV','INV_INT_SERMISEXP');
1272 FND_MSG_PUB.ADD;
1273 RAISE FND_API.G_EXC_ERROR;
1274 END IF;
1275
1276
1277 SELECT COUNT(fm_serial_number)
1278 INTO l_total_serial_cnt
1279 FROM mtl_serial_numbers_temp msnt,
1280 mtl_transaction_lots_temp mtlt
1281 WHERE mtlt.transaction_temp_id = p_temp_id
1282 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
1283
1284 IF (l_debug = 1) THEN
1285 trace('lpn_match: SN tot count'||l_total_serial_cnt);
1286 END IF;
1287 IF ( l_total_serial_cnt > l_serial_exist_cnt ) THEN
1288 IF (l_debug = 1) THEN
1289 trace('lpn_match: LPN has less');
1290 END IF;
1291 l_lpn_qty := l_serial_exist_cnt;
1292
1293 END IF;
1294
1295 ELSE -- Plain item OR REVISION controlled item
1296
1297 IF (l_debug = 1) THEN
1298 trace('lpn_match: Getting total qty in user entered uom..');
1299 END IF;
1300
1301 IF nvl(l_allocated_lpn_id, 0) = p_lpn THEN
1302 --from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
1303 -- in order to get correct att.
1304 inv_quantity_tree_pub.update_quantities
1305 ( p_api_version_number => 1.0
1306 , p_init_msg_lst => fnd_api.g_false
1307 , x_return_status => l_return_status
1308 , x_msg_count => l_msg_cnt
1309 , x_msg_data => l_msg_data
1310 , p_organization_id => p_org_id
1311 , p_inventory_item_id => p_item_id
1312 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1313 , p_is_revision_control => b_is_revision_control
1314 , p_is_lot_control => FALSE
1315 , p_is_serial_control => b_is_serial_control
1316 , p_revision => nvl(p_rev, NULL)
1317 , p_lot_number => null
1318 , p_subinventory_code => l_sub
1319 , p_locator_id => l_loc_id
1320 , p_primary_quantity => -p_qty
1321 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1322 , x_qoh => l_qoh
1323 , x_rqoh => l_rqoh
1324 , x_qr => l_qr
1325 , x_qs => l_qs
1326 , x_att => l_att
1327 , x_atr => l_atr
1328 , p_lpn_id => p_lpn
1329 , p_transfer_subinventory_code => l_xfr_sub_code
1330 );
1331 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1332 IF (l_debug = 1) THEN
1333 trace('lpn_match: update qty tree with lpn 2nd time: l_att:' || l_att);
1334 END IF;
1335 ELSE
1336 IF (l_debug = 1) THEN
1337 trace('lpn_match: calling update qty tree with lpn 2nd time failed ');
1338 END IF;
1339 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1340 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1341 FND_MSG_PUB.ADD;
1342 RAISE FND_API.G_EXC_ERROR;
1343 END IF;
1344 ELSE
1345 inv_quantity_tree_pub.update_quantities
1346 ( p_api_version_number => 1.0
1347 , p_init_msg_lst => fnd_api.g_false
1348 , x_return_status => l_return_status
1349 , x_msg_count => l_msg_cnt
1350 , x_msg_data => l_msg_data
1351 , p_organization_id => p_org_id
1352 , p_inventory_item_id => p_item_id
1353 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1354 , p_is_revision_control => b_is_revision_control
1355 , p_is_lot_control => FALSE
1356 , p_is_serial_control => b_is_serial_control
1357 , p_revision => nvl(p_rev, NULL)
1358 , p_lot_number => null
1359 , p_subinventory_code => l_sub
1360 , p_locator_id => l_loc_id
1361 , p_primary_quantity => -p_qty
1362 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1363 , x_qoh => l_qoh
1364 , x_rqoh => l_rqoh
1365 , x_qr => l_qr
1366 , x_qs => l_qs
1367 , x_att => l_att
1368 , x_atr => l_atr
1369 -- , p_lpn_id => p_lpn withour lpn_id, only to locator level
1370 , p_transfer_subinventory_code => l_xfr_sub_code
1371 );
1372 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1373 IF (l_debug = 1) THEN
1374 trace('lpn_match: update qty tree without lpn 2nd time:l_att:'||l_att);
1375 END IF;
1376 ELSE
1377 IF (l_debug = 1) THEN
1378 trace('lpn_match: calling update qty tree back without lpn 2nd time failed ');
1379 END IF;
1380 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1381 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1382 FND_MSG_PUB.ADD;
1383 RAISE FND_API.G_EXC_ERROR;
1384 END IF;
1385
1386 END IF;
1387
1388 inv_quantity_tree_pub.query_quantities
1389 ( p_api_version_number => 1.0
1390 , p_init_msg_lst => fnd_api.g_false
1391 , x_return_status => l_return_status
1392 , x_msg_count => l_msg_cnt
1393 , x_msg_data => l_msg_data
1394 , p_organization_id => p_org_id
1395 , p_inventory_item_id => p_item_id
1396 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1397 , p_is_revision_control => b_is_revision_control
1398 , p_is_lot_control => FALSE
1399 , p_is_serial_control => b_is_serial_control
1400 , p_demand_source_type_id => -9999
1401 , p_revision => nvl(p_rev, NULL)
1402 , p_lot_number => null
1403 , p_subinventory_code => l_sub
1404 , p_locator_id => l_loc_id
1405 , x_qoh => l_qoh
1406 , x_rqoh => l_rqoh
1407 , x_qr => l_qr
1408 , x_qs => l_qs
1409 , x_att => l_att
1410 , x_atr => l_atr
1411 , p_lpn_id => p_lpn
1412 , p_transfer_subinventory_code => l_xfr_sub_code
1413 );
1414
1415 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1416
1417 l_lpn_qty := l_att;
1418
1419 ELSE
1420
1421 IF (l_debug = 1) THEN
1422 trace('lpn_match: calling qty tree 2nd time failed ');
1423 END IF;
1424 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1425 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1426 FND_MSG_PUB.add;
1427 RAISE FND_API.G_EXC_ERROR;
1428
1429 END IF;
1430
1431 IF nvl(l_allocated_lpn_id, 0) = p_lpn THEN
1432 --from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
1433 -- in order to get correct att.
1434 inv_quantity_tree_pub.update_quantities
1435 ( p_api_version_number => 1.0
1436 , p_init_msg_lst => fnd_api.g_false
1437 , x_return_status => l_return_status
1438 , x_msg_count => l_msg_cnt
1439 , x_msg_data => l_msg_data
1440 , p_organization_id => p_org_id
1441 , p_inventory_item_id => p_item_id
1442 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1443 , p_is_revision_control => b_is_revision_control
1444 , p_is_lot_control => FALSE
1445 , p_is_serial_control => b_is_serial_control
1446 , p_revision => nvl(p_rev, NULL)
1447 , p_lot_number => null
1448 , p_subinventory_code => l_sub
1449 , p_locator_id => l_loc_id
1450 , p_primary_quantity => p_qty
1451 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1452 , x_qoh => l_qoh
1453 , x_rqoh => l_rqoh
1454 , x_qr => l_qr
1455 , x_qs => l_qs
1456 , x_att => l_att
1457 , x_atr => l_atr
1458 , p_lpn_id => p_lpn
1459 , p_transfer_subinventory_code => l_xfr_sub_code
1460 );
1461 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1462 IF (l_debug = 1) THEN
1463 trace('lpn_match: update qty tree back with lpn 2nd time: l_att:' || l_att);
1464 END IF;
1465 ELSE
1466 IF (l_debug = 1) THEN
1467 trace('lpn_match: calling update qty tree with lpn 2nd time failed ');
1468 END IF;
1469 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1470 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1471 FND_MSG_PUB.ADD;
1472 RAISE FND_API.G_EXC_ERROR;
1473 END IF;
1474 ELSE
1475 inv_quantity_tree_pub.update_quantities
1476 ( p_api_version_number => 1.0
1477 , p_init_msg_lst => fnd_api.g_false
1478 , x_return_status => l_return_status
1479 , x_msg_count => l_msg_cnt
1480 , x_msg_data => l_msg_data
1481 , p_organization_id => p_org_id
1482 , p_inventory_item_id => p_item_id
1483 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1484 , p_is_revision_control => b_is_revision_control
1485 , p_is_lot_control => FALSE
1486 , p_is_serial_control => b_is_serial_control
1487 , p_revision => nvl(p_rev, NULL)
1488 , p_lot_number => null
1489 , p_subinventory_code => l_sub
1490 , p_locator_id => l_loc_id
1491 , p_primary_quantity => p_qty
1492 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1493 , x_qoh => l_qoh
1494 , x_rqoh => l_rqoh
1495 , x_qr => l_qr
1496 , x_qs => l_qs
1497 , x_att => l_att
1498 , x_atr => l_atr
1499 -- , p_lpn_id => p_lpn withour lpn_id, only to locator level
1500 , p_transfer_subinventory_code => l_xfr_sub_code
1501 );
1502 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1503 IF (l_debug = 1) THEN
1504 trace('lpn_match: update qty tree back without lpn 2nd time:l_att:'||l_att);
1505 END IF;
1506 ELSE
1507 IF (l_debug = 1) THEN
1508 trace('lpn_match: calling update qty tree back without lpn 2nd time failed ');
1509 END IF;
1510 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1511 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1512 FND_MSG_PUB.ADD;
1513 RAISE FND_API.G_EXC_ERROR;
1514 END IF;
1515
1516 END IF;
1517
1518 END IF;
1519
1520 ELSE
1521
1522 -- LPN has just the item requested
1523 -- See if quantity/details it has will match the quantity allocated
1524 -- Find out if the item is lot/serial controlled and UOM of item
1525 -- and compare with transaction details
1526
1527 IF (l_debug = 1) THEN
1528 trace('lpn_match: lpn has only the requested item ');
1529 END IF;
1530
1531 SELECT
1532 primary_quantity,
1533 transaction_uom
1534 INTO
1535 l_mmtt_qty,
1536 l_txn_uom
1537 FROM mtl_material_transactions_temp
1538 WHERE transaction_temp_id = p_temp_id;
1539
1540
1541 -- If item is lot controlled then validate the lots
1542
1543 IF l_lot_code > 1 THEN
1544
1545 IF (l_debug = 1) THEN
1546 trace('lpn_match: item is lot controlled' );
1547 END IF;
1548
1549 -- If item is also serial controlled and serial allocation is
1550 -- on then count the number of serials allocated which exist
1551 -- in the LPN.
1552 -- If the count is 0 then raise an error
1553
1554 IF p_is_sn_alloc = 'Y' AND p_action = 4 THEN
1555
1556 IF (l_debug = 1) THEN
1557 trace('lpn_match: SN control and SN allocation on');
1558 END IF;
1559
1560 SELECT COUNT(fm_serial_number)
1561 INTO l_serial_exist_cnt
1562 FROM
1563 mtl_serial_numbers_temp msnt,
1564 mtl_transaction_lots_temp mtlt
1565 WHERE mtlt.transaction_temp_id = p_temp_id
1566 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
1567 AND msnt.fm_serial_number IN
1568 ( SELECT serial_number
1569 FROM mtl_serial_numbers
1570 WHERE lpn_id = p_lpn
1571 AND inventory_item_id = p_item_id
1572 AND Nvl(revision, '-999') = Nvl(p_rev, '-999')
1573 );
1574
1575 IF (l_debug = 1) THEN
1576 trace('lpn_match: SN exist count'||l_serial_exist_cnt);
1577 END IF;
1578
1579 IF ( l_serial_exist_cnt = 0 ) THEN
1580
1581 IF (l_debug = 1) THEN
1582 trace('lpn_match: No serial allocations have occured or LPN does not have the allocated serials ');
1583 END IF;
1584 -- Serial numbers missing for the transaction
1585 x_match := 9;
1586 FND_MESSAGE.SET_NAME('INV','INV_INT_SERMISEXP');
1587 FND_MSG_PUB.ADD;
1588 RAISE FND_API.G_EXC_ERROR;
1589
1590 END IF;
1591
1592 END IF;
1593
1594 -- Check whether the Lots allocated are all in the LPN
1595 -- An LPN can have many lots and items/revisions, check if the
1596 -- lots allocated for the item exist in the LPN and if any of
1597 -- them has quantity less/more than what was suggested.
1598
1599 IF (l_debug = 1) THEN
1600 trace( 'lpn_match: Check whether the LPN has any lot whose quantity exceeds allocated quantity');
1601 END IF;
1602 l_lpn_qty := 0;
1603
1604 OPEN lot_csr;
1605 LOOP
1606 FETCH lot_csr
1607 INTO
1608 l_mtlt_primary_qty,
1609 l_mtlt_lot_number;
1610
1611 EXIT WHEN lot_csr%notfound;
1612
1613 l_lot_match := 0;
1614 IF (l_debug = 1) THEN
1615 trace('lpn_match: l_mtlt_lot_number : ' || l_mtlt_lot_number);
1616 trace('lpn_match: l_mtlt_primary_qty: ' || l_mtlt_primary_qty);
1617 END IF;
1618
1619
1620 l_lot_cnt := l_lot_cnt - 1;
1621
1622
1623 IF nvl(l_allocated_lpn_id, 0) = p_lpn THEN
1624 --from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
1625 -- in order to get correct att.
1626 inv_quantity_tree_pub.update_quantities
1627 ( p_api_version_number => 1.0
1628 , p_init_msg_lst => fnd_api.g_false
1629 , x_return_status => l_return_status
1630 , x_msg_count => l_msg_cnt
1631 , x_msg_data => l_msg_data
1632 , p_organization_id => p_org_id
1633 , p_inventory_item_id => p_item_id
1634 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1635 , p_is_revision_control => b_is_revision_control
1636 , p_is_lot_control => TRUE
1637 , p_is_serial_control => b_is_serial_control
1638 , p_revision => nvl(p_rev, NULL)
1639 , p_lot_number => l_mtlt_lot_number
1640 , p_subinventory_code => l_sub
1641 , p_locator_id => l_loc_id
1642 , p_primary_quantity => -l_mtlt_primary_qty
1643 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1644 , x_qoh => l_qoh
1645 , x_rqoh => l_rqoh
1646 , x_qr => l_qr
1647 , x_qs => l_qs
1648 , x_att => l_att
1649 , x_atr => l_atr
1650 , p_lpn_id => p_lpn
1651 , p_transfer_subinventory_code => l_xfr_sub_code
1652 );
1653 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1654 IF (l_debug = 1) THEN
1655 trace('lpn_match: update qty tree 3rd time for lpn l_att:'||l_att||' for lot:'||l_mtlt_lot_number);
1656 END IF;
1657 ELSE
1658 IF (l_debug = 1) THEN
1659 trace('lpn_match: calling update qty tree with lpn 3rd time failed ');
1660 END IF;
1661 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1662 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1663 FND_MSG_PUB.ADD;
1664 RAISE FND_API.G_EXC_ERROR;
1665 END IF;
1666 ELSE
1667 inv_quantity_tree_pub.update_quantities
1668 ( p_api_version_number => 1.0
1669 , p_init_msg_lst => fnd_api.g_false
1670 , x_return_status => l_return_status
1671 , x_msg_count => l_msg_cnt
1672 , x_msg_data => l_msg_data
1673 , p_organization_id => p_org_id
1674 , p_inventory_item_id => p_item_id
1675 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1676 , p_is_revision_control => b_is_revision_control
1677 , p_is_lot_control => TRUE
1678 , p_is_serial_control => b_is_serial_control
1679 , p_revision => nvl(p_rev, NULL)
1680 , p_lot_number => l_mtlt_lot_number
1681 , p_subinventory_code => l_sub
1682 , p_locator_id => l_loc_id
1683 , p_primary_quantity => -l_mtlt_primary_qty
1684 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1685 , x_qoh => l_qoh
1686 , x_rqoh => l_rqoh
1687 , x_qr => l_qr
1688 , x_qs => l_qs
1689 , x_att => l_att
1690 , x_atr => l_atr
1691 -- , p_lpn_id => p_lpn withour lpn_id, only to locator level
1692 , p_transfer_subinventory_code => l_xfr_sub_code
1693 );
1694 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1695 IF (l_debug = 1) THEN
1696 trace('lpn_match: after update without lpn 3rd time l_att:'|| l_att||' for lot:'||l_mtlt_lot_number);
1697 END IF;
1698 ELSE
1699 IF (l_debug = 1) THEN
1700 trace('lpn_match: calling update qty tree back 3rd time without lpn 3rd time failed ');
1701 END IF;
1702 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1703 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1704 FND_MSG_PUB.ADD;
1705 RAISE FND_API.G_EXC_ERROR;
1706 END IF;
1707
1708 END IF;
1709
1710 inv_quantity_tree_pub.query_quantities
1711 ( p_api_version_number => 1.0
1712 , p_init_msg_lst => fnd_api.g_false
1713 , x_return_status => l_return_status
1714 , x_msg_count => l_msg_cnt
1715 , x_msg_data => l_msg_data
1716 , p_organization_id => p_org_id
1717 , p_inventory_item_id => p_item_id
1718 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1719 , p_is_revision_control => b_is_revision_control
1720 , p_is_lot_control => TRUE
1721 , p_is_serial_control => b_is_serial_control
1722 , p_demand_source_type_id => -9999
1723 , p_revision => nvl(p_rev, NULL)
1724 , p_lot_number => l_mtlt_lot_number
1725 , p_subinventory_code => l_sub
1726 , p_locator_id => l_loc_id
1727 , x_qoh => l_qoh
1728 , x_rqoh => l_rqoh
1729 , x_qr => l_qr
1730 , x_qs => l_qs
1731 , x_att => l_att
1732 , x_atr => l_atr
1733 , p_lpn_id => p_lpn
1734 , p_transfer_subinventory_code => l_xfr_sub_code
1735 );
1736
1737 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1738
1739 l_lot_match := 1;
1740
1741 IF (l_att > 0) THEN
1742
1743 l_table_index := l_table_index + 1;
1744
1745 IF (l_mtlt_primary_qty >= l_att) THEN
1746
1747 l_lpn_qty := l_lpn_qty + l_att;
1748
1749 IF (l_debug = 1) THEN
1750 trace('lpn_match: l_table_index:'||l_table_index||' lot_number:'||l_mtlt_lot_number||' qty:'||l_att);
1751 END IF;
1752 t_lpn_lot_qty_table(l_table_index).lpn_id := p_lpn;
1753 t_lpn_lot_qty_table(l_table_index).lot_number := l_mtlt_lot_number;
1754 t_lpn_lot_qty_table(l_table_index).qty := l_att;
1755
1756 ELSE
1757
1758 l_lpn_qty := l_lpn_qty + l_mtlt_primary_qty;
1759
1760 IF (l_debug = 1) THEN
1761 trace('lpn_match: l_table_index:'||l_table_index||' lot_number:'||l_mtlt_lot_number||' qty:'||l_mtlt_primary_qty);
1762 END IF;
1763 t_lpn_lot_qty_table(l_table_index).lpn_id := p_lpn;
1764 t_lpn_lot_qty_table(l_table_index).lot_number := l_mtlt_lot_number;
1765 t_lpn_lot_qty_table(l_table_index).qty := l_mtlt_primary_qty;
1766
1767 END IF;
1768
1769 ELSE
1770
1771 IF (l_debug = 1) THEN
1772 trace('lpn_match: LPN does not have lot ' || l_mtlt_lot_number);
1773 END IF;
1774
1775 /*trace('lpn_match: l_table_index:'||l_table_index||' lot_number:'||l_mtlt_lot_number||' qty:0');
1776 t_lpn_lot_qty_table(l_table_index).lpn_id := p_lpn;
1777 t_lpn_lot_qty_table(l_table_index).lot_number := l_mtlt_lot_number;
1778 t_lpn_lot_qty_table(l_table_index).qty := 0; */
1779
1780 IF x_match <> 4 THEN
1781
1782 x_match := 3;
1783
1784 END IF;
1785
1786 l_lot_match := 0;
1787
1788 l_lot_cnt := l_lot_cnt + 1;
1789
1790
1791 END IF;
1792
1793 ELSE
1794
1795 IF (l_debug = 1) THEN
1796 trace('lpn_match: calling qty tree 3rd time failed ');
1797 END IF;
1798 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1799 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1800 FND_MSG_PUB.add;
1801 RAISE FND_API.G_EXC_ERROR;
1802
1803 END IF;
1804
1805 IF l_lot_match <> 0 AND x_match <> 4 THEN
1806
1807 IF l_mtlt_primary_qty < l_att THEN
1808
1809 IF (l_debug = 1) THEN
1810 trace('lpn_match: Qty in LPN for lot ' || l_mtlt_lot_number || ' more than transaction qty for that lot');
1811 END IF;
1812 x_match := 4;
1813
1814 ELSIF l_mtlt_primary_qty > l_att THEN
1815 if l_qoh = l_att then
1816 IF (l_debug = 1) THEN
1817 trace('lpn_match: Qty in LPN for lot ' || l_mtlt_lot_number || ' less than transaction qty for that lot');
1818 END IF;
1819 x_match := 3;
1820 else
1821 IF (l_debug = 1) THEN
1822 trace('lpn_match: Qty in LPN for lot ' || l_mtlt_lot_number || ' less than transaction qty for that lot and lpn is for multiple task');
1823 END IF;
1824 x_match := 4;
1825 end if;
1826
1827 ELSE
1828
1829 IF x_match <> 3 THEN
1830
1831 IF (l_debug = 1) THEN
1832 trace('lpn_match: qty in LPN for lot ' || l_mtlt_lot_number || ' equal to transaction qty for that lot');
1833 END IF;
1834 if l_qoh = l_att then
1835 IF (l_debug = 1) THEN
1836 trace('lpn_match: lpn qoh is equal to att. Exact match');
1837 END IF;
1838 x_match := 1;
1839 else
1840 IF (l_debug = 1) THEN
1841 trace('lpn_match: lpn qoh is great than att. part of lpn is match');
1842 END IF;
1843 x_match := 4;
1844 end if;
1845 END IF;
1846
1847 END IF;
1848
1849 END IF;
1850
1851 IF nvl(l_allocated_lpn_id, 0) = p_lpn THEN
1852 --from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
1853 -- in order to get correct att.
1854 inv_quantity_tree_pub.update_quantities
1855 ( p_api_version_number => 1.0
1856 , p_init_msg_lst => fnd_api.g_false
1857 , x_return_status => l_return_status
1858 , x_msg_count => l_msg_cnt
1859 , x_msg_data => l_msg_data
1860 , p_organization_id => p_org_id
1861 , p_inventory_item_id => p_item_id
1862 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1863 , p_is_revision_control => b_is_revision_control
1864 , p_is_lot_control => TRUE
1865 , p_is_serial_control => b_is_serial_control
1866 , p_revision => nvl(p_rev, NULL)
1867 , p_lot_number => l_mtlt_lot_number
1868 , p_subinventory_code => l_sub
1869 , p_locator_id => l_loc_id
1870 , p_primary_quantity => l_mtlt_primary_qty
1871 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1872 , x_qoh => l_qoh
1873 , x_rqoh => l_rqoh
1874 , x_qr => l_qr
1875 , x_qs => l_qs
1876 , x_att => l_att
1877 , x_atr => l_atr
1878 , p_lpn_id => p_lpn
1879 , p_transfer_subinventory_code => l_xfr_sub_code
1880 );
1881 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1882 IF (l_debug = 1) THEN
1883 trace('lpn_match: update qty tree back 3rd time for lpn l_att:'||l_att||' for lot:'||l_mtlt_lot_number);
1884 END IF;
1885 ELSE
1886 IF (l_debug = 1) THEN
1887 trace('lpn_match: calling update qty tree with lpn 3rd time failed ');
1888 END IF;
1889 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1890 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1891 FND_MSG_PUB.ADD;
1892 RAISE FND_API.G_EXC_ERROR;
1893 END IF;
1894 ELSE
1895 inv_quantity_tree_pub.update_quantities
1896 ( p_api_version_number => 1.0
1897 , p_init_msg_lst => fnd_api.g_false
1898 , x_return_status => l_return_status
1899 , x_msg_count => l_msg_cnt
1900 , x_msg_data => l_msg_data
1901 , p_organization_id => p_org_id
1902 , p_inventory_item_id => p_item_id
1903 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
1904 , p_is_revision_control => b_is_revision_control
1905 , p_is_lot_control => TRUE
1906 , p_is_serial_control => b_is_serial_control
1907 , p_revision => nvl(p_rev, NULL)
1908 , p_lot_number => l_mtlt_lot_number
1909 , p_subinventory_code => l_sub
1910 , p_locator_id => l_loc_id
1911 , p_primary_quantity => l_mtlt_primary_qty
1912 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1913 , x_qoh => l_qoh
1914 , x_rqoh => l_rqoh
1915 , x_qr => l_qr
1916 , x_qs => l_qs
1917 , x_att => l_att
1918 , x_atr => l_atr
1919 -- , p_lpn_id => p_lpn withour lpn_id, only to locator level
1920 , p_transfer_subinventory_code => l_xfr_sub_code
1921 );
1922 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1923 IF (l_debug = 1) THEN
1924 trace('lpn_match: after update qty tree back without lpn 3rd time l_att:'|| l_att||' for lot:'||l_mtlt_lot_number);
1925 END IF;
1926 ELSE
1927 IF (l_debug = 1) THEN
1928 trace('lpn_match: calling update qty tree back without lpn 3rd time failed ');
1929 END IF;
1930 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1931 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1932 FND_MSG_PUB.ADD;
1933 RAISE FND_API.G_EXC_ERROR;
1934 END IF;
1935
1936 END IF;
1937
1938 END LOOP;
1939 CLOSE lot_csr;
1940
1941 IF l_lot_cnt > 0 THEN
1942
1943 x_match := 4;
1944
1945 END IF;
1946
1947 -- Now that all the lots have been validated, check whether the serial
1948 -- numbers allocated match the ones in the lpn.
1949
1950 IF p_is_sn_alloc = 'Y' AND p_action = 4 AND ( x_match = 1 OR x_match = 3 ) THEN
1951
1952 SELECT COUNT(fm_serial_number)
1953 INTO l_total_serial_cnt
1954 FROM mtl_serial_numbers_temp msnt,
1955 mtl_transaction_lots_temp mtlt
1956 WHERE mtlt.transaction_temp_id = p_temp_id
1957 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
1958
1959 IF (l_debug = 1) THEN
1960 trace('lpn_match: SN tot count'||l_total_serial_cnt);
1961 END IF;
1962 IF ( l_total_serial_cnt = l_serial_exist_cnt ) THEN
1963
1964 IF (l_debug = 1) THEN
1965 trace('lpn_match: LPN matches exactly');
1966 END IF;
1967 x_match := 1;
1968
1969 ELSIF ( l_total_serial_cnt > l_serial_exist_cnt ) THEN
1970
1971 IF (l_debug = 1) THEN
1972 trace('lpn_match: LPN has less');
1973 END IF;
1974 x_match := 3;
1975
1976 ELSE
1977
1978 IF (l_debug = 1) THEN
1979 trace('lpn_match: LPN has extra serials');
1980 END IF;
1981 x_match := 4;
1982
1983 END IF;
1984
1985 END IF;
1986
1987
1988 ELSE -- Item is not lot controlled
1989
1990 IF (l_debug = 1) THEN
1991 trace('lpn_match: Not Lot controlled ..');
1992 END IF;
1993 -- Check serial numbers if serial controlled and serial
1994 -- allocation is turned on
1995
1996 IF p_is_sn_alloc = 'Y' AND p_action = 4 THEN
1997
1998 IF (l_debug = 1) THEN
1999 trace('lpn_match: SN control and SN allocation on');
2000 END IF;
2001
2002 SELECT COUNT(fm_serial_number)
2003 INTO l_serial_exist_cnt
2004 FROM mtl_serial_numbers_temp msnt
2005 WHERE msnt.transaction_temp_id = p_temp_id
2006 AND msnt.fm_serial_number IN
2007 ( SELECT serial_number
2008 FROM mtl_serial_numbers
2009 WHERE lpn_id = p_lpn
2010 AND inventory_item_id = p_item_id
2011 AND Nvl(revision, '-999') = Nvl(p_rev, '-999')
2012 );
2013
2014 IF (l_debug = 1) THEN
2015 trace('lpn_match: SN exist count'||l_serial_exist_cnt);
2016 END IF;
2017
2018 IF ( l_serial_exist_cnt = 0 ) THEN
2019 IF (l_debug = 1) THEN
2020 trace('lpn_match: LPN does not have the allocated serials ');
2021 END IF;
2022 -- Serial numbers missing for the transaction
2023 x_match := 9;
2024 FND_MESSAGE.SET_NAME('INV','INV_INT_SERMISEXP');
2025 FND_MSG_PUB.ADD;
2026 RAISE FND_API.G_EXC_ERROR;
2027 END IF;
2028
2029
2030 END IF;
2031
2032 -- Get qty
2033 IF (l_debug = 1) THEN
2034 trace('lpn_match: get lpn quantity ');
2035 END IF;
2036
2037
2038 IF nvl(l_allocated_lpn_id, 0) = p_lpn THEN
2039 --from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
2040 -- in order to get correct att.
2041 inv_quantity_tree_pub.update_quantities
2042 ( p_api_version_number => 1.0
2043 , p_init_msg_lst => fnd_api.g_false
2044 , x_return_status => l_return_status
2045 , x_msg_count => l_msg_cnt
2046 , x_msg_data => l_msg_data
2047 , p_organization_id => p_org_id
2048 , p_inventory_item_id => p_item_id
2049 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
2050 , p_is_revision_control => b_is_revision_control
2051 , p_is_lot_control => FALSE
2052 , p_is_serial_control => b_is_serial_control
2053 , p_revision => nvl(p_rev, NULL)
2054 , p_lot_number => null
2055 , p_subinventory_code => l_sub
2056 , p_locator_id => l_loc_id
2057 , p_primary_quantity => -l_mmtt_qty
2058 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
2059 , x_qoh => l_qoh
2060 , x_rqoh => l_rqoh
2061 , x_qr => l_qr
2062 , x_qs => l_qs
2063 , x_att => l_att
2064 , x_atr => l_atr
2065 , p_lpn_id => p_lpn
2066 , p_transfer_subinventory_code => l_xfr_sub_code
2067 );
2068 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
2069 IF (l_debug = 1) THEN
2070 trace('lpn_match: update qty tree with lpn 4th time: l_att:' || l_att);
2071 END IF;
2072 ELSE
2073 IF (l_debug = 1) THEN
2074 trace('lpn_match: calling update qty tree with lpn 4th time failed ');
2075 END IF;
2076 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
2077 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
2078 FND_MSG_PUB.ADD;
2079 RAISE FND_API.G_EXC_ERROR;
2080 END IF;
2081 ELSE
2082 inv_quantity_tree_pub.update_quantities
2083 ( p_api_version_number => 1.0
2084 , p_init_msg_lst => fnd_api.g_false
2085 , x_return_status => l_return_status
2086 , x_msg_count => l_msg_cnt
2087 , x_msg_data => l_msg_data
2088 , p_organization_id => p_org_id
2089 , p_inventory_item_id => p_item_id
2090 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
2091 , p_is_revision_control => b_is_revision_control
2092 , p_is_lot_control => FALSE
2093 , p_is_serial_control => b_is_serial_control
2094 , p_revision => nvl(p_rev, NULL)
2095 , p_lot_number => null
2096 , p_subinventory_code => l_sub
2097 , p_locator_id => l_loc_id
2098 , p_primary_quantity => -l_mmtt_qty
2099 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
2100 , x_qoh => l_qoh
2101 , x_rqoh => l_rqoh
2102 , x_qr => l_qr
2103 , x_qs => l_qs
2104 , x_att => l_att
2105 , x_atr => l_atr
2106 -- , p_lpn_id => p_lpn withour lpn_id, only to locator level
2107 , p_transfer_subinventory_code => l_xfr_sub_code
2108 );
2109 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
2110 IF (l_debug = 1) THEN
2111 trace('lpn_match: update qty tree without lpn 4th time:l_att:'||l_att);
2112 END IF;
2113 ELSE
2114 IF (l_debug = 1) THEN
2115 trace('lpn_match: calling update qty tree without lpn 4th time failed ');
2116 END IF;
2117 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
2118 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
2119 FND_MSG_PUB.ADD;
2120 RAISE FND_API.G_EXC_ERROR;
2121 END IF;
2122
2123 END IF;
2124
2125 inv_quantity_tree_pub.query_quantities
2126 ( p_api_version_number => 1.0
2127 , p_init_msg_lst => fnd_api.g_false
2128 , x_return_status => l_return_status
2129 , x_msg_count => l_msg_cnt
2130 , x_msg_data => l_msg_data
2131 , p_organization_id => p_org_id
2132 , p_inventory_item_id => p_item_id
2133 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
2134 , p_is_revision_control => b_is_revision_control
2135 , p_is_lot_control => FALSE
2136 , p_is_serial_control => b_is_serial_control
2137 , p_demand_source_type_id => -9999
2138 , p_revision => nvl(p_rev, NULL)
2139 , p_lot_number => NULL
2140 , p_subinventory_code => l_sub
2141 , p_locator_id => l_loc_id
2142 , x_qoh => l_qoh
2143 , x_rqoh => l_rqoh
2144 , x_qr => l_qr
2145 , x_qs => l_qs
2146 , x_att => l_att
2147 , x_atr => l_atr
2148 , p_lpn_id => p_lpn
2149 , p_transfer_subinventory_code => l_xfr_sub_code
2150 );
2151
2152 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
2153
2154 IF (l_debug = 1) THEN
2155 trace('lpn_match: lpn quantity = ' || l_att );
2156 END IF;
2157
2158 IF l_mmtt_qty = l_att THEN
2159 if l_qoh = l_att then
2160 -- LPN is a match!
2161 IF (l_debug = 1) THEN
2162 trace('lpn_match: LPN matched');
2163 END IF;
2164 x_match := 1;
2165 else
2166 -- LPN is for multiple task
2167 IF (l_debug = 1) THEN
2168 trace('lpn_match: LPN has multiple task.');
2169 END IF;
2170 x_match := 4;
2171 end if;
2172
2173 ELSIF l_mmtt_qty > l_att THEN
2174 if l_qoh = l_att then
2175 IF (l_debug = 1) THEN
2176 trace('lpn_match: lpn has less requested qty and lpn is whole allocation');
2177 END IF;
2178 x_match := 3;
2179 else
2180 IF (l_debug = 1) THEN
2181 trace('lpn_match: lpn has less than requested qty and lpn is partial allocation');
2182 END IF;
2183 x_match := 4;
2184 end if;
2185 l_lpn_qty := l_att;
2186 ELSE
2187
2188 x_match := 4;
2189
2190 END IF;
2191
2192 ELSE
2193
2194 IF (l_debug = 1) THEN
2195 trace('lpn_match: calling qty tree 4th time failed');
2196 END IF;
2197 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
2198 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
2199 FND_MSG_PUB.add;
2200 RAISE FND_API.G_EXC_ERROR;
2201
2202 END IF;
2203
2204 IF nvl(l_allocated_lpn_id, 0) = p_lpn THEN
2205 --from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
2206 -- in order to get correct att.
2207 inv_quantity_tree_pub.update_quantities
2208 ( p_api_version_number => 1.0
2209 , p_init_msg_lst => fnd_api.g_false
2210 , x_return_status => l_return_status
2211 , x_msg_count => l_msg_cnt
2212 , x_msg_data => l_msg_data
2213 , p_organization_id => p_org_id
2214 , p_inventory_item_id => p_item_id
2215 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
2216 , p_is_revision_control => b_is_revision_control
2217 , p_is_lot_control => FALSE
2218 , p_is_serial_control => b_is_serial_control
2219 , p_revision => nvl(p_rev, NULL)
2220 , p_lot_number => null
2221 , p_subinventory_code => l_sub
2222 , p_locator_id => l_loc_id
2223 , p_primary_quantity => l_mmtt_qty
2224 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
2225 , x_qoh => l_qoh
2226 , x_rqoh => l_rqoh
2227 , x_qr => l_qr
2228 , x_qs => l_qs
2229 , x_att => l_att
2230 , x_atr => l_atr
2231 , p_lpn_id => p_lpn
2232 , p_transfer_subinventory_code => l_xfr_sub_code
2233 );
2234 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
2235 IF (l_debug = 1) THEN
2236 trace('lpn_match: update qty tree back with lpn 4th time: l_att:' || l_att);
2237 END IF;
2238 ELSE
2239 IF (l_debug = 1) THEN
2240 trace('lpn_match: calling update qty tree back with lpn 4th time failed ');
2241 END IF;
2242 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
2243 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
2244 FND_MSG_PUB.ADD;
2245 RAISE FND_API.G_EXC_ERROR;
2246 END IF;
2247 ELSE
2248 inv_quantity_tree_pub.update_quantities
2249 ( p_api_version_number => 1.0
2250 , p_init_msg_lst => fnd_api.g_false
2251 , x_return_status => l_return_status
2252 , x_msg_count => l_msg_cnt
2253 , x_msg_data => l_msg_data
2254 , p_organization_id => p_org_id
2255 , p_inventory_item_id => p_item_id
2256 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
2257 , p_is_revision_control => b_is_revision_control
2258 , p_is_lot_control => FALSE
2259 , p_is_serial_control => b_is_serial_control
2260 , p_revision => nvl(p_rev, NULL)
2261 , p_lot_number => null
2262 , p_subinventory_code => l_sub
2263 , p_locator_id => l_loc_id
2264 , p_primary_quantity => l_mmtt_qty
2265 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
2266 , x_qoh => l_qoh
2267 , x_rqoh => l_rqoh
2268 , x_qr => l_qr
2269 , x_qs => l_qs
2270 , x_att => l_att
2271 , x_atr => l_atr
2272 -- , p_lpn_id => p_lpn withour lpn_id, only to locator level
2273 , p_transfer_subinventory_code => l_xfr_sub_code
2274 );
2275 IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
2276 IF (l_debug = 1) THEN
2277 trace('lpn_match: update qty tree back without lpn 4th time:l_att:'||l_att);
2278 END IF;
2279 ELSE
2280 IF (l_debug = 1) THEN
2281 trace('lpn_match: calling update qty tree back without lpn 4th time failed ');
2282 END IF;
2283 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
2284 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
2285 FND_MSG_PUB.ADD;
2286 RAISE FND_API.G_EXC_ERROR;
2287 END IF;
2288
2289 END IF;
2290
2291 -- If the LPN quantity exactly matches/ has less than, the requested
2292 -- quantity then match the serial numbers also
2293
2294 IF p_is_sn_alloc = 'Y' AND p_action = 4 AND ( x_match = 1 OR x_match = 3 ) THEN
2295
2296 SELECT
2297 COUNT(fm_serial_number)
2298 INTO
2299 l_total_serial_cnt
2300 FROM
2301 mtl_serial_numbers_temp msnt
2302 WHERE msnt.transaction_temp_id=p_temp_id;
2303 IF (l_debug = 1) THEN
2304 trace('lpn_match: SN tot count'||l_total_serial_cnt);
2305 END IF;
2306
2307 IF (l_total_serial_cnt = l_serial_exist_cnt) THEN
2308
2309 IF (l_debug = 1) THEN
2310 trace('lpn_match: LPN matches exactly');
2311 END IF;
2312 x_match := 1;
2313
2314 ELSIF(l_total_serial_cnt > l_serial_exist_cnt) THEN
2315
2316 IF (l_debug = 1) THEN
2317 trace('lpn_match: LPN has less');
2318 END IF;
2319 x_match := 3;
2320 l_lpn_qty := l_serial_exist_cnt;
2321
2322 ELSE
2323
2324 IF (l_debug = 1) THEN
2325 trace('lpn_match: LPN has extra serials');
2326 END IF;
2327 x_match := 4;
2328
2329 END IF;
2330
2331 END IF;
2332
2333 IF (l_debug = 1) THEN
2334 trace('lpn_match: After 4');
2335 END IF;
2336
2337 END IF; -- lot control check
2338
2339 END IF; -- lpn has only one item
2340
2341
2342 IF x_match = 1 OR x_match = 3 THEN
2343
2344 IF p_action = 4 THEN
2345
2346 -- serial controlled - CHECK serial status
2347 IF (l_debug = 1) THEN
2348 trace('lpn_match: x_match is '||x_match||' and item is serial controlled ');
2349 END IF;
2350
2351 OPEN ser_csr;
2352 LOOP
2353
2354 FETCH ser_csr into l_serial_number;
2355
2356 EXIT WHEN ser_csr%NOTFOUND;
2357
2358 IF inv_material_status_grp.is_status_applicable
2359 (p_wms_installed => p_wms_installed,
2360 p_trx_status_enabled => NULL,
2361 p_trx_type_id => p_transaction_type_id,
2362 p_lot_status_enabled => NULL,
2363 p_serial_status_enabled => NULL,
2364 p_organization_id => p_org_id,
2365 p_inventory_item_id => p_item_id,
2366 p_sub_code => x_sub,
2367 p_locator_id => NULL,
2368 p_lot_number => p_lot,
2369 p_serial_number => l_serial_number,
2370 p_object_type => 'A') = 'N'
2371
2372 THEN
2373
2374 IF (l_debug = 1) THEN
2375 trace('lpn_match: After 6');
2376 END IF;
2377 x_match := 11;
2378 CLOSE ser_csr;
2379 FND_MESSAGE.SET_NAME('INV','INV_SER_STATUS_NA');
2380 FND_MESSAGE.SET_TOKEN('TOKEN',l_serial_number);
2381 FND_MSG_PUB.ADD;
2382 RAISE FND_API.G_EXC_ERROR;
2383
2384 END IF;
2385
2386 END LOOP;
2387 CLOSE ser_csr;
2388
2389 ELSE
2390
2391 l_serial_number := NULL;
2392 -- Check whether the LPN status is applicable for this transaction
2393 IF inv_material_status_grp.is_status_applicable
2394 (p_wms_installed => p_wms_installed,
2395 p_trx_status_enabled => NULL,
2396 p_trx_type_id => p_transaction_type_id,
2397 p_lot_status_enabled => NULL,
2398 p_serial_status_enabled => NULL,
2399 p_organization_id => p_org_id,
2400 p_inventory_item_id => p_item_id,
2401 p_sub_code => x_sub,
2402 p_locator_id => NULL,
2403 p_lot_number => p_lot,
2404 p_serial_number => l_serial_number,
2405 p_object_type => 'A') = 'N'
2406
2407 THEN
2408
2409 x_match := 8;
2410
2411 -- LPN status is invalid for this operation
2412
2413 FND_MESSAGE.SET_NAME('INV','INV_INVALID_LPN_STATUS');
2414 FND_MESSAGE.SET_TOKEN('TOKEN1',TO_CHAR(p_lpn));
2415 FND_MSG_PUB.ADD;
2416 RAISE FND_API.G_EXC_ERROR;
2417
2418 END IF;
2419
2420 END IF;
2421
2422 END IF;
2423
2424
2425 IF (l_debug = 1) THEN
2426 trace('lpn_match: x_match : ' || x_match);
2427 trace('lpn_match: p_is_sn_alloc : ' || p_is_sn_alloc);
2428 END IF;
2429
2430
2431 l_table_total := t_lpn_lot_qty_table.COUNT;
2432 if l_table_total > 0 then
2433 IF (l_debug = 1) THEN
2434 trace('lpn_match: building lpn lot vector for '||l_table_total||' records');
2435 END IF;
2436 for l_table_count IN 1..l_table_total LOOP
2437 IF (l_debug = 1) THEN
2438 trace('lpn_match: index is : '||l_table_count);
2439 END IF;
2440 x_lpn_lot_vector := x_lpn_lot_vector || t_lpn_lot_qty_table(l_table_count).lot_number
2441 || '@@@@@'
2442 || t_lpn_lot_qty_table(l_table_count).qty
2443 || '&&&&&';
2444
2445
2446 END LOOP;
2447
2448 else
2449 x_lpn_lot_vector := null;
2450 end if;
2451
2452 IF (l_debug = 1) THEN
2453 trace('lpn_match: LPN QTY '||l_lpn_qty);
2454 END IF;
2455
2456 x_temp_id := l_out_temp_id;
2457 x_qty := least(l_lpn_qty, p_qty);
2458 x_return_status := FND_API.G_RET_STS_SUCCESS;
2459
2460 IF (l_debug = 1) THEN
2461 trace('lpn_match: Match :'||x_match);
2462 trace('lpn_match: x_loc_id :' || x_loc_id);
2463 trace('lpn_match: x_lpn_lot_vector :'||x_lpn_lot_vector);
2464 trace('lpn_match: x_qty :'||x_qty );
2465 trace('lpn_match: x_return_status :'||x_return_status);
2466 trace('lpn_match: x_temp_id :'||x_temp_id);
2467 END IF;
2468
2469 EXCEPTION
2470
2471 WHEN FND_API.G_EXC_ERROR THEN
2472 IF (l_debug = 1) THEN
2473 trace('lpn_match: Exception raised');
2474 END IF;
2475 x_return_status := FND_API.G_RET_STS_ERROR;
2476 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
2477
2478 WHEN OTHERS THEN
2479 IF (l_debug = 1) THEN
2480 trace('lpn_match: Other exception raised : ' || Sqlerrm);
2481 END IF;
2482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2483 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
2484
2485 END get_lpn_match;
2486
2487
2488 -----------------------------------------------------
2489 -- retrieve all the records from wms_device_requests and process
2490 --
2491 ------------------------------------------------------
2492 PROCEDURE device_confirmation(
2493 x_return_status OUT NOCOPY VARCHAR2
2494 ,x_msg_count OUT NOCOPY NUMBER
2495 ,x_msg_data OUT NOCOPY VARCHAR2
2496 ,p_request_id IN NUMBER
2497 ,x_successful_row_cnt OUT nocopy number
2498 ) IS
2499
2500 l_conf_rec_cnt NUMBER;
2501 l_lpn_controlled_flag NUMBER;
2502 l_count NUMBER :=0;
2503 l_txn_quantity NUMBER;
2504 l_xfer_sub_code VARCHAR2(30);
2505 l_xfer_loc_id NUMBER;
2506 ll_xfer_sub_code VARCHAR2(30);
2507 ll_xfer_loc_id NUMBER;
2508 l_lpn_sub VARCHAR2(30);
2509 l_lpn_loc VARCHAR2(60);
2510 l_lot_code NUMBER;
2511 l_serial_code NUMBER;
2512 l_inventory_item_id NUMBER;
2513 l_parent_request_id NUMBER;
2514 l_mmtt_count NUMBER;
2515 l_move_order_line_id NUMBER;
2516 l_pr_qty NUMBER;
2517 ll_pr_qty NUMBER;
2518 l_txn_hdr_id NUMBER;
2519 l_txn_temp_id NUMBER;
2520 l_lot NUMBER;
2521 l_rev VARCHAR2(3);
2522 l_tran_source_type_id NUMBER;
2523 l_tran_action_id NUMBER;
2524 l_qty_discrepancy_flag NUMBER :=0;
2525 l_sub_discrepancy_flag NUMBER :=0;
2526 l_loc_discrepancy_flag NUMBER :=0;
2527 l_period_id NUMBER;
2528 l_open_past_period BOOLEAN;
2529 l_txn_ret NUMBER;
2530 l_lpn_context NUMBER;
2531 l_pick_lpn_context NUMBER;
2532 l_sub_code VARCHAR2(30);
2533 l_loc_id NUMBER;
2534 l_source_line_id NUMBER;
2535 l_wf NUMBER :=0;
2536 l_last_updated_by NUMBER;
2537 l_mmtt_txn_qty NUMBER;
2538 ll_mmtt_txn_qty NUMBER;
2539 l_primary_uom varchar2(3);
2540 l_primary_quantity NUMBER;
2541 l_transaction_uom VARCHAR2(3);
2542 l_lpn_match NUMBER;
2543 l_tran_type_id NUMBER;
2544 l_cost_group_id NUMBER;
2545 l_orig_lpn_id NUMBER;
2546 l_lpn_qty_pickable NUMBER :=0;
2547 x_lpn_lot_vector VARCHAR2(200);
2548 x_temp_id NUMBER;
2549 x_loc_id NUMBER;
2550 l_new_request_id NUMBER;
2551 l_mtlt_pr_qty NUMBER;
2552 l_xfer_lpn VARCHAR2(30);
2553 l_lpn WMS_CONTAINER_PUB.LPN;
2554 l_any_row_failed BOOLEAN := FALSE;
2555
2556 l_per_res_id NUMBER;
2557 l_wms_task_type NUMBER;
2558 l_std_op_id NUMBER;
2559 l_operation_plan_id NUMBER;
2560 l_person_id NUMBER;
2561 l_orig_txn_hdr_id NUMBER;
2562 l_org_id NUMBER;
2563
2564
2565 /*
2566 CURSOR c_fm_to_serial_number(l_txn_temp_id NUMBER) IS
2567 SELECT
2568 msnt.fm_serial_number,
2569 msnt.to_serial_number
2570 FROM mtl_serial_numbers_temp msnt
2571 WHERE msnt.transaction_temp_id = l_txn_temp_id;
2572
2573 CURSOR c_fm_to_lot_serial_number(l_txn_temp_id NUMBER) IS
2574 SELECT
2575 msnt.fm_serial_number,
2576 msnt.to_serial_number
2577 FROM
2578 mtl_serial_numbers_temp msnt,
2579 mtl_transaction_lots_temp mtlt
2580 WHERE mtlt.transaction_temp_id = l_txn_temp_id
2581 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
2582 */
2583
2584 CURSOR wdr_cursor IS
2585 SELECT relation_id -- parent_request_id
2586 , task_id
2587 , task_summary
2588 , business_event_id
2589 , transaction_quantity
2590 , transfer_sub_code
2591 , transfer_loc_id
2592 , lpn_id
2593 , xfer_lpn_id
2594 , device_status
2595 , reason_id
2596 , organization_id--Its NOT NULL Column
2597 FROM wms_device_requests
2598 WHERE business_event_id IN
2599 (wms_device_integration_pvt.wms_be_task_confirm, wms_device_integration_pvt.wms_be_load_confirm)
2600 AND device_status = 'S'
2601 AND task_summary = 'Y'
2602 ORDER BY xfer_lpn_id;
2603
2604 --this cursor will be used to upate mtlt for unpicked lots
2605 CURSOR c_mtlt_update (p_relation_id NUMBER, p_temp_id NUMBER) IS
2606 SELECT lot_number,lot_qty FROM wms_device_requests
2607 WHERE relation_id = p_relation_id
2608 AND task_id = p_temp_id
2609 AND task_summary = 'N'
2610 AND business_event_id IN
2611 (wms_device_integration_pvt.wms_be_task_confirm,
2612 wms_device_integration_pvt.wms_be_load_confirm);
2613
2614
2615 --Following c_update_xfer_lpn_context cursor is used to update LPN context to RESIDE_IN_INV after call to TM
2616 --1-For REPLENISHMENT TASKS
2617 --2-Only for LPNs that are being dropped
2618 --For SO Pick, TM handles it in post 11.5.10.
2619 --In case the LPN is going to the non-LPN controlled sub then TM would have already unpacked and updated the lpn context TO 'defined but not used'. Leave these LPN as it is.
2620
2621 CURSOR c_update_xfer_lpns_context IS
2622 SELECT wlpn.lpn_id,organization_id FROM wms_license_plate_numbers wlpn
2623 WHERE wlpn.lpn_context <> wms_container_pub.lpn_context_pregenerated
2624 --to avoid LPNS that have been unpacked by TM for non-LPN ctrld sub
2625 AND wlpn.lpn_id IN
2626 (SELECT wdr.xfer_lpn_id
2627 FROM wms_device_requests wdr,
2628 wms_device_requests_hist wdrh,
2629 wms_dispatched_tasks wdt
2630 WHERE wdr.business_event_id = wms_device_integration_pvt.WMS_BE_TASK_CONFIRM
2631 AND wdr.task_id = wdt.transaction_temp_id
2632 AND wdt.task_type IN (4,5,7) -- ONLY for Replenishment, MO Xfer,Staging TASKS
2633 AND wdr.status_code = 'S'
2634 AND wdr.device_status = 'S'
2635 AND wdr.task_summary = 'Y'
2636 AND wdr.task_summary = wdrh.task_summary
2637 and wdrh.request_id = wdr.relation_id
2638 AND wdr.task_id = wdrh.task_id
2639 AND wdr.transaction_quantity > 0
2640 AND wdrh.business_event_id IN (wms_device_integration_pvt.wms_be_pick_release,wms_device_integration_pvt.wms_be_wip_pick_release,wms_device_integration_pvt.wms_be_mo_task_alloc)
2641 AND wdrh.TASK_TYPE_ID = 1);
2642
2643
2644 --used to xfer records into WDTH
2645 CURSOR mmtt_csr IS
2646 SELECT transaction_temp_id, organization_id, transfer_lpn_id, content_lpn_id
2647 FROM mtl_material_transactions_temp mmtt
2648 WHERE mmtt.transaction_header_id = l_txn_hdr_id;
2649
2650
2651 --used to update the account period in mmtt
2652 CURSOR c_open_period_check IS SELECT distinct organization_id, task_id
2653 FROM wms_device_requests
2654 WHERE business_event_id = wms_device_integration_pvt.WMS_BE_TASK_CONFIRM
2655 AND status_code = 'S'
2656 AND device_status = 'S'
2657 AND task_summary = 'Y';
2658
2659
2660 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2661 BEGIN
2662 x_successful_row_cnt := 0;
2663 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2664
2665 SAVEPOINT WMS_DEVICE_REQUESTS_SP_OUTER;
2666
2667 --this header_id will be used to call TM for the batch
2668 SELECT mtl_material_transactions_s.NEXTVAL INTO l_txn_hdr_id FROM DUAL;
2669
2670 IF (l_debug = 1) THEN
2671 trace('Inside Device_Confirmation API:p_request_id'||p_request_id);
2672 END IF;
2673
2674 FOR l_rec IN wdr_cursor LOOP
2675 --FOR each record this savepoint wil be overwritten
2676 SAVEPOINT WMS_DEVICE_REQUESTS_SP;
2677 FND_MSG_PUB.initialize;
2678 --Check if device status
2679
2680 IF l_rec.device_status <> FND_API.g_ret_sts_success THEN
2681 IF (l_debug = 1) THEN
2682 trace('DEVICE REQUEST has its status as ERROR ');
2683 END IF;
2684 FND_MESSAGE.SET_NAME('WMS', 'WMS_ERR_DEVICE_STATUS');
2685 FND_MSG_PUB.ADD;
2686
2687 --update wdr for error_code and error_mesg
2688 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
2689 l_any_row_failed := TRUE;
2690 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
2691 GOTO continue_loop;
2692
2693 ELSE--device status is success
2694 IF l_rec.relation_id IS NULL OR l_rec.task_id IS NULL THEN
2695 IF (l_debug = 1) THEN
2696 trace('Error: parent_request_id or task_id is null for Task Confirm');
2697 END IF;
2698 FND_MESSAGE.SET_NAME('WMS', 'WMS_MISSING_TASK_INFO');
2699 FND_MSG_PUB.ADD;
2700
2701 --update wdr for error_code and error_mesg
2702 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
2703 l_any_row_failed := TRUE;
2704 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
2705 GOTO continue_loop;
2706
2707 ELSE
2708 IF (l_debug = 1) THEN
2709 trace('Processing Task: task_id:'||l_rec.task_id||':request_id :'||l_rec.relation_id);
2710 END IF;
2711 --Check if corresponding RECORD exist in the WDRH history table
2712
2713 BEGIN
2714 SELECT task_id,lpn_id INTO l_txn_temp_id,l_orig_lpn_id
2715 FROM wms_device_requests_hist
2716 WHERE request_id = l_rec.relation_id
2717 AND task_id = l_rec.task_id
2718 AND business_event_id IN (wms_device_integration_pvt.wms_be_pick_release,wms_device_integration_pvt.wms_be_wip_pick_release,wms_device_integration_pvt.wms_be_mo_task_alloc)
2719 AND ROWNUM <2;
2720 EXCEPTION
2721 WHEN no_data_found THEN
2722
2723 FND_MESSAGE.SET_NAME('WMS', 'WMS_MISSING_TASK_INFO');
2724 FND_MSG_PUB.ADD;
2725
2726 --update wdr for error_code and error_mesg
2727 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
2728 l_any_row_failed := TRUE;
2729 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
2730 GOTO continue_loop;
2731 END;
2732
2733
2734 --There are two records for a single mmtt record (after
2735 -- pick_release) IN wdrh one for pick and another for drop, hence
2736 --the condition AND ROWNUM <1 is required in above query
2737
2738 IF l_txn_temp_id IS NOT NULL THEN --means record exist
2739 IF (l_debug = 1) THEN
2740 trace('txn_temp_id is valid:'||l_txn_temp_id);
2741 END IF;
2742 --get remaining details from MMTT
2743 SELECT
2744 transaction_header_id,
2745 inventory_item_id,
2746 move_order_line_id,
2747 primary_quantity,
2748 transaction_quantity,
2749 transfer_subinventory,
2750 transfer_to_location,
2751 revision,
2752 transaction_source_type_id,
2753 transaction_action_id,
2754 subinventory_code,
2755 locator_id,
2756 last_updated_by,
2757 transaction_uom,
2758 transaction_type_id,
2759 cost_group_id
2760 INTO
2761 l_orig_txn_hdr_id,
2762 l_inventory_item_id,
2763 l_move_order_line_id,
2764 l_pr_qty,
2765 l_mmtt_txn_qty,
2766 l_xfer_sub_code,
2767 l_xfer_loc_id,
2768 l_rev,
2769 l_tran_source_type_id,
2770 l_tran_action_id,
2771 l_sub_code,
2772 l_loc_id,
2773 l_last_updated_by,
2774 l_transaction_uom,
2775 l_tran_type_id,
2776 l_cost_group_id
2777 FROM mtl_material_transactions_temp
2778 WHERE transaction_temp_id = l_txn_temp_id;
2779
2780 --Validate the passed quantity
2781 IF l_rec.transaction_quantity IS NOT NULL THEN --chances are that didn't choose suggestion
2782 IF (l_rec.transaction_quantity < l_mmtt_txn_qty) THEN
2783 l_qty_discrepancy_flag := 1;--so qty discrepancy
2784 ELSIF (l_rec.transaction_quantity > l_mmtt_txn_qty) THEN
2785 IF (l_debug = 1) THEN
2786 trace('Quantity is not valid in the record');
2787 END IF;
2788 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_QTY');
2789 FND_MSG_PUB.ADD;
2790
2791 --update wdr for error_code and mesg
2792 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
2793 l_any_row_failed := TRUE;
2794 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
2795 GOTO continue_loop;
2796 END IF;
2797 END IF;
2798 IF (l_debug = 1) THEN
2799 trace('Validate xfer Sub');
2800 END IF;
2801 --Validate drop off Subinventory
2802 IF l_rec.transfer_sub_code IS NOT NULL THEN --chances are that didn't choose suggestion
2803 IF l_rec.transfer_sub_code <> l_xfer_sub_code THEN--sub discrepancy
2804 l_sub_discrepancy_flag := 1;
2805 BEGIN
2806 SELECT
2807 msi.lpn_controlled_flag
2808 INTO
2809 l_lpn_controlled_flag
2810 FROM
2811 mtl_secondary_inventories msi
2812 WHERE msi.organization_id = l_rec.organization_id
2813 AND msi.secondary_inventory_name = l_rec.transfer_sub_code
2814 AND sysdate <= nvl(msi.disable_date,sysdate);
2815 EXCEPTION
2816 WHEN no_data_found THEN
2817 IF (l_debug = 1) THEN
2818 trace('Invalid Sub:This Subinventory does not exist');
2819 END IF;
2820 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SUB');
2821 FND_MSG_PUB.ADD;
2822
2823 --update wdr for error_code and mesg
2824 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
2825 l_any_row_failed := TRUE;
2826 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
2827 GOTO continue_loop;
2828 END;
2829 IF (l_lpn_controlled_flag IS NULL) OR (l_lpn_controlled_flag <> wms_globals.g_lpn_controlled_sub) THEN
2830 IF (l_debug = 1) THEN
2831 trace('Invalid Sub:SUB is not LPN Controlled');
2832 END IF;
2833 FND_MESSAGE.SET_NAME('WMS', 'WMS_SUB_NOLPN_CTRLD');
2834 FND_MSG_PUB.ADD;
2835 --update wdr for error_code and mesg
2836 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
2837 l_any_row_failed := TRUE;
2838 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
2839 GOTO continue_loop;
2840 END IF;
2841 END IF;
2842 END IF;
2843
2844 --Validate drop off Locator
2845 IF (l_debug = 1) THEN
2846 trace('Validate xfer Loc');
2847 END IF;
2848 IF l_rec.transfer_loc_id IS NOT NULL THEN --chances are that didn't choose suggestion
2849 IF l_rec.transfer_loc_id <> l_xfer_loc_id THEN--loc discrepancy
2850 l_loc_discrepancy_flag := 1;
2851
2852 SELECT 1 INTO l_count FROM DUAL WHERE exists
2853 ( SELECT 1
2854 FROM mtl_item_locations_kfv
2855 WHERE organization_id = l_rec.organization_id
2856 AND inventory_location_id = l_rec.transfer_loc_id
2857 AND sysdate < nvl(disable_date,sysdate+1)
2858 );
2859 IF (l_count = 0) THEN
2860 IF (l_debug = 1) THEN
2861 trace('Invalid Locator:Locator does not exist in this Sub/Org');
2862 END IF;
2863 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LOC');--done
2864 FND_MSG_PUB.ADD;
2865
2866 --update wdr for error_code and mesg
2867 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
2868 l_any_row_failed := TRUE;
2869 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
2870 GOTO continue_loop;
2871 END IF;
2872 END IF;
2873 END IF;
2874
2875 --Validate the picked lpn_id
2876 IF (l_debug = 1) THEN
2877 trace('passed lpn_id:'|| l_rec.lpn_id);
2878 END IF;
2879
2880 IF ((l_orig_lpn_id IS NOT NULL) AND (l_rec.lpn_id IS NOT NULL)
2881 AND (l_orig_lpn_id <> l_rec.lpn_id)) OR ((l_orig_lpn_id IS NOT NULL) AND (l_rec.lpn_id IS NULL)) THEN
2882
2883 --If we pass the LPN_id (which will appear in case LPN
2884 --allocation is turned on) in WDR the user must not pick
2885 --loose and record for task_confirmation should have
2886 --lpn_id populated, he might pick another LPN but from same
2887 --location, we will validate and process
2888
2889 --If we do not pass LPN_id, THEN item can be picked looses and
2890 --record for task_confirmation can have lpn_id as null
2891 -- OR valid lpn can be picked from SAME SUGGESTED LOCATOR
2892 --with lpn_id in WDR being populated
2893 IF (l_debug = 1) THEN
2894 trace('Error:Invalid Picked LPN:allocated LPN and picked LPNs do NOT match');
2895 END IF;
2896 FND_MESSAGE.SET_NAME('WMS', 'WMS_MISMATCH_LPN');
2897 FND_MSG_PUB.ADD;
2898
2899 --update wdr for error_code and error_mesg
2900 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
2901 l_any_row_failed := TRUE;
2902 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
2903 GOTO continue_loop;
2904
2905 ELSIF (l_rec.lpn_id IS NOT NULL) THEN
2906
2907
2908 IF (l_debug = 1) THEN
2909 trace('validate the lpn context OF picked lpn');
2910 END IF;
2911
2912 BEGIN
2913 SELECT lpn_context
2914 INTO l_pick_lpn_context
2915 FROM wms_license_plate_numbers WHERE
2916 lpn_id = l_rec.lpn_id
2917 AND organization_id = l_rec.organization_id;
2918
2919
2920 IF (l_debug = 1) THEN
2921 trace('picked LPN:'||l_rec.lpn_id||'::context::'||l_pick_lpn_context);
2922 END IF;
2923
2924 exception
2925 WHEN no_data_found THEN
2926 IF (l_debug = 1) THEN
2927 trace('Error:NO data found for picked lpn_id:'||l_rec.lpn_id);
2928 END IF;
2929 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
2930 FND_MSG_PUB.ADD;
2931
2932 --update wdr for error_code and error_mesg
2933 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
2934 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
2935 GOTO continue_loop;
2936 END;
2937
2938 IF l_pick_lpn_context NOT IN ( wms_container_pub.lpn_context_pregenerated,wms_container_pub.LPN_CONTEXT_INV) THEN
2939 IF (l_debug = 1) THEN
2940 trace('Invalid LPN context for picked LPN:'||l_rec.lpn_id||'::context::'||l_pick_lpn_context);
2941 END IF;
2942 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
2943 FND_MSG_PUB.ADD;
2944
2945 --update wdr for error_code and error_mesg
2946 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
2947 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
2948 GOTO continue_loop;
2949
2950 END IF;
2951
2952
2953 IF (l_debug = 1) THEN
2954 trace('Calling get LPN Match for picked LPN');
2955 END IF;
2956 --get information about picked LPN
2957 get_lpn_match (
2958 p_lpn => l_rec.lpn_id
2959 , p_org_id => l_rec.organization_id
2960 , p_item_id => l_inventory_item_id
2961 , p_rev => null
2962 , p_lot => null
2963 , p_qty => l_mmtt_txn_qty
2964 , p_uom => l_transaction_uom
2965 , x_match => l_lpn_match
2966 , x_sub => l_lpn_sub
2967 , x_loc => l_lpn_loc
2968 , x_qty => l_lpn_qty_pickable--qty that can be picked from LPN
2969 , x_return_status => x_return_status
2970 , x_msg_count => x_msg_count
2971 , x_msg_data => x_msg_data
2972 , p_temp_id => l_txn_temp_id
2973 , p_wms_installed => 'true'
2974 , p_transaction_type_id => l_tran_type_id
2975 , p_cost_group_id => l_cost_group_id
2976 , p_is_sn_alloc => 'Y' --we support only this
2977 , p_action => 0--We DO not support serial,needed to pass ; FOR serial:4
2978 , x_temp_id => x_temp_id --not used currently
2979 , x_loc_id => x_loc_id
2980 , x_lpn_lot_vector => x_lpn_lot_vector --not used currently
2981 );
2982
2983 IF (l_debug = 1) THEN
2984 trace('device_confirmation: returned from get_lpn_match');
2985 trace('device_confirmation: value of lpn_match'||l_lpn_match||':loc_id:'||x_loc_id);
2986 END IF;
2987
2988
2989 --put a check to see that the picked LPN is from the
2990 --suggested location otherwise error out
2991 IF l_loc_id <> x_loc_id THEN
2992 IF (l_debug = 1) THEN
2993 trace('Error:Unallowed LPN:substituted LPN must be picked from suggested Location');
2994 END IF;
2995 FND_MESSAGE.SET_NAME('WMS', 'WMS_SUBST_LPN_ERR');
2996 FND_MSG_PUB.ADD;
2997
2998 --update wdr for error_code and error_mesg
2999 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3000 l_any_row_failed := TRUE;
3001 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3002 GOTO continue_loop;
3003
3004 END IF;
3005
3006
3007 END IF;
3008
3009 IF (l_debug = 1) THEN
3010 trace('passed xfer_lpn_id:'||l_rec.xfer_lpn_id);
3011 END IF;
3012 --Validate the Xfer_lpn_id only if it is different than picked
3013 --LPN_id
3014
3015 IF (l_rec.xfer_lpn_id IS NOT NULL AND l_rec.lpn_id <> l_rec.xfer_lpn_id )
3016 OR (l_rec.xfer_lpn_id IS NOT NULL AND l_rec.lpn_id IS NULL) THEN --Mandatory field
3017
3018 BEGIN
3019 SELECT lpn_context,license_plate_number
3020 INTO l_lpn_context, l_xfer_lpn
3021 FROM wms_license_plate_numbers WHERE
3022 lpn_id = l_rec.xfer_lpn_id
3023 AND organization_id = l_rec.organization_id;
3024
3025 IF (l_debug = 1) THEN
3026 trace('xfer_LPN:'||l_rec.xfer_lpn_id||'::context::'||l_lpn_context);
3027 END IF;
3028
3029 EXCEPTION
3030 WHEN no_data_found THEN
3031 IF (l_debug = 1) THEN
3032 trace('Error:NO data found for xfer_lpn_id:'||l_rec.xfer_lpn_id);
3033 END IF;
3034 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
3035 FND_MSG_PUB.ADD;
3036
3037 --update wdr for error_code and error_mesg
3038 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3039 l_any_row_failed := TRUE;
3040 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3041 GOTO continue_loop;
3042 END;
3043
3044 IF l_lpn_context NOT IN (wms_container_pub.LPN_CONTEXT_PACKING,
3045 wms_container_pub.lpn_context_pregenerated,wms_container_pub.LPN_CONTEXT_INV) THEN
3046 IF (l_debug = 1) THEN
3047 trace('Invalid LPN context for xfer_LPN:'||l_rec.xfer_lpn_id||'::context::'||l_lpn_context);
3048 END IF;
3049 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
3050 FND_MSG_PUB.ADD;
3051
3052 --update wdr for error_code and error_mesg
3053 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3054 l_any_row_failed := TRUE;
3055 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3056 GOTO continue_loop;
3057
3058 END IF;
3059
3060
3061 --make sure that lines picked in the LPN have same
3062 --delivery_id, IF delivery_id is not stamped then it uses
3063 --mol.carton_grouping_id. It also makes sure that lines in
3064 --the lpn are going to the same xfer sub/loc
3065
3066
3067 IF (l_debug = 1) THEN
3068 trace('After calling validate_pick_to_lpn l_txn_temp_id :'||l_txn_temp_id);
3069 END IF;
3070
3071
3072 --this call will make sure that the lpn picked has same delivery
3073 wms_task_dispatch_gen.validate_pick_to_lpn
3074 (p_api_version_number => 1.0 ,
3075 x_return_status => x_return_status,
3076 x_msg_count => x_msg_count,
3077 x_msg_data => x_msg_data,
3078 p_organization_id => l_rec.organization_id,
3079 p_pick_to_lpn => l_xfer_lpn,
3080 p_temp_id => l_txn_temp_id);
3081
3082 IF (l_debug = 1) THEN
3083 trace('After calling validate_pick_to_lpn x_return_status:'||x_return_status);
3084 trace('x_msg_data :'||x_msg_data);
3085 END IF;
3086
3087
3088 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3089 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
3090 FND_MSG_PUB.ADD;
3091 --update wdr for error_code and error_mesg
3092 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3093 l_any_row_failed := TRUE;
3094 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3095 GOTO continue_loop;
3096
3097 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3098 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
3099 FND_MSG_PUB.ADD;
3100 --update wdr for error_code and error_mesg
3101 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3102 l_any_row_failed := TRUE;
3103 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3104 GOTO continue_loop;
3105 END IF;
3106
3107 ELSIF (l_rec.transaction_quantity <> 0 AND l_rec.lpn_id <>
3108 l_rec.xfer_lpn_id ) THEN --ERROR OUT
3109 --l_rec.transaction_quantity =0 no need for xfer_lpn_id
3110 IF (l_debug = 1) THEN
3111 trace('Error:No infomration about xfer_LPN_id was passed from WCS');
3112 END IF;
3113 FND_MESSAGE.SET_NAME('WMS', 'WMS_MISSING_XFER_LPN');
3114 FND_MSG_PUB.ADD;
3115
3116 --update wdr for error_code and error_mesg
3117 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3118 l_any_row_failed := TRUE;
3119 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3120 GOTO continue_loop;
3121 END IF;
3122
3123
3124 -- Getting info about item being lot/Serial controlled
3125 SELECT lot_control_code,serial_number_control_code,primary_uom_code
3126 INTO l_lot_code,l_serial_code,l_primary_uom
3127 FROM mtl_system_items
3128 WHERE organization_id = l_rec.organization_id
3129 AND inventory_item_id = l_inventory_item_id;
3130 IF (l_debug = 1) THEN
3131 trace('l_lot_code := '||l_lot_code||':l_serial_code:='||l_serial_code);
3132 END IF;
3133
3134
3135 -- Error out if Short picked for Serial item
3136 IF l_qty_discrepancy_flag <> 0 AND (l_serial_code >1 AND
3137 l_serial_code<>6) THEN
3138 IF (l_debug = 1) THEN
3139 trace('Error:UnderPick of Serial item is not supported');
3140 END IF;
3141 FND_MESSAGE.SET_NAME('WMS', 'WMS_SER_SHORT_PICK_ERR');
3142 FND_MSG_PUB.ADD;
3143
3144 --update wdr for error_code and error_mesg
3145 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3146 l_any_row_failed := TRUE;
3147 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3148 GOTO continue_loop;
3149
3150 END IF;
3151
3152 ----*****************New Code Starts***********
3153 --Game plan to support mix of just Load_confirm along with task_confirm
3154 --Step0 Get reouseces information
3155 --Step1 Insert into WMS_DISPATCHED_TASKS --as "ACTIVE =9" for the user fnd_global.user_id
3156 --Step2 Do all MMTT manipulation and then WDT manipulation
3157 --Step3 Update the transaction_header_id of MMTT tasks that need to be dropped TO a NEW same common value AND THEN call TM
3158 --Step4 Archieve task Xfer WDT to WDTH for records that are being dropped
3159 --Step5 Call TM after the loop
3160 --Step6 Updae LPN Context apporpriately for droped LPNs:
3161 -- Replenishment task = 2 (reside IN inv), so tasks are taken
3162 -- care OF BY TM
3163
3164
3165 --Step0 Get reources information
3166 -- Picking or Replenishment task
3167 SELECT bremp.resource_id role_id
3168 , t.wms_task_type
3169 , t.standard_operation_id
3170 , t.operation_plan_id
3171 INTO l_per_res_id
3172 , l_wms_task_type
3173 , l_std_op_id
3174 , l_operation_plan_id
3175 FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
3176 WHERE t.transaction_temp_id = l_rec.task_id
3177 AND t.standard_operation_id = bsor.standard_operation_id
3178 AND bsor.resource_id = bremp.resource_id
3179 AND bremp.resource_type = 2
3180 AND ROWNUM < 2;
3181
3182
3183 SELECT employee_id INTO l_person_id
3184 FROM fnd_user WHERE user_id = fnd_global.user_id;
3185
3186
3187 --Step1 Insert into WMS_DISPATCHED_TASKS --as "ACTIVE =9"
3188
3189 INSERT INTO wms_dispatched_tasks
3190 (
3191 task_id
3192 , transaction_temp_id
3193 , organization_id
3194 , user_task_type
3195 , person_id
3196 , effective_start_date
3197 , effective_end_date
3198 , person_resource_id
3199 , status
3200 , dispatched_time
3201 , last_update_date
3202 , last_updated_by
3203 , creation_date
3204 , created_by
3205 , task_type
3206 , operation_plan_id
3207 , move_order_line_id
3208 )
3209 VALUES (
3210 wms_dispatched_tasks_s.NEXTVAL
3211 , l_rec.task_id --transaction_temp_id
3212 , l_rec.organization_id
3213 , NVL(l_std_op_id, 2)
3214 , l_person_id
3215 , SYSDATE
3216 , SYSDATE
3217 , l_per_res_id
3218 , l_g_task_active
3219 , SYSDATE
3220 , SYSDATE
3221 , fnd_global.user_id
3222 , SYSDATE
3223 , fnd_global.user_id
3224 , l_wms_task_type
3225 , l_operation_plan_id
3226 , l_move_order_line_id
3227 );
3228
3229
3230
3231 --Step2 DO all MMTT manipulation
3232
3233
3234 IF (l_qty_discrepancy_flag = 0 AND l_sub_discrepancy_flag = 0 AND
3235 l_loc_discrepancy_flag = 0) THEN --Has picked all suggested values
3236
3237 IF (l_debug = 1) THEN
3238 trace('User has picked all suggested values');
3239 END IF;
3240
3241 --Even if the user has picked the suggested qty, make sure he
3242 --picked allocated lot/serials. Validation for lot/serial
3243 --will be required only in case he passes those information,
3244 --Otherwise will not error out assuming he picked correct ones
3245 --Update the request_id for child records if they exist.
3246
3247 IF (l_lot_code >1 OR l_serial_code >1) THEN--LOT OR/AND SERIAL ITEMS
3248 IF (l_debug = 1) THEN
3249 trace('device_confirmation:validating lot/serial substitution');
3250 END IF;
3251
3252 validate_child_record(l_rec.relation_id,'Y',l_lot_code,l_serial_code,l_txn_temp_id,l_qty_discrepancy_flag,l_rec.transaction_quantity,x_return_status);
3253 --l_rec.task_id is same asl_txn_temp_id in this file
3254
3255 IF x_return_status <> FND_API.g_ret_sts_success THEN
3256 IF (l_debug = 1) THEN
3257 trace('Error:In validating Lot/Serial information');
3258 END IF;
3259 FND_MESSAGE.SET_NAME('WMS', 'WMS_LOT_SER_VALIDATION_FAIL');
3260 FND_MSG_PUB.ADD;
3261
3262 --update wdr for error_code and error_mesg
3263 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3264 l_any_row_failed := TRUE;
3265 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3266 GOTO continue_loop;
3267
3268 END IF;
3269
3270 END IF;--LOT OR/AND SERIAL ITEMS
3271
3272
3273
3274 --Updating MMTT with LPN information
3275 IF (l_debug = 1) THEN
3276 trace('device_confirmation:Updating MMTT');
3277 END IF;
3278
3279 IF (l_rec.lpn_id IS NOT NULL) THEN--picked an LPN
3280
3281 IF (l_debug = 1) THEN
3282 trace('device_confirmation:user has picked LPN');
3283 END IF;
3284 --IN this case lpn_match=3 won't arise otherwise we have
3285 --either data issue or l_lpn_match returned is not correct
3286
3287 --l_lpn_match will have value only in case there is
3288 --wdr.lpn_id, so no need to check for it being null here
3289
3290 IF l_lpn_match=1 THEN --and no qty_disc
3291
3292 UPDATE mtl_material_transactions_temp
3293 SET content_lpn_id = l_rec.lpn_id,
3294 transfer_lpn_id = l_rec.xfer_lpn_id
3295 WHERE transaction_temp_id = l_txn_temp_id
3296 AND organization_id= l_rec.organization_id;
3297
3298 -- Later update LPN context For this case of picked_from_LPN to Packing
3299 -- context AS it will be entirely nested
3300
3301 ELSIF l_lpn_match=2 THEN
3302 if l_rec.lpn_id = l_rec.xfer_lpn_id then
3303 --Error
3304 IF (l_debug = 1) THEN
3305 trace('error out,Can not move the entire LPN');
3306 END IF;
3307 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVLID_LPN_MOVE');
3308 FND_MSG_PUB.ADD;
3309
3310 --update wdr for error_code and error_mesg
3311 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3312 l_any_row_failed := TRUE;
3313 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3314 GOTO continue_loop;
3315 else
3316 if l_rec.transaction_quantity > l_lpn_qty_pickable then
3317 --ERROR;
3318 IF (l_debug = 1) THEN
3319 trace('error out,qty picked is more then pickable qty in LPN');
3320 END IF;
3321 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_QTY');
3322 FND_MSG_PUB.ADD;
3323
3324 --update wdr for error_code and error_mesg
3325 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3326 l_any_row_failed := TRUE;
3327 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3328 GOTO continue_loop;
3329
3330 else
3331 UPDATE mtl_material_transactions_temp
3332 SET lpn_id = l_rec.lpn_id,
3333 transfer_lpn_id = l_rec.xfer_lpn_id
3334 WHERE transaction_temp_id = l_txn_temp_id
3335 AND organization_id= l_rec.organization_id;
3336 end if;
3337 end if;
3338
3339 ELSIF l_lpn_match=4 THEN
3340 if l_rec.lpn_id = l_rec.xfer_lpn_id then
3341 --ERROR;
3342 IF (l_debug = 1) THEN
3343 trace('error out,Can not move the entire LPN');
3344 END IF;
3345 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_LPN_MOVE');
3346 FND_MSG_PUB.ADD;
3347
3348 --update wdr for error_code and error_mesg
3349 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3350 l_any_row_failed := TRUE;
3351 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3352 GOTO continue_loop;
3353 else
3354 UPDATE mtl_material_transactions_temp
3355 SET lpn_id = l_rec.lpn_id,
3356 transfer_lpn_id = l_rec.xfer_lpn_id
3357 WHERE transaction_temp_id = l_txn_temp_id
3358 AND organization_id= l_rec.organization_id;
3359 end if;
3360
3361 END IF;
3362
3363 ELSE--PICKED LOOSE
3364 IF (l_debug = 1) THEN
3365 trace('user has picked loose');
3366 END IF;
3367 UPDATE mtl_material_transactions_temp
3368 SET transfer_lpn_id = l_rec.xfer_lpn_id
3369 WHERE transaction_temp_id = l_txn_temp_id
3370 AND organization_id= l_rec.organization_id;
3371
3372 END IF;--picked LOOSE
3373
3374
3375 ELSE--means,at least one kind of qty/sub/loc discrepancy
3376
3377 IF (l_debug = 1) THEN
3378 trace('At least one kind of qty/sub/loc discrepancy');
3379 END IF;
3380
3381 --If there is qty discrepancy, only in that case worry about
3382 --child records here because the case in which the user picked
3383 --the suggested qty of different lot/serial has been taken care
3384 --of above
3385 --Update MTLT/MSNT
3386
3387 IF (l_qty_discrepancy_flag <> 0) AND l_rec.transaction_quantity <> 0 THEN --means qty_discrepancy
3388 --we do not want to error out in case qty_picked is 0 and
3389 --lot/serial info in child record is NOT provided
3390
3391 IF (l_lot_code >1 OR l_serial_code >1) THEN --LOT OR/AND SERIAL ITEMS
3392 IF (l_debug = 1) THEN
3393 trace('device_confirmation:validating lot/serial substitution');
3394 END IF;
3395 --make sure that WCS has passed information about picked: Manadatory
3396 --Also verify lot_qty/serial_qty information is correct
3397 --for respective child records. like for a particular lot,
3398 --lot_qty should NOT be greater than allocated_lot_qty
3399
3400 validate_child_record(l_rec.relation_id,'Y',l_lot_code,l_serial_code,l_txn_temp_id,l_qty_discrepancy_flag,l_rec.transaction_quantity,x_return_status);
3401
3402 IF x_return_status <> FND_API.g_ret_sts_success THEN
3403 IF (l_debug = 1) THEN
3404 trace('Error:In validating Lot/Serial information');
3405 END IF;
3406 FND_MESSAGE.SET_NAME('WMS', 'WMS_LOT_SER_VALIDATION_FAIL');
3407 FND_MSG_PUB.ADD;
3408
3409 --update wdr for error_code and error_mesg
3410 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3411 l_any_row_failed := TRUE;
3412 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3413 GOTO continue_loop;
3414 END IF;
3415
3416 END IF;--LOT OR/AND SERIAL ITEMS
3417 END IF;--means qty_discrepancy
3418
3419
3420 --getting some info.
3421 BEGIN
3422 SELECT COUNT(*)
3423 INTO l_mmtt_count
3424 FROM mtl_material_transactions_temp mmtt
3425 WHERE mmtt.transaction_temp_id <> l_rec.task_id
3426 AND mmtt.move_order_line_id = l_move_order_line_id;
3427 EXCEPTION
3428 WHEN no_data_found THEN
3429 l_mmtt_count := 0;
3430 END;
3431
3432 SELECT mtrl.txn_source_line_id
3433 INTO l_source_line_id
3434 FROM mtl_txn_request_lines mtrl
3435 WHERE mtrl.line_id = l_move_order_line_id;
3436
3437
3438 --If user has picked qty zero, backorder line,log
3439 --exception,fire appropriate workflow
3440 IF l_rec.transaction_quantity = 0 THEN-- l_cur.transaction_quantity = 0
3441
3442 IF l_mmtt_count > 0 THEN -- l_mmtt_count > 0
3443 IF (l_debug = 1) THEN
3444 trace('Other MMTT lines exist too. Delete MMTT and UPDATE move ORDER line');
3445 END IF;
3446 DELETE FROM mtl_material_transactions_temp
3447 WHERE transaction_temp_id = l_txn_temp_id;
3448
3449 IF l_lot_code > 1 THEN
3450
3451 -- Lot controlled item
3452 IF (l_serial_code >1 AND l_serial_code<>6) THEN -- Lot and Serial controlled item
3453
3454 DELETE FROM mtl_serial_numbers_temp msnt
3455 WHERE msnt.transaction_temp_id IN
3456 (SELECT mtlt.serial_transaction_temp_id
3457 FROM mtl_transaction_lots_temp mtlt
3458 WHERE mtlt.transaction_temp_id = l_txn_temp_id);
3459 END IF;
3460
3461 DELETE FROM mtl_transaction_lots_temp mtlt
3462 WHERE mtlt.transaction_temp_id = l_txn_temp_id;
3463
3464 ELSIF (l_serial_code >1 AND l_serial_code<>6) THEN --Serial controlled item
3465
3466 DELETE FROM mtl_serial_numbers_temp msnt
3467 WHERE msnt.transaction_temp_id = l_txn_temp_id;
3468
3469 END IF;
3470
3471 UPDATE mtl_txn_request_lines
3472 SET quantity_detailed = quantity_detailed - l_pr_qty-- this diff is zero
3473 WHERE line_id = l_move_order_line_id;
3474
3475 ELSE -- means l_mmtt_count = 0
3476
3477 IF (l_debug = 1) THEN
3478 trace('Just one MMTT line exists. Close MO and backorder');
3479 END IF;
3480
3481 DELETE FROM wms_dispatched_tasks WHERE transaction_temp_id = l_rec.task_id; --Bug 6987801
3482
3483 inv_mo_backorder_pvt.backorder
3484 (p_line_id => l_move_order_line_id
3485 , x_return_status => x_return_status
3486 , x_msg_count => x_msg_count
3487 , x_msg_data => x_msg_data);
3488
3489
3490 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3491 FND_MESSAGE.SET_NAME('WMS','WMS_BACKORDER_FAILED');
3492 FND_MSG_PUB.ADD;
3493
3494 --update wdr for error_code and error_mesg
3495 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3496 l_any_row_failed := TRUE;
3497 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3498 GOTO continue_loop;
3499 END IF;
3500
3501 IF (l_debug = 1) THEN
3502 trace('Calling API to clean up reservations');
3503 END IF;
3504
3505 inv_transfer_order_pvt.clean_reservations
3506 (p_source_line_id => l_source_line_id,
3507 x_return_status => x_return_status,
3508 x_msg_count => x_msg_count,
3509 x_msg_data => x_msg_data);
3510
3511 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3512 FND_MESSAGE.SET_NAME('WMS','WMS_BACKORDER_FAILED');
3513 FND_MSG_PUB.ADD;
3514
3515 --update wdr for error_code and error_mesg
3516 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3517 l_any_row_failed := TRUE;
3518 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3519 GOTO continue_loop;
3520 END IF;
3521
3522 DELETE FROM mtl_material_transactions_temp
3523 WHERE transaction_temp_id = l_txn_temp_id;
3524
3525 IF l_lot_code > 1 THEN
3526
3527 -- Lot controlled item
3528 IF (l_serial_code >1 AND l_serial_code<>6) THEN -- Lot and Serial controlled item
3529 DELETE FROM mtl_serial_numbers_temp msnt
3530 WHERE msnt.transaction_temp_id IN
3531 (SELECT mtlt.serial_transaction_temp_id
3532 FROM mtl_transaction_lots_temp mtlt
3533 WHERE mtlt.transaction_temp_id = l_txn_temp_id);
3534 END IF;
3535
3536 DELETE FROM mtl_transaction_lots_temp mtlt
3537 WHERE mtlt.transaction_temp_id = l_txn_temp_id;
3538
3539 ELSIF (l_serial_code >1 AND l_serial_code<>6) THEN --Serial controlled item
3540
3541 DELETE FROM mtl_serial_numbers_temp msnt
3542 WHERE msnt.transaction_temp_id = l_txn_temp_id;
3543
3544 END IF;
3545
3546 END IF;-- l_mmtt_count > 0
3547
3548
3549 IF (l_debug = 1) THEN
3550 trace('Reverting Xfer LPN context to its original value');
3551 END IF;
3552 --
3553 -- Update the context to whatever it started with
3554 -- before processing the record with zero qty
3555 --
3556
3557 /* updated as part of the bug 4411819
3558 --using wms_container_pvt.Modify_LPN() API instead
3559 UPDATE wms_license_plate_numbers
3560 SET lpn_context = l_lpn_context -- this still has
3561 --the original value of lpn context
3562 WHERE lpn_id = l_rec.xfer_lpn_id;
3563 */
3564
3565 --Bug 6987801 : added if xfer_lpn_id is not null
3566 IF l_rec.xfer_lpn_id IS NOT NULL THEN
3567 l_lpn.lpn_id := l_rec.xfer_lpn_id;
3568 l_lpn.organization_id := l_rec.organization_id;
3569 l_lpn.lpn_context := l_lpn_context;
3570
3571 wms_container_pvt.Modify_LPN
3572 (
3573 p_api_version => 1.0
3574 , p_validation_level => fnd_api.g_valid_level_none
3575 , x_return_status => x_return_status
3576 , x_msg_count => x_msg_count
3577 , x_msg_data => x_msg_data
3578 , p_lpn => l_lpn
3579 ) ;
3580
3581 l_lpn := NULL;
3582 END IF;
3583
3584 ELSE --means l_cur.transaction_quantity <> 0
3585
3586
3587 IF (l_qty_discrepancy_flag <> 0) THEN --for qty disc only
3588 --backorder remaining qty
3589 IF (l_debug = 1) THEN
3590 trace('Inside Qty discrepancy ');
3591 END IF;
3592
3593 -- Clean up code. Have to delete MMTT, MTLT, MSNT, WDT, if picked less
3594 -- and update move order line
3595
3596 IF (l_debug = 1) THEN
3597 trace('Deleteing all unpicked lot/serials from MTLT/MSNT');
3598 END IF;
3599
3600 --delete all unpicked lot/serials from MTLT/MSNT
3601 IF l_lot_code >1 THEN
3602
3603 IF (l_serial_code >1 AND l_serial_code<>6) THEN
3604
3605 DELETE FROM mtl_serial_numbers_temp msnt
3606 WHERE transaction_temp_id IN
3607 (SELECT msnt.transaction_temp_id
3608 FROM mtl_transaction_lots_temp mtlt,
3609 mtl_serial_numbers_temp msnt
3610 WHERE mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
3611 AND mtlt.transaction_temp_id =l_txn_temp_id)
3612 AND msnt.fm_serial_number NOT IN
3613 (SELECT serial_number FROM wms_device_requests
3614 WHERE relation_id = l_rec.relation_id
3615 AND task_id = l_txn_temp_id
3616 AND business_event_id = wms_device_integration_pvt.WMS_BE_TASK_CONFIRM
3617 AND task_summary = 'N');
3618
3619 END IF;
3620
3621
3622 --Update qty in MTLT by qty passed in the child
3623 -- records for corresponding lots
3624
3625 --In the child record, txn quantity is total
3626 --quantity of the parent record. It is the
3627 --lot_qty column which keeps correct lot qty for child record.
3628
3629 for l_mtlt_update in c_mtlt_update(l_rec.relation_id,l_txn_temp_id)
3630 loop
3631
3632 l_mtlt_pr_qty :=l_mtlt_update.lot_qty;
3633
3634 --get the primary qty correctly based on UOM
3635 IF (l_primary_uom <> l_transaction_uom) THEN
3636 l_mtlt_pr_qty := INV_Convert.inv_um_convert
3637 (item_id => l_inventory_item_id,
3638 precision => null,
3639 from_quantity => l_mtlt_update.lot_qty,
3640 from_unit => l_transaction_uom,
3641 to_unit => l_primary_uom,
3642 from_name => null,
3643 to_name => null);
3644 END IF;
3645
3646 trace('l_mtlt_pr_qty::'||l_mtlt_pr_qty||'mtlt_lot_TXN_QTY:::'||l_mtlt_update.lot_qty);
3647
3648 update mtl_transaction_lots_temp set
3649 TRANSACTION_QUANTITY = l_mtlt_update.lot_qty,
3650 PRIMARY_QUANTITY = l_mtlt_pr_qty
3651 WHERE transaction_temp_id = l_txn_temp_id
3652 AND lot_number = l_mtlt_update.lot_number;
3653
3654 end loop;
3655
3656
3657
3658 ELSIF (l_serial_code >1 AND l_serial_code<>6) THEN
3659
3660 -- Deleting serials which have not been picked
3661
3662 DELETE FROM mtl_serial_numbers_temp msnt
3663 WHERE msnt.transaction_temp_id = l_txn_temp_id
3664 AND msnt.fm_serial_number NOT IN
3665 ( SELECT wdr.serial_number FROM
3666 wms_device_requests wdr
3667 WHERE relation_id = l_rec.relation_id
3668 AND task_id = l_txn_temp_id
3669 AND task_summary = 'N'
3670 AND business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.WMS_BE_load_CONFIRM));
3671
3672
3673 END IF;
3674
3675 IF (l_debug = 1) THEN
3676 trace('Upating quantity_detailed of mtrl');
3677 END IF;
3678 UPDATE mtl_txn_request_lines
3679 SET quantity_detailed = l_rec.transaction_quantity
3680 WHERE line_id = l_move_order_line_id;
3681
3682 END IF;--for qty disc only
3683
3684
3685
3686 --Updating MMTT for LPN
3687 IF (l_rec.lpn_id IS NOT NULL) THEN--picked an LPN
3688 IF (l_debug = 1) THEN
3689 trace('Updating MMTT for LPN');
3690 END IF;
3691 IF l_lpn_match =1 THEN
3692 if (l_qty_discrepancy_flag <> 0) THEN--means qty_disc
3693 if l_rec.lpn_id = l_rec.xfer_lpn_id then
3694 --ERROR;
3695 IF (l_debug = 1) THEN
3696 trace('error out,Can not move the entire LPN');
3697 END IF;
3698 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_LPN_MOVE');
3699 FND_MSG_PUB.ADD;
3700 --update wdr for error_code and error_mesg
3701 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3702 l_any_row_failed := TRUE;
3703 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3704 GOTO continue_loop;
3705
3706 ELSE-- taking from pickLPN to XferLPN
3707 UPDATE mtl_material_transactions_temp
3708 SET lpn_id = l_rec.lpn_id,
3709 transfer_lpn_id = l_rec.xfer_lpn_id
3710 WHERE transaction_temp_id = l_txn_temp_id
3711 AND organization_id= l_rec.organization_id;
3712 end if;
3713 ELSE --means no qty discrepancy
3714
3715 UPDATE mtl_material_transactions_temp
3716 SET content_lpn_id = l_rec.lpn_id,
3717 transfer_lpn_id = l_rec.xfer_lpn_id
3718 WHERE transaction_temp_id = l_txn_temp_id
3719 AND organization_id= l_rec.organization_id;
3720 END IF;
3721
3722 ELSIF l_lpn_match=2 THEN
3723 if l_rec.lpn_id = l_rec.xfer_lpn_id then
3724 --Error
3725 IF (l_debug = 1) THEN
3726 trace('error out,Can not move the entire LPN');
3727 END IF;
3728 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_LPN_MOVE');
3729 FND_MSG_PUB.ADD;
3730 --update wdr for error_code and error_mesg
3731 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3732 l_any_row_failed := TRUE;
3733 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3734 GOTO continue_loop;
3735 else if l_rec.transaction_quantity > l_lpn_qty_pickable then
3736 --ERROR;
3737 IF (l_debug = 1) THEN
3738 trace('error out,qty picked is more then pickable qty in LPN');
3739 END IF;
3740 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_QTY');
3741 FND_MSG_PUB.ADD;
3742 --update wdr for error_code and error_mesg
3743 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3744 l_any_row_failed := TRUE;
3745 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3746 GOTO continue_loop;
3747
3748 else-- taking from pickLPN to XferLPN
3749 UPDATE mtl_material_transactions_temp
3750 SET lpn_id = l_rec.lpn_id,
3751 transfer_lpn_id = l_rec.xfer_lpn_id
3752 WHERE transaction_temp_id = l_txn_temp_id
3753 AND organization_id= l_rec.organization_id;
3754 end if;
3755 end if;
3756
3757 ELSIF l_lpn_match = 3 THEN--already means qty_disc
3758 if l_rec.transaction_quantity = l_lpn_qty_pickable then
3759
3760 UPDATE mtl_material_transactions_temp
3761 SET content_lpn_id = l_rec.lpn_id,
3762 transfer_lpn_id = l_rec.xfer_lpn_id
3763 WHERE transaction_temp_id = l_txn_temp_id
3764 AND organization_id= l_rec.organization_id;
3765
3766 elsif l_rec.transaction_quantity < l_lpn_qty_pickable then
3767 if l_rec.lpn_id = l_rec.xfer_lpn_id then
3768 --ERROR;
3769 IF (l_debug = 1) THEN
3770 trace('error out,Can not move the entire LPN');
3771 END IF;
3772 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_LPN_MOVE');
3773 FND_MSG_PUB.ADD;
3774 --update wdr for error_code and error_mesg
3775 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3776 l_any_row_failed := TRUE;
3777 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3778 GOTO continue_loop;
3779 ELSE
3780 UPDATE mtl_material_transactions_temp
3781 SET lpn_id = l_rec.lpn_id,
3782 transfer_lpn_id = l_rec.xfer_lpn_id
3783 WHERE transaction_temp_id = l_txn_temp_id
3784 AND organization_id= l_rec.organization_id;
3785 end if;
3786 else --means (wdr.transaction_quantity > l_lpn_qty_pickable), not possible
3787 --ERROR;
3788 IF (l_debug = 1) THEN
3789 trace('error out,qty picked is more then pickable qty in LPN');
3790 END IF;
3791 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_QTY');
3792 FND_MSG_PUB.ADD;
3793 --update wdr for error_code and error_mesg
3794 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3795 l_any_row_failed := TRUE;
3796 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3797 GOTO continue_loop;
3798
3799 end if;
3800
3801 ELSIF l_lpn_match = 4 THEN
3802 if l_rec.lpn_id = l_rec.xfer_lpn_id then
3803 --ERROR;
3804 IF (l_debug = 1) THEN
3805 trace('error out,Can not move the entire LPN');
3806 END IF;
3807 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_LPN_MOVE');
3808 FND_MSG_PUB.ADD;
3809 --update wdr for error_code and error_mesg
3810 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3811 l_any_row_failed := TRUE;
3812 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3813 GOTO continue_loop;
3814 else
3815 UPDATE mtl_material_transactions_temp
3816 SET lpn_id = l_rec.lpn_id,
3817 transfer_lpn_id = l_rec.xfer_lpn_id
3818 WHERE transaction_temp_id = l_txn_temp_id
3819 AND organization_id= l_rec.organization_id;
3820 end if;
3821
3822 END IF;
3823
3824 ELSE-- Picked Loose
3825 IF (l_debug = 1) THEN
3826 trace('User has picked loose');
3827 END IF;
3828 UPDATE mtl_material_transactions_temp
3829 SET transfer_lpn_id = l_rec.xfer_lpn_id
3830 WHERE transaction_temp_id = l_txn_temp_id
3831 AND organization_id= l_rec.organization_id;
3832
3833 END IF;--picked Loose
3834
3835
3836 IF (l_debug = 1) THEN
3837 trace('Picked txn quantity:'||l_rec.transaction_quantity);
3838 END IF;
3839
3840 --update MMTT for qty/loc/sub disc
3841 ll_pr_qty := l_pr_qty ;
3842 ll_mmtt_txn_qty := l_mmtt_txn_qty;
3843
3844 IF (l_qty_discrepancy_flag <> 0) THEN
3845
3846 ll_mmtt_txn_qty := l_rec.transaction_quantity;
3847 ll_pr_qty := l_rec.transaction_quantity;
3848
3849 IF (l_debug = 1) THEN
3850 trace('Updating MMTT for qty disc');
3851 END IF;
3852 --get the primary qty correctly based on UOM
3853 IF (l_primary_uom <> l_transaction_uom) THEN
3854 ll_pr_qty := INV_Convert.inv_um_convert
3855 (item_id => l_inventory_item_id,
3856 precision => null,
3857 from_quantity => l_rec.transaction_quantity,
3858 from_unit => l_transaction_uom,
3859 to_unit => l_primary_uom,
3860 from_name => null,
3861 to_name => null);
3862 END IF;
3863 IF (l_debug = 1) THEN
3864 trace('qty discrepancy new l_txn_qty:'||ll_mmtt_txn_qty);
3865 trace('qty discrepancy new l_prim_qty:'||ll_pr_qty);
3866 END IF;
3867
3868 END IF;
3869
3870
3871 --Updating MMTT for sub/loc disc
3872 ll_xfer_sub_code := l_xfer_sub_code;
3873 ll_xfer_loc_id := l_xfer_loc_id;
3874
3875 IF (l_sub_discrepancy_flag <> 0) AND
3876 (l_loc_discrepancy_flag <> 0) THEN
3877
3878 ll_xfer_sub_code := l_rec.transfer_sub_code;
3879 ll_xfer_loc_id := l_rec.transfer_loc_id;
3880
3881 IF (l_debug = 1) THEN
3882 trace('sub/loc discrepancy new ll_xfer_sub_code:'||ll_xfer_sub_code );
3883 trace('sub/loc discrepancy new ll_xfer_loc_id:'|| ll_xfer_loc_id );
3884 END IF;
3885 ELSIF (l_loc_discrepancy_flag <> 0) THEN
3886
3887 ll_xfer_loc_id := l_rec.transfer_loc_id;
3888 IF (l_debug = 1) THEN
3889 trace('loc discrepancy new ll_xfer_loc_id:'|| ll_xfer_loc_id );
3890 END IF;
3891 END IF;
3892
3893
3894 IF (l_debug = 1) THEN
3895 trace('Updating MMTT for qty/sub/loc disc');
3896 END IF;
3897
3898 UPDATE mtl_material_transactions_temp
3899 SET transfer_subinventory = ll_xfer_sub_code
3900 , transfer_to_location = ll_xfer_loc_id
3901 , primary_quantity = ll_pr_qty
3902 , transaction_quantity = ll_mmtt_txn_qty
3903 WHERE transaction_temp_id = l_txn_temp_id
3904 AND organization_id = l_rec.organization_id;
3905
3906
3907
3908 END IF; --l_cur.transaction_quantity <> 0
3909
3910
3911
3912 -- Log Exception
3913 IF l_rec.reason_id IS NOT NULL THEN
3914
3915 IF (l_debug = 1) THEN
3916 trace('logging exception for qty/sub/loc discrepanc FOR reason id'||l_rec.reason_id);
3917 END IF;
3918
3919 wms_txnrsn_actions_pub.log_exception
3920 (p_api_version_number =>1.0
3921 , p_init_msg_lst =>fnd_api.g_false
3922 , p_commit => FND_API.G_FALSE
3923 , x_return_status =>x_return_status
3924 , x_msg_count =>x_msg_count
3925 , x_msg_data =>x_msg_data
3926 , p_organization_id =>l_rec.organization_id
3927 , p_mmtt_id =>l_txn_temp_id
3928 , p_task_id =>l_txn_temp_id
3929 , p_reason_id =>l_rec.reason_id
3930 , p_subinventory_code=>l_sub_code--picking sub
3931 , p_locator_id =>l_loc_id--picking loc
3932 , p_discrepancy_type =>1
3933 , p_user_id =>l_last_updated_by--from mmtt
3934 , p_item_id =>l_inventory_item_id
3935 , p_is_loc_desc =>TRUE --Bug 4319541
3936 );
3937
3938 IF (l_debug = 1) THEN
3939 trace('After logging exception for qty discrepancy');
3940 END IF;
3941
3942 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3943 FND_MESSAGE.SET_NAME('WMS','WMS_LOG_EXCEPTION_FAIL');
3944 FND_MSG_PUB.ADD;
3945 --update wdr for error_code and error_mesg
3946 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3947 l_any_row_failed := TRUE;
3948 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3949 GOTO continue_loop;
3950
3951 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3952 FND_MESSAGE.SET_NAME('WMS','WMS_LOG_EXCEPTION_FAIL');
3953 FND_MSG_PUB.ADD;
3954 --update wdr for error_code and error_mesg
3955 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
3956 l_any_row_failed := TRUE;
3957 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
3958 GOTO continue_loop;
3959
3960 END IF;
3961
3962 END IF;
3963
3964
3965 l_wf:=0;
3966
3967 BEGIN
3968 SELECT 1
3969 INTO l_wf
3970 FROM MTL_TRANSACTION_REASONS
3971 WHERE reason_id=l_rec.reason_id
3972 and workflow_name is not null
3973 and workflow_name<>' '
3974 and workflow_process is not null
3975 and workflow_process<>' ';
3976 EXCEPTION
3977 WHEN NO_DATA_FOUND THEN
3978 l_wf:=0;
3979 END;
3980
3981 IF l_wf > 0 THEN
3982
3983 IF (l_debug = 1) THEN
3984 trace('WF exists qty_pick reason_id'||l_rec.reason_id);
3985 END IF;
3986 -- Calling Workflow
3987
3988 IF l_rec.reason_id IS NOT NULL THEN
3989
3990 IF (l_debug = 1) THEN
3991 trace('Calling workflow wrapper for Qty Discrepancy');
3992 END IF;
3993
3994 wms_workflow_wrappers.wf_wrapper
3995 (p_api_version => 1.0,
3996 p_init_msg_list => fnd_api.g_false,
3997 p_commit => fnd_api.g_false,
3998 x_return_status => x_return_status ,
3999 x_msg_count => x_msg_count,
4000 x_msg_data => x_msg_data,
4001 p_org_id => l_rec.organization_id ,
4002 p_rsn_id => l_rec.reason_id,
4003 p_calling_program => 'wms_device_confirmation_pub.device_confirmation',
4004 p_tmp_id => l_txn_temp_id,
4005 p_quantity_picked => l_rec.transaction_quantity
4006 );
4007
4008 END IF;
4009
4010 IF (l_debug = 1) THEN
4011 trace('After Calling WF Wrapperfor Qty Discrepancy');
4012 END IF;
4013
4014 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4015 IF (l_debug = 1) THEN
4016 trace('device_confirmation: Error callinf WF wrapper');
4017 END IF;
4018 FND_MESSAGE.SET_NAME('WMS','WMS_WORK_FLOW_FAIL');
4019 FND_MSG_PUB.ADD;
4020 --update wdr for error_code and error_mesg
4021 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
4022 l_any_row_failed := TRUE;
4023 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
4024 GOTO continue_loop;
4025
4026 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
4027 IF (l_debug = 1) THEN
4028 trace('device_confirmation: Error calling WF wrapper');
4029 END IF;
4030 FND_MESSAGE.SET_NAME('WMS','WMS_WORK_FLOW_FAIL');
4031 FND_MSG_PUB.ADD;
4032 --update wdr for error_code and error_mesg
4033 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
4034 l_any_row_failed := TRUE;
4035 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
4036 GOTO continue_loop;
4037
4038 END IF;
4039
4040 END IF;
4041
4042
4043 END IF;--at least one kind of discrepancy
4044
4045
4046
4047 IF (l_debug = 1) THEN
4048 trace('Updating Headr and txn_status of the Drop record');
4049 END IF;
4050
4051 UPDATE mtl_material_transactions_temp
4052 SET transaction_date = sysdate
4053 ,transaction_status = 3
4054 ,transaction_header_id = l_txn_hdr_id
4055 WHERE transaction_temp_id = l_txn_temp_id
4056 AND organization_id= l_rec.organization_id
4057 AND l_rec.business_event_id <> wms_device_integration_pvt.wms_be_load_confirm;
4058 -- update headerand txn_status of only those mmtt records that are destined TO be dropped
4059
4060
4061
4062 --Step2.2 Do manipulation of WDT for THIS task status as Loaded Now
4063 -- IT does not have to wait till laterr stage.
4064
4065 UPDATE wms_dispatched_tasks
4066 SET status = l_g_task_loaded
4067 ,last_update_date = Sysdate
4068 ,loaded_time = Sysdate
4069 ,last_updated_by = fnd_global.user_id
4070 WHERE transaction_temp_id = l_rec.task_id;
4071
4072
4073
4074 /*
4075 --update LPN context of picked_from_LPN as it
4076 --will be entirely nested in following cases
4077 IF ((l_lpn_match=1 or l_lpn_match=3) AND l_rec.lpn_id <> l_rec.xfer_lpn_id)
4078
4079
4080
4081 IF (l_rec.lpn_id IS NOT NULL AND (l_lpn_match=1 or l_lpn_match=3) AND l_rec.lpn_id <> l_rec.xfer_lpn_id) THEN
4082
4083 wms_container_pvt.modify_lpn_wrapper
4084 (p_api_version => '1.0',
4085 x_return_status => x_return_status,
4086 x_msg_count => x_msg_count,
4087 x_msg_data => x_msg_data,
4088 p_lpn_id => l_rec.lpn_id,
4089 p_lpn_context => WMS_Container_PUB.LPN_CONTEXT_PACKING);
4090
4091 IF ((x_return_status = FND_API.g_ret_sts_unexp_error) OR (x_return_status
4092 = FND_API.g_ret_sts_error)) THEN
4093
4094 IF (l_debug = 1) THEN
4095 trace('device_confirmation: Load LPN modify_lpn_wrapper error');
4096 END IF;
4097 FND_MESSAGE.SET_NAME('WMS','WMS_TD_MODIFY_LPN_ERROR');
4098 FND_MSG_PUB.ADD;
4099 --update wdr for error_code and error_mesg
4100 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
4101 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
4102 GOTO continue_loop;
4103
4104
4105 END IF;
4106
4107 END IF;
4108 */
4109
4110 --NOW Change the LPN context of Xfer LPN to Loaded here to avoid
4111 --faling load operation OF entire batch AS against TO
4112 -- malicious records ONLY
4113
4114 --Bug 6987801 : added if xfer_lpn_id is not null
4115 IF l_rec.xfer_lpn_id IS NOT NULL THEN
4116 wms_container_pvt.modify_lpn_wrapper
4117 (p_api_version => '1.0',
4118 x_return_status => x_return_status,
4119 x_msg_count => x_msg_count,
4120 x_msg_data => x_msg_data,
4121 p_lpn_id => l_rec.xfer_lpn_id,
4122 p_lpn_context => WMS_Container_PUB.LPN_CONTEXT_PACKING);
4123
4124 IF ((x_return_status = FND_API.g_ret_sts_unexp_error) OR (x_return_status
4125 = FND_API.g_ret_sts_error)) THEN
4126
4127 IF (l_debug = 1) THEN
4128 trace('device_confirmation: Load LPN modify_lpn_wrapper error');
4129 END IF;
4130 FND_MESSAGE.SET_NAME('WMS','WMS_TD_MODIFY_LPN_ERROR');
4131 FND_MSG_PUB.ADD;
4132 --update wdr for error_code and error_mesg
4133 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
4134 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
4135 GOTO continue_loop;
4136 END IF;
4137 END IF;
4138
4139
4140
4141 ---88888888888888888888888888888888888888888888888888888888888888888
4142 --moved the call to TM for complete batch rather than for each line
4143 ---888888888888888888888888888888888888888888888888888888888888888888
4144
4145
4146 ELSE--means l_txn_temp_id IS NULL,No corresponding record in WDRH
4147 IF (l_debug = 1) THEN
4148 trace('parent_request_id is not valid');
4149 END IF;
4150 FND_MESSAGE.SET_NAME('WMS', 'WMS_MISSING_TASK_INFO');
4151 FND_MSG_PUB.ADD;
4152 --update wdr for error_code and error_mesg
4153 ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
4154 l_any_row_failed := TRUE;
4155 update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
4156 GOTO continue_loop;
4157 END IF;
4158 END IF;-- l_rec.relation_id IS NULL
4159
4160 END IF;--device success
4161
4162 --Update WDR for Success, which will be transferred to WDRH
4163 UPDATE wms_device_requests
4164 SET status_code = 'S',
4165 status_msg = null
4166 WHERE business_event_id in (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
4167 AND task_id = l_rec.task_id
4168 AND relation_id = l_rec.relation_id;
4169
4170 --It has come here it means that, this record was successful
4171 -- increasing the count for successful rows
4172 x_successful_row_cnt := x_successful_row_cnt +1;
4173
4174 <<continue_loop>>
4175 IF (l_debug = 1) THEN
4176 trace('device_confirmation:done with current record ');
4177 END IF;
4178 END LOOP;
4179
4180 --Proces the BATCH now
4181
4182 IF l_any_row_failed THEN
4183 x_return_status := 'W';
4184 END IF;
4185
4186
4187 --validating the account period and updating mmtt accordingly
4188 -- ONLY for records that is being dropped
4189 IF (l_debug = 1) THEN
4190 trace('Check if account period is open before calling TM');
4191 END IF;
4192
4193 FOR l_open_period_check IN c_open_period_check loop
4194
4195 invttmtx.tdatechk(org_id => l_open_period_check.organization_id,
4196 transaction_date => sysdate,
4197 period_id => l_period_id,
4198 open_past_period => l_open_past_period);
4199
4200 IF l_period_id <> -1 THEN
4201 IF (l_debug = 1) THEN
4202 trace('Need to update the account period in MMTT');
4203 END IF;
4204 UPDATE mtl_material_transactions_temp
4205 SET acct_period_id = l_period_id
4206 WHERE transaction_temp_id = l_open_period_check.task_id
4207 AND organization_id = l_open_period_check.organization_id;
4208 ELSE
4209 IF (l_debug = 1) THEN
4210 trace('device_confirmation: Period is invalid');
4211 END IF;
4212 FND_MESSAGE.SET_NAME('INV', 'INV_NO_OPEN_PERIOD');
4213 FND_MSG_PUB.ADD;
4214 x_return_status := FND_API.g_ret_sts_error;
4215 ROLLBACK TO WMS_DEVICE_REQUESTS_SP_OUTER;
4216
4217 END IF;
4218
4219 END LOOP;
4220
4221
4222
4223 IF (l_debug = 1) THEN
4224 trace('Done with all records: Insert WDT History ONLY for LPNs to be DROPPED');
4225 END IF;
4226
4227
4228 --Step4 Xfer WDT to WDTH for records that are being dropped
4229 -- ONLY those MMTT are update with this NEW l_txn_hdr_id that are destined
4230 -- TO be dropped
4231
4232
4233 FOR l_mmtt_csr IN mmtt_csr LOOP
4234
4235 -- we need this for interoperabiliy of Device framework and MObile UI
4236 -- First keep both values transfer_lpn_id and content_lpn_id same but
4237 -- before call tm , correct it
4238
4239 -- Modified for bug 7254269 start
4240 IF (l_mmtt_csr.content_lpn_id = l_mmtt_csr.transfer_lpn_id) THEN
4241 -- We are transferring the entire lpn
4242 UPDATE mtl_material_transactions_temp mmtt
4243 set transfer_lpn_id = NULL , lpn_id = NULL
4244 WHERE mmtt.transaction_temp_id = l_mmtt_csr.transaction_temp_id;
4245 END IF;
4246 -- Modified for bug 7254269 end
4247
4248
4249
4250 wms_task_dispatch_put_away.archive_task
4251 (
4252 p_temp_id => l_mmtt_csr.transaction_temp_id
4253 , p_org_id => l_mmtt_csr.organization_id
4254 , x_return_status => x_return_status
4255 , x_msg_count => x_msg_count
4256 , x_msg_data => x_msg_data
4257 , p_delete_mmtt_flag => 'N'
4258 , p_txn_header_id => l_txn_hdr_id
4259 , p_transfer_lpn_id => NVL(l_mmtt_csr.transfer_lpn_id, l_mmtt_csr.content_lpn_id)
4260 );
4261 END LOOP;
4262
4263
4264
4265 --Step5 Calling TM ONLY for those records in the batch that are destined to be
4266 -- dropped. IN code above only those MMTT records are stamped with
4267 --NEW common transaction_header_id that are TO be dropped.
4268
4269 --Failed records in the batch will have their original header_id, so they
4270 --will not get considered in this call to TM and hence willl NOT be picked by TM
4271
4272 IF (l_debug = 1) THEN
4273 trace('Calling TM ONLY for LPNs to be Dropped' );
4274 trace('Calling TM:hdr_id'||l_txn_hdr_id);
4275 END IF;
4276
4277
4278 l_txn_ret := inv_lpn_trx_pub.process_lpn_trx
4279 (p_trx_hdr_id => l_txn_hdr_id,
4280 p_commit => fnd_api.g_false,
4281 x_proc_msg => x_msg_data);
4282
4283 IF (l_debug = 1) THEN
4284 trace('After call to TM, Txn proc ret'||l_txn_ret);
4285 END IF;
4286
4287 --If any record in the batch fails TM returns -1, so if any record in TM
4288 --fails, total batch will be rolled back
4289
4290 IF l_txn_ret<>0 THEN
4291 IF (l_debug = 1) THEN
4292 trace('*************TM call FAILED***************');
4293 END IF;
4294
4295 FND_MESSAGE.SET_NAME('WMS','WMS_TD_TXNMGR_ERROR' );
4296 FND_MSG_PUB.ADD;
4297
4298 x_return_status := FND_API.g_ret_sts_error;
4299
4300 ROLLBACK TO WMS_DEVICE_REQUESTS_SP_OUTER;
4301
4302 ELSE
4303 --TM CALL SUCCESSFUL
4304
4305
4306 IF (l_debug = 1) THEN
4307 trace('*************TM call successful***************');
4308 trace('Updating context for all replenished LPNs' );
4309 END IF;
4310
4311 --Step6 Update LPN Context apporpriately for processed LPNs
4312 --For droped LPN tasks ONLY:
4313 -- Replenishment task = (reside IN inv) wms_container_pub.lpn_context_inv
4314 -- so taks = Handled by TM (Do NOT do anything)
4315 -- All successful LPNs are already Loaded in the loop above for each call
4316
4317
4318 --Update the LPN context to Reside in INV for all LPNs for Replenishment
4319 --tasks. For SO tasks, it is handled in the TM
4320 -- the cursor c_update_xfer_lpns_context ensure that LPNs for
4321 -- replenishment tasks are here only
4322
4323
4324 FOR l_update_xfer_lpns_context IN c_update_xfer_lpns_context loop
4325
4326 --In case LPN is getting transferred, both lpn_id and
4327 --xfer_lpn_id are same, so getting updated correctly
4328
4329 --as part of the bug 4411819, replaced
4330 --wms_container_pub.modify_lpn_wrapper to wms_container_pvt.Modify_LPN
4331
4332 l_lpn.lpn_id := l_update_xfer_lpns_context.lpn_id;
4333 l_lpn.organization_id := l_update_xfer_lpns_context.organization_id;
4334 l_lpn.lpn_context := wms_container_pub.lpn_context_picked;
4335
4336 wms_container_pvt.Modify_LPN
4337 (
4338 p_api_version => 1.0
4339 , p_validation_level => fnd_api.g_valid_level_none
4340 , x_return_status => x_return_status
4341 , x_msg_count => x_msg_count
4342 , x_msg_data => x_msg_data
4343 , p_lpn => l_lpn
4344 ) ;
4345
4346
4347 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
4348 x_return_status = FND_API.g_ret_sts_error THEN
4349
4350 IF (l_debug = 1) THEN
4351 trace('device_confirmation: modify_lpn Unexpected error');
4352 END IF;
4353 FND_MESSAGE.SET_NAME('WMS','WMS_TD_MODIFY_LPN_ERROR' );
4354 FND_MSG_PUB.ADD;
4355 --rollback the batch
4356 ROLLBACK TO wms_device_requests_sp_outer;
4357
4358 END IF;
4359
4360 END LOOP;
4361
4362 END IF;
4363
4364 IF (l_debug = 1) THEN
4365 trace('calling populate_history in WDRH for all records' );
4366 END IF;
4367 populate_history(l_new_request_id);
4368
4369 IF (l_debug = 1) THEN
4370 trace(' device_confirmation:Delete requested rows from WDR');
4371 END IF;
4372 delete from wms_device_requests;--since temp table is session specific
4373
4374 --Commit will be done by calling api
4375 IF (l_debug = 1) THEN
4376 trace('device_confirmation:done with this API');
4377 END IF;
4378
4379 EXCEPTION
4380 WHEN FND_API.g_exc_error THEN
4381 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
4382 FND_MSG_PUB.ADD;
4383 --since all original records have been deleted from WDRH before making this
4384 --call
4385 IF (l_debug = 1) THEN
4386 trace('calling populate_history for all records' );
4387 END IF;
4388 populate_history(l_new_request_id);
4389
4390 IF p_request_id IS NOT NULL THEN---p_request_id is not null only for
4391 --resubmission, it is null for
4392 --normal case
4393 IF (l_debug = 1) THEN
4394 trace('Updating resubmitted records for EXP exception thrown' );
4395 END IF;
4396 --this is set to P in the form while making call to concurrent req
4397 UPDATE wms_device_requests_hist
4398 SET status_code = 'E',resubmit_date = sysdate,
4399 status_msg= 'g_expected_error'
4400 WHERE request_id = l_new_request_id
4401 AND BUSINESS_EVENT_ID IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm);
4402
4403 COMMIT;
4404 ELSE --Bug#4535546. Added ELSE block.
4405 ROLLBACK TO WMS_DEVICE_REQUESTS_SP_OUTER;
4406 END IF;
4407
4408 IF (l_debug = 1) THEN
4409 trace('DEVICE_CONFIRMATION:Error: G_EXC_ERR');
4410 END IF;
4411 -- ROLLBACK TO WMS_DEVICE_REQUESTS_SP_OUTER;Commented for Bug#4535546
4412 x_return_status := FND_API.G_RET_STS_ERROR;
4413
4414 x_msg_data:=GET_MSG_STACK ; --Bug#4535546.
4415
4416 WHEN OTHERS THEN
4417
4418 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
4419 FND_MSG_PUB.ADD;
4420 --since all original records have been deleted from WDRH before making this
4421 --call
4422 IF (l_debug = 1) THEN
4423 trace('calling populate_history for all records' );
4424 END IF;
4425 populate_history(l_new_request_id);
4426
4427 IF p_request_id IS NOT NULL THEN---p_request_id is not null only for
4428 --resubmission, it is null for
4429 --normal case
4430 IF (l_debug = 1) THEN
4431 trace('Updating resubmitted records for UNEXP exception thrown' );
4432 END IF;
4433 --this is set to P in the form while making call to concurrent req
4434 UPDATE wms_device_requests_hist
4435 SET status_code = 'E',resubmit_date = Sysdate,
4436 status_msg='g_unexpected_error'
4437 WHERE request_id = l_new_request_id
4438 AND BUSINESS_EVENT_ID IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm);
4439
4440 COMMIT;
4441 ELSE --Bug#4535546. Added ELSE block.
4442 ROLLBACK TO WMS_DEVICE_REQUESTS_SP_OUTER;
4443 END IF;
4444
4445 IF (l_debug = 1) THEN
4446 trace('DEVICE_CONFIRMATION:Error: G_UNEXC_ERR');
4447 END IF;
4448 -- ROLLBACK TO WMS_DEVICE_REQUESTS_SP_OUTER; Commented for Bug#4535546
4449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4450
4451 x_msg_data:=GET_MSG_STACK ; --Bug#4535546.
4452
4453 END device_confirmation;
4454
4455
4456 END wms_device_confirmation_pub;
4457
[Home] [Help]