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