1 PACKAGE BODY inv_receiving_transaction AS
2 /* $Header: INVRCVFB.pls 120.8.12010000.2 2008/07/29 12:54:37 ptkumar ship $*/
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'inv_receiving_transaction';
6
7
8
9 PROCEDURE print_debug(p_err_msg VARCHAR2,
10 p_level NUMBER)
11 IS
12 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
13 BEGIN
14 IF (l_debug = 1) THEN
15 inv_mobile_helper_functions.tracelog
16 (p_err_msg => p_err_msg,
17 p_module => 'inv_receiving_transaction',
18 p_level => p_level);
19 END IF;
20
21 END print_debug;
22
23
24
25 PROCEDURE create_errors(p_group_id IN NUMBER,
26 p_msg IN VARCHAR2)
27 IS
28 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
29 BEGIN
30 IF (l_debug = 1) THEN
31 print_debug('Enter create_errors : 10:'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
32 print_debug('Parameters passed : 10.1: p_msg - '||p_msg, 4);
33 END IF;
34
35 INSERT INTO po_interface_errors
36 (interface_type,
37 interface_transaction_id,
38 error_message,
39 processing_date,
40 creation_date,
41 created_by,
42 last_update_date,
43 last_updated_by,
44 last_update_login)
45 SELECT 'RECEIVING',
46 rti.interface_transaction_id,
47 p_msg,
48 sysdate,
49 rti.creation_date,
50 rti.created_by,
51 rti.last_update_date,
52 rti.last_updated_by,
53 rti.last_update_login
54 FROM rcv_transactions_interface rti
55 WHERE rti.group_id = p_group_id;
56
57 UPDATE rcv_transactions_interface
58 SET processing_status_code = 'COMPLETED',
59 transaction_status_code = 'ERROR'
60 WHERE group_id = p_group_id;
61
62 IF (l_debug = 1) THEN
63 print_debug('Exit create_errors : 10:'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
64 END IF;
65 EXCEPTION
66 WHEN no_data_found THEN
67 NULL;
68 WHEN OTHERS THEN
69 IF (l_debug = 1) THEN
70 print_debug('Exit create_errors with exception : 10:'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
71 END IF;
72 END create_errors;
73
74
75
76 -- Need to make sure RTI has not been deleted before calling
77 -- this cleanup api
78 PROCEDURE rcv_txn_clean_up
79 ( x_return_status OUT NOCOPY VARCHAR2,
80 x_msg_count OUT NOCOPY NUMBER,
81 x_msg_data OUT NOCOPY VARCHAR2,
82 p_group_id IN NUMBER)
83 IS
84 l_lpn_id NUMBER;
85 l_inventory_item_id NUMBER;
86 l_revision VARCHAR(3);
87 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
88 l_lot_number VARCHAR2(80);
89 l_serial_number VARCHAR2(30);
90 l_quantity NUMBER;
91 l_uom_code VARCHAR2(3);
92 l_organization_id NUMBER;
93 l_po_line_id NUMBER;
94 l_po_release_id NUMBER;
95 l_source_line_id NUMBER;
96 l_group_id NUMBER;
97 l_from_organization_id NUMBER;
98 l_receipt_source_code VARCHAR2(25);
99 l_source_document_code VARCHAR2(25);
100 l_serial_control_at_from_org NUMBER;
101 l_source_name varchar2(30);
102
103 l_progress VARCHAR2(10);
104
105 CURSOR lpn_pack_histroy_cur
106 IS
107 SELECT wlh.parent_lpn_id
108 , wlh.inventory_item_id
109 , wlh.revision
110 , wlh.lot_number
111 , wlh.serial_number
112 , wlh.quantity
113 , wlh.uom_code
114 , wlh.organization_id
115 , wlh.source_name
116 FROM wms_lpn_histories wlh
117 WHERE wlh.source_header_id = p_group_id;
118
119 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
120 BEGIN
121 x_return_status := FND_API.G_RET_STS_SUCCESS;
122 IF (l_debug = 1) THEN
123 print_debug('rcv_txn_clean_up entered 10'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
124 print_debug('rcv_txn_clean_up 10.1: Parameters passed : p_group_id - '||p_group_id, 4);
125 END IF;
126 SAVEPOINT rcv_cleanup_sp;
127
128 l_progress := '10';
129
130 OPEN lpn_pack_histroy_cur;
131
132 l_progress := '20';
133
134 LOOP
135 l_progress := '30';
136 FETCH lpn_pack_histroy_cur INTO
137 l_lpn_id,
138 l_inventory_item_id,
139 l_revision,
140 l_lot_number,
141 l_serial_number,
142 l_quantity,
143 l_uom_code,
144 l_organization_id,
145 l_source_name;
146
147 l_progress := '40';
148
149 EXIT WHEN lpn_pack_histroy_cur%notfound;
150
151 -- unpack the lpn
152 IF (l_debug = 1) THEN
153 print_debug('rcv_txn_clean_up 20: We need to undo the pack/unpack changes ',4);
154 print_debug('l_source_name='||l_source_name, 4); --Bug 4611237
155 END IF;
156
157 if l_source_name = 'ASNEXP'
158 then
159 IF (l_debug = 1) THEN
160 print_debug('rcv_txn_clean_up Cleanup for ASNEXP Receive Case - ',1);
161 END IF;
162 UPDATE wms_license_plate_numbers
163 SET lpn_context = 7
164 WHERE lpn_id = l_lpn_id;
165 --Begin Bug 4611237
166 elsif l_source_name = 'INTEXP'
167 then
168 IF (l_debug = 1) THEN
169 print_debug('rcv_txn_clean_up Cleanup for INTEXP Receive Case',1);
170 print_debug('l_lpn_id='||l_lpn_id,1);
171 END IF;
172
173 UPDATE wms_license_plate_numbers
174 SET lpn_context = 6,
175 organization_id = l_organization_id
176 WHERE lpn_id = l_lpn_id;
177
178 UPDATE wms_lpn_contents
179 SET organization_id = l_organization_id
180 WHERE parent_lpn_id = l_lpn_id;
181
182 UPDATE mtl_serial_numbers
183 SET current_organization_id = l_organization_id,
184 group_mark_id = null,
185 current_subinventory_code = null,
186 current_locator_id = null
187 WHERE lpn_id = l_lpn_id;
188
189 IF (l_debug = 1) THEN
190 print_debug('After rcv_txn_clean_up Cleanup for INTEXP Receive Case',1);
191 END IF;
192 --End Bug 4611237
193 else
194 IF (l_debug = 1) THEN
195 print_debug('rcv_txn_clean_up Cleanup for NON ASNEXP Receive Case - ',1);
196 END IF;
197 WMS_Container_PUB.PackUnpack_Container
198 (p_api_version => 1.0,
199 x_return_status => x_return_status,
200 x_msg_count => x_msg_count,
201 x_msg_data => x_msg_data,
202 p_lpn_id => l_lpn_id,
203 p_content_lpn_id => NULL,
204 p_content_item_id => l_inventory_item_id,
205 p_content_item_desc => NULL,
206 p_revision => l_revision,
207 p_lot_number => l_lot_number,
208 p_from_serial_number => l_serial_number,
209 p_to_serial_number => l_serial_number,
210 p_quantity => l_quantity,
211 p_uom => l_uom_code,
212 p_organization_id => l_organization_id,
213 p_subinventory => NULL,
214 p_locator_id => NULL,
215 p_enforce_wv_constraints => NULL,
216 p_operation => 2, -- unpack flag
217 p_cost_group_id => NULL,
218 p_source_type_id => NULL,
219 p_source_header_id => NULL,
220 p_source_name => NULL,
221 p_source_line_id => NULL,
222 p_source_line_detail_id => NULL,
223 p_homogeneous_container => NULL,
224 p_match_locations => NULL,
225 p_match_lpn_context => NULL,
226 p_match_lot => NULL,
227 p_match_cost_groups =>NULL,
228 p_match_mtl_status => NULL
229 );
230 end if;
231
232 IF x_return_status <> fnd_api.g_ret_sts_success THEN
233 IF (l_debug = 1) THEN
234 print_debug('rcv_txn_clean_up 30: Could not undo the pack/unpack changes, exitting - '||x_msg_data,1);
235 END IF;
236 RAISE FND_API.g_exc_error; --RETURN;
237 END IF;
238 IF (l_debug = 1) THEN
239 print_debug('rcv_txn_clean_up 30.1: pack/unpack done',1);
240 END IF;
241
242 -- revert serial number changes if there's any
243 -- and revert serial attributes
244 IF l_serial_number IS NOT NULL THEN
245 IF (l_debug = 1) THEN
246 print_debug('rcv_txn_clean_up 30.3: revert serial number ' || l_serial_number,1);
247 END IF;
248
249 l_progress := '50';
250
251 l_from_organization_id := NULL;
252
253 SELECT from_organization_id
254 , receipt_source_code
255 , source_document_code
256 INTO l_from_organization_id
257 , l_receipt_source_code
258 , l_source_document_code
259 FROM rcv_transactions_interface
260 WHERE group_id = p_group_id
261 AND ROWNUM < 2;
262
263 l_progress := '55';
264
265 IF l_source_name = 'ASNEXP'
266 THEN
267 l_progress := '55.1';
268 IF (l_debug = 1) THEN
269 print_debug('rcv_txn_clean_up - Case for Serial ASNEXP cleanup ',1);
270 END IF;
271
272 UPDATE mtl_serial_numbers
273 SET current_status = Nvl(previous_status, current_status)
274 , group_mark_id = -1
275 , previous_status = NULL
276 WHERE inventory_item_id = l_inventory_item_id
277 AND serial_number = l_serial_number
278 AND current_organization_id = l_organization_id;
279
280 ELSE
281 IF (l_debug = 1) THEN
282 print_debug('rcv_txn_clean_up - Case for Serial NON ASNEXP cleanup ',1);
283 END IF;
284 IF ((l_receipt_source_code = 'INVENTORY'
285 AND l_source_document_code = 'INVENTORY')
286 OR (l_receipt_source_code = 'INTERNAL ORDER'
287 AND l_source_document_code = 'REQ')) THEN
288 SELECT serial_number_control_code
289 INTO l_serial_control_at_from_org
290 FROM mtl_system_items
291 WHERE inventory_item_id = l_inventory_item_id
292 AND organization_id = l_from_organization_id;
293 ELSE
294 -- delete if it is a newly created dynamic serial
295 DELETE mtl_serial_numbers
296 WHERE inventory_item_id = l_inventory_item_id
297 AND serial_number = l_serial_number
298 AND current_organization_id = l_organization_id
299 AND previous_status IS NULL;
300 END IF;
301
302 l_progress := '60';
303 -- revert its previous status otherwise
304 UPDATE mtl_serial_numbers
305 SET current_status = Nvl(previous_status, current_status)
306 , group_mark_id = -1 -- This line and next line for Bug#2368323
307 , current_organization_id = Decode(previous_status, NULL,
308 Decode(l_serial_control_at_from_org,
309 1, current_organization_id,
310 6, current_organization_id,
311 Nvl (l_from_organization_id,current_organization_id)),
312 current_organization_id)
313 , previous_status = NULL
314 WHERE inventory_item_id = l_inventory_item_id
315 AND serial_number = l_serial_number
316 AND current_organization_id = l_organization_id;
317
318 END IF;
319
320 l_progress := '70';
321
322 END IF;
323
324 END LOOP;
325
326 l_progress := '80';
327
328 CLOSE lpn_pack_histroy_cur;
329
330 -- Delete MO line(s) that are for the RTI that errors out
331 -- It is populated when MOL is created.
332 IF (l_debug = 1) THEN
333 print_debug('rcv_txn_clean_up 40: delete MO Lines RTI ',4);
334 END IF;
335 l_progress := '90';
336
337 DELETE mtl_txn_request_lines
338 WHERE line_id IN
339 (SELECT line_id
340 FROM rcv_transactions_interface rti
341 , mtl_txn_request_lines mol
342 WHERE rti.group_id = p_group_id
343 AND mol.txn_source_id = rti.interface_transaction_id
344 AND mol.organization_id = rti.to_organization_id
345 AND mol.inventory_item_id = rti.item_id);
346
347 l_progress := '100';
348
349 IF (l_debug = 1) THEN
350 print_debug('rcv_txn_clean_up 50 complete ',4);
351 END IF;
352
353 EXCEPTION
354
355 WHEN FND_API.g_exc_error THEN
356 IF (l_debug = 1) THEN
357 print_debug('rcv_txn_clean_up: Execution error',4);
358 END IF;
359 ROLLBACK TO rcv_cleanup_sp;
360 CLOSE lpn_pack_histroy_cur;
361
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
364
365 WHEN OTHERS THEN
366 IF (l_debug = 1) THEN
367 print_debug('rcv_txn_clean_up: Other Exception',4);
368 END IF;
369 ROLLBACK TO rcv_cleanup_sp;
370 CLOSE lpn_pack_histroy_cur;
371
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
373
374 IF SQLCODE IS NOT NULL THEN
375 inv_mobile_helper_functions.sql_error('inv_receiving_transaction.rcv_txn_clean_up', l_progress, SQLCODE);
376 IF (l_debug = 1) THEN
377 print_debug('rcv_txn_clean_up : l_progress = ' || l_progress ||' SQLCODE is '||SQLCODE,4);
378 END IF;
379 END IF;
380 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
381
382 END rcv_txn_clean_up;
383
384
385
386 PROCEDURE txn_complete(p_group_id IN NUMBER,
387 p_txn_status IN VARCHAR2, -- TRUE/FALSE
388 p_txn_mode IN VARCHAR2, -- ONLINE/IMMEDIATE
389 x_return_status OUT NOCOPY VARCHAR2,
390 x_msg_data OUT NOCOPY VARCHAR2,
391 x_msg_count OUT NOCOPY NUMBER)
392 IS
393 l_transaction_type VARCHAR2(100);
394 l_error_code NUMBER;
395 l_prev_lpn_group_id NUMBER;
396 l_txn_mode_code VARCHAR2(25);--BUG 5090595
397
398 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
399 BEGIN
400 x_return_status := fnd_api.g_ret_sts_success;
401 IF (l_debug = 1) THEN
402 print_debug('TXN_COMPLETE - Enter txn_complete : 10: '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
403 print_debug('TXN_COMPLETE - Parameters passed : 10.1: p_group_id - '||p_group_id, 4);
404 print_debug('TXN_COMPLETE - Parameters passed : 10.2: p_txn_status - '||p_txn_status, 4);
405 print_debug('TXN_COMPLETE - Parameters passed : 10.3: p_txn_mode - '||p_txn_mode, 4);
406 END IF;
407
408
409 IF (l_debug = 1) THEN
410 print_debug('TXN_COMPLETE - Release Level is J or Above',1);
411 END IF;
412
413 IF (p_txn_status = 'FALSE') THEN
414 --BUG 5090595 (FP of BUG 5082146)
415 IF (p_txn_mode = 'ONLINE') THEN
416 l_txn_mode_code := 'ONLINE';
417 ELSE
418 BEGIN
419 SELECT 'ONLINE'
420 INTO l_txn_mode_code
421 FROM dual
422 WHERE exists (SELECT 1
423 FROM rcv_transactions_interface
424 WHERE interface_transaction_id = p_group_id
425 AND processing_mode_code = 'ONLINE');
426 EXCEPTION
427 WHEN OTHERS THEN
428 l_txn_mode_code := NULL;
429 END;
430 END IF;
431
432 IF (l_debug = 1) THEN
433 print_debug('TXN_COMPLETE - PROCESSING MODE:'||l_txn_mode_code,1);
434 END IF;
435
436 IF (l_txn_mode_code = 'ONLINE') THEN
437 IF (l_debug = 1) THEN
438 print_debug('TXN_COMPLETE - Txn Failed for Online mode. Rolling back all work by tm',1);
439 END IF;
440 ROLLBACK WORK;
441 END IF;
442 --END BUG 5090595
443
444 IF (p_txn_mode IN ('PREPROCESSOR','PROCESSOR')) THEN
445 FOR l_rti_rec IN (SELECT interface_transaction_id
446 , transaction_type
447 , mmtt_temp_id
451 , lpn_id
448 , processing_mode_code
449 , parent_transaction_id
450 , item_id
452 , item_revision
453 , item_description
454 , to_organization_id
455 FROM rcv_transactions_interface
456 WHERE interface_transaction_id =
457 p_group_id)
458 LOOP
459 IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
460 IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
461 --Call Cleanup Op Instance
462 wms_atf_runtime_pub_apis.cleanup_operation_instance
463 (x_return_status => x_return_status
464 ,x_msg_data => x_msg_data
465 ,x_msg_count => x_msg_count
466 ,x_error_code => l_error_code
467 ,p_source_task_id => l_rti_rec.mmtt_temp_id
468 ,p_activity_type_id => 1);
469 END IF; --IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
470 END IF; --IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
471
472 --update mol and msn
473
474 --In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
475 --is removed. So when unmarking the wme_process_flag here,
476 --we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
477 --use of rti.mmtt_temp_id if present. If not, we can only
478 --update all MOL for the given org/item combination
479 IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
480 UPDATE mtl_txn_request_lines
481 SET wms_process_flag = 1
482 WHERE line_id = (SELECT move_order_line_id
483 FROM mtl_material_transactions_temp
484 WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
485 ELSIF (l_rti_rec.lpn_id IS NOT NULL) THEN
486 UPDATE mtl_txn_request_lines
487 SET wms_process_flag = 1
488 WHERE organization_id = l_rti_rec.to_organization_id
489 AND lpn_id = l_rti_rec.lpn_id
490 AND wms_process_flag = 2;
491 ELSE
492 UPDATE mtl_txn_request_lines
493 SET wms_process_flag = 1
494 WHERE organization_id = l_rti_rec.to_organization_id
495 AND inventory_item_id = l_rti_rec.item_id
496 AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!')
497 AND wms_process_flag = 2;
498 --AND txn_source_id = l_rti_rec.parent_transaction_id;
499 END IF;--IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
500
501 IF (l_debug = 1) THEN
502 print_debug('Number of MOL updated: '||SQL%rowcount,4);
503 END IF;
504 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
505 along with group_mark_id */
506 -- Bug 6869089
507 update /*+ ROWID */ mtl_serial_numbers msn
508 set group_mark_id = NULL,
509 line_mark_id = NULL,
510 lot_line_mark_id = NULL
511 where msn.ROWID in ( select msn1.ROWID
512 from mtl_serial_numbers msn1 ,
513 mtl_serial_numbers_interface msni
514 where msn1.inventory_item_id = l_rti_rec.item_id
515 and msni.product_code = 'RCV'
516 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
517 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
518 and length(msn1.serial_number) = length(msni.fm_serial_number)
519 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
520
521 --MSNI could have been moved to MSNT
522 update /*+ ROWID */ mtl_serial_numbers msn
523 set group_mark_id = NULL,
524 line_mark_id = NULL,
525 lot_line_mark_id = NULL
526 where msn.ROWID in ( select msn1.ROWID
527 from mtl_serial_numbers msn1 ,
528 mtl_serial_numbers_temp msnt
529 where msn1.inventory_item_id = l_rti_rec.item_id
530 and msnt.product_code = 'RCV'
531 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
532 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
533 and length(msn1.serial_number) = length(msnt.fm_serial_number)
534 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
535
536 END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
537 ELSIF (p_txn_mode = 'LPN_GROUP') THEN --IF (p_txn_mode IN ('PREPROCESSOR','PROCESSOR')) THEN
538 FOR l_rti_rec IN (SELECT interface_transaction_id
539 , transaction_type
540 , mmtt_temp_id
541 , processing_mode_code
542 , parent_transaction_id
543 , item_id
544 , to_organization_id
545 , lpn_id
546 , item_description
547 , item_revision
548 FROM rcv_transactions_interface
549 WHERE lpn_group_id =
550 p_group_id)
551 LOOP
552 IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
553 IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
554 --Call Cleanup Op Instance
555 wms_atf_runtime_pub_apis.cleanup_operation_instance
556 (x_return_status => x_return_status
557 ,x_msg_data => x_msg_data
561 ,p_activity_type_id => 1);
558 ,x_msg_count => x_msg_count
559 ,x_error_code => l_error_code
560 ,p_source_task_id => l_rti_rec.mmtt_temp_id
562 END IF; --IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
563 END IF; --IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
564
565 --update mol and msn
566
567 --In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
568 --is removed. So when unmarking the wme_process_flag here,
569 --we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
570 --use of rti.mmtt_temp_id if present. If not, we can only
571 --update all MOL for the given org/item combination
572 IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
573 UPDATE mtl_txn_request_lines
574 SET wms_process_flag = 1
575 WHERE line_id = (SELECT move_order_line_id
576 FROM mtl_material_transactions_temp
577 WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
578 ELSIF (l_rti_rec.lpn_id IS NOT NULL) THEN
579 UPDATE mtl_txn_request_lines
580 SET wms_process_flag = 1
581 WHERE organization_id = l_rti_rec.to_organization_id
582 AND lpn_id = l_rti_rec.lpn_id
583 AND wms_process_flag = 2;
584 ELSE
585 UPDATE mtl_txn_request_lines
586 SET wms_process_flag = 1
587 WHERE organization_id = l_rti_rec.to_organization_id
588 AND inventory_item_id = l_rti_rec.item_id
589 AND wms_process_flag = 2
590 AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!');
591 --AND txn_source_id = l_rti_rec.parent_transaction_id;
592 END IF;--IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
593
594 IF (l_debug = 1) THEN
595 print_debug('Number of MOL updated: '||SQL%rowcount,4);
596 END IF;
597
598 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
599 along with group_mark_id */
600 -- Bug 6869089
601 update /*+ ROWID */ mtl_serial_numbers msn
602 set group_mark_id = NULL,
603 line_mark_id = NULL,
604 lot_line_mark_id = NULL
605 where msn.ROWID in ( select msn1.ROWID
606 from mtl_serial_numbers msn1 ,
607 mtl_serial_numbers_interface msni
608 where msn1.inventory_item_id = l_rti_rec.item_id
609 and msni.product_code = 'RCV'
610 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
611 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
612 and length(msn1.serial_number) = length(msni.fm_serial_number)
613 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
614
615 --MSNI could have been moved to MSNT
616 update /*+ ROWID */ mtl_serial_numbers msn
617 set group_mark_id = NULL,
618 line_mark_id = NULL,
619 lot_line_mark_id = NULL
620 where msn.ROWID in ( select msn1.ROWID
621 from mtl_serial_numbers msn1 ,
622 mtl_serial_numbers_temp msnt
623 where msn1.inventory_item_id = l_rti_rec.item_id
624 and msnt.product_code = 'RCV'
625 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
626 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
627 and length(msn1.serial_number) = length(msnt.fm_serial_number)
628 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
629
630
631 END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
632 ELSIF (p_txn_mode = 'HEADER') THEN --IF (p_txn_mode IN ('PREPROCESSOR','PROCESSOR')) THEN
633 FOR l_rti_rec IN (SELECT interface_transaction_id
634 , transaction_type
635 , mmtt_temp_id
636 , processing_mode_code
637 , parent_transaction_id
638 , item_id
639 , to_organization_id
640 , lpn_id
641 , item_description
642 , item_revision
643 FROM rcv_transactions_interface
644 WHERE header_interface_id = p_group_id)
645 LOOP
646 IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
647 IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
648 --Call Cleanup Op Instance
649 wms_atf_runtime_pub_apis.cleanup_operation_instance
650 (x_return_status => x_return_status
651 ,x_msg_data => x_msg_data
652 ,x_msg_count => x_msg_count
653 ,x_error_code => l_error_code
654 ,p_source_task_id => l_rti_rec.mmtt_temp_id
655 ,p_activity_type_id => 1);
656 END IF; --IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
657 END IF; --IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
658
659 --update mol and msn
660
661 --In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
662 --is removed. So when unmarking the wme_process_flag here,
663 --we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
667 UPDATE mtl_txn_request_lines
664 --use of rti.mmtt_temp_id if present. If not, we can only
665 --update all MOL for the given org/item combination
666 IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
668 SET wms_process_flag = 1
669 WHERE line_id = (SELECT move_order_line_id
670 FROM mtl_material_transactions_temp
671 WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
672 ELSIF (l_rti_rec.lpn_id IS NOT NULL) THEN
673 UPDATE mtl_txn_request_lines
674 SET wms_process_flag = 1
675 WHERE organization_id = l_rti_rec.to_organization_id
676 AND lpn_id = l_rti_rec.lpn_id
677 AND wms_process_flag = 2;
678 ELSE
679 UPDATE mtl_txn_request_lines
680 SET wms_process_flag = 1
681 WHERE organization_id = l_rti_rec.to_organization_id
682 AND inventory_item_id = l_rti_rec.item_id
683 AND wms_process_flag = 2
684 AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!');
685 --AND txn_source_id = l_rti_rec.parent_transaction_id;
686 END IF;--IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
687
688 IF (l_debug = 1) THEN
689 print_debug('Number of MOL updated: '||SQL%rowcount,4);
690 END IF;
691 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
692 along with group_mark_id */
693 -- Bug 6869089
694 update /*+ ROWID */ mtl_serial_numbers msn
695 set group_mark_id = NULL,
696 line_mark_id = NULL,
697 lot_line_mark_id = NULL
698 where msn.ROWID in ( select msn1.ROWID
699 from mtl_serial_numbers msn1 ,
700 mtl_serial_numbers_interface msni
701 where msn1.inventory_item_id = l_rti_rec.item_id
702 and msni.product_code = 'RCV'
703 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
704 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
705 and length(msn1.serial_number) = length(msni.fm_serial_number)
706 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
707
708 -- MSNI could have been moved to MSNT
709 update /*+ ROWID */ mtl_serial_numbers msn
710 set group_mark_id = NULL,
711 line_mark_id = NULL,
712 lot_line_mark_id = NULL
713 where msn.ROWID in ( select msn1.ROWID
714 from mtl_serial_numbers msn1 ,
715 mtl_serial_numbers_temp msnt
716 where msn1.inventory_item_id = l_rti_rec.item_id
717 and msnt.product_code = 'RCV'
718 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
719 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
720 and length(msn1.serial_number) = length(msnt.fm_serial_number)
721 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
722
723 END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
724 ELSE --IF (p_txn_mode IN ('PREPROCESSOR','PROCESSOR')) THEN
725 l_prev_lpn_group_id := 0;
726 FOR l_rti_rec IN (SELECT interface_transaction_id
727 , transaction_type
728 , mmtt_temp_id
729 , processing_mode_code
730 , parent_transaction_id
731 , item_id
732 , to_organization_id
733 , lpn_group_id
734 , lpn_id
735 , item_description
736 , item_revision
737 FROM rcv_transactions_interface
738 WHERE group_id =
739 p_group_id)
740 LOOP
741 IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
742 IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
743 --Call Cleanup Op Instance
744 wms_atf_runtime_pub_apis.cleanup_operation_instance
745 (x_return_status => x_return_status
746 ,x_msg_data => x_msg_data
747 ,x_msg_count => x_msg_count
748 ,x_error_code => l_error_code
749 ,p_source_task_id => l_rti_rec.mmtt_temp_id
750 ,p_activity_type_id => 1);
751 END IF; --IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
752 END IF; --IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
753
754 --update mol and msn
755
756 --In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
757 --is removed. So when unmarking the wme_process_flag here,
758 --we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
759 --use of rti.mmtt_temp_id if present. If not, we can only
760 --update all MOL for the given org/item combination
761 IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
762 UPDATE mtl_txn_request_lines
763 SET wms_process_flag = 1
764 WHERE line_id = (SELECT move_order_line_id
765 FROM mtl_material_transactions_temp
766 WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
767 ELSIF (l_rti_rec.lpn_id IS NOT NULL) THEN
768 UPDATE mtl_txn_request_lines
769 SET wms_process_flag = 1
773 ELSE
770 WHERE organization_id = l_rti_rec.to_organization_id
771 AND lpn_id = l_rti_rec.lpn_id
772 AND wms_process_flag = 2;
774 UPDATE mtl_txn_request_lines
775 SET wms_process_flag = 1
776 WHERE organization_id = l_rti_rec.to_organization_id
777 AND inventory_item_id = l_rti_rec.item_id
778 AND wms_process_flag = 2
779 AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!');
780 --AND txn_source_id = l_rti_rec.parent_transaction_id;
781 END IF;--IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
782
783 IF (l_debug = 1) THEN
784 print_debug('Number of MOL updated: '||SQL%rowcount,4);
785 END IF;
786 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
787 along with group_mark_id */
788 -- Bug 6869089
789 update /*+ ROWID */ mtl_serial_numbers msn
790 set group_mark_id = NULL,
791 line_mark_id = NULL,
792 lot_line_mark_id = NULL
793 where msn.ROWID in ( select msn1.ROWID
794 from mtl_serial_numbers msn1 ,
795 mtl_serial_numbers_interface msni
796 where msn1.inventory_item_id = l_rti_rec.item_id
797 and msni.product_code = 'RCV'
798 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
799 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
800 and length(msn1.serial_number) = length(msni.fm_serial_number)
801 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
802
803 -- MSNI could have been moved to MSNT
804 update /*+ ROWID */ mtl_serial_numbers msn
805 set group_mark_id = NULL,
806 line_mark_id = NULL,
807 lot_line_mark_id = NULL
808 where msn.ROWID in ( select msn1.ROWID
809 from mtl_serial_numbers msn1 ,
810 mtl_serial_numbers_temp msnt
811 where msn1.inventory_item_id = l_rti_rec.item_id
812 and msnt.product_code = 'RCV'
813 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
814 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
815 and length(msn1.serial_number) = length(msnt.fm_serial_number)
816 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
817
818 -- Delete WLPNI/MSNI/MSNT/MTLI/MTLT
819 -- Commenting the following as PO never deletes the RTI row.
820 /* Bug 4901912 - Uncommenting the deleting of the interface and temp tables as
821 also deleting the rti if in the online mode. */
822
823 IF (l_rti_rec.processing_mode_code = 'ONLINE' ) THEN
824 IF (l_debug = 1) THEN
825 print_debug('TXN_COMPLETE - Deleting mtli, msni, mtlt, msnt, wlpni for interface id:'
826 || l_rti_rec.interface_transaction_id ,1);
827 END IF;
828
829 DELETE FROM mtl_transaction_lots_interface
830 WHERE product_code = 'RCV'
831 AND product_transaction_id = l_rti_rec.interface_transaction_id;
832
833 DELETE FROM mtl_transaction_lots_temp
834 WHERE product_code = 'RCV'
835 AND product_transaction_id = l_rti_rec.interface_transaction_id;
836
837 DELETE FROM mtl_serial_numbers_interface
838 WHERE product_code = 'RCV'
839 AND product_transaction_id = l_rti_rec.interface_transaction_id;
840
841 DELETE FROM mtl_serial_numbers_temp
842 WHERE product_code = 'RCV'
843 AND product_transaction_id = l_rti_rec.interface_transaction_id;
844
845 IF (l_prev_lpn_group_id <> l_rti_rec.lpn_group_id) THEN
846 l_prev_lpn_group_id := l_rti_rec.lpn_group_id;
847
848 DELETE FROM wms_lpn_interface
849 WHERE source_group_id = l_rti_rec.lpn_group_id;
850 END IF; --IF (l_prev_lpn_group_id <> l_rti_rec.lpn_group_id) THEN
851 END IF; -- l_rti_rec.processing_mode_code = 'ONLINE'
852
853 /* End of fix for Bug 4901912 */
854
855 END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
856 END IF; --IF (p_txn_mode IN ('PREPROCESSOR','PROCESSOR')) THEN
857 COMMIT;
858 ELSE --IF (p_txn_status = 'FALSE') THEN
859 BEGIN
860 IF p_txn_mode = 'LPN_GROUP' THEN
861 SELECT transaction_type
862 INTO l_transaction_type
863 FROM rcv_transactions
864 WHERE lpn_group_id = p_group_id
865 AND transaction_date >= (Sysdate - 1) --BUG 3444137: RT
866 --will have INDEX ON transaction_date AND lpn_group_id
867 AND transaction_type IN ('CORRECT','RETURN TO VENDOR',
868 'RETURN TO RECEIVING','RETURN TO CUSTOMER')
869 AND ROWNUM < 2;
870 ELSE
871 SELECT transaction_type
872 INTO l_transaction_type
873 FROM rcv_transactions
874 WHERE group_id = p_group_id
875 AND transaction_type IN ('CORRECT','RETURN TO VENDOR',
876 'RETURN TO RECEIVING','RETURN TO CUSTOMER')
877 AND ROWNUM < 2;
878 END IF; --IF p_txn_mode = 'LPN_GROUP' THEN
879 EXCEPTION
880 WHEN no_data_found THEN
881 IF (l_debug = 1) THEN
882 print_debug('No records matched in RT for group_id - '||p_group_id||' : 100',1);
883 END IF;
884 RETURN;
885 END;
886
887 --Must call wms_return_sv.txn_complete to take care of deleting
888 --reservations for non-express case.
889 IF l_transaction_type in ('CORRECT','RETURN TO VENDOR','RETURN TO RECEIVING','RETURN TO CUSTOMER') THEN
890
891 x_return_status := fnd_api.g_ret_sts_success;
892 wms_return_sv.txn_complete(
893 p_group_id => p_group_id,
894 p_txn_status => p_txn_status,
895 p_txn_mode => p_txn_mode,
896 x_return_status => x_return_status,
897 x_msg_data => x_msg_data,
898 x_msg_count => x_msg_count);
899
900 IF ( x_return_status = fnd_api.g_ret_sts_error OR
901 x_return_status = fnd_api.g_ret_sts_unexp_error ) THEN
902 IF (l_debug = 1) THEN
903 print_debug('Error return from wms_return_sv.txn_complete, exitting - '||x_msg_data||' : 101',1);
904 END IF;
905 IF p_txn_mode <> 'ONLINE' THEN
906 create_errors(p_group_id => p_group_id,
907 p_msg => 'inv_receiving_transaction.txn_complete - 102 -'||x_msg_data);
908 END IF;
909 IF x_return_status = fnd_api.g_ret_sts_error THEN
910 RAISE fnd_api.g_exc_error;
911 ELSE
912 RAISE fnd_api.g_exc_unexpected_error;
913 END IF;
914 END IF;
915
916 x_return_status := fnd_api.g_ret_sts_success;
917
918 END IF; --IF l_transaction_type in ('CORRECT','RETURN TO VENDOR','RETURN TO RECEIVING','RETURN TO CUSTOMER') THEN
919
920 END IF; --IF (p_txn_status = 'FALSE') THEN
921
922 --Begin bug 4611237
923 --Delete records from WLPNH with context 6, operation_mode of -99999 and
924 --group_id passed to txn_complete api.
925
926 IF (l_debug = 1) THEN
927 print_debug('Delete records from WLPN with context 6, operation_mode of -99999 and group_id', 4);
928 END IF;
929
930 DELETE FROM wms_lpn_histories
931 WHERE source_header_id = p_group_id
932 AND lpn_context = 6
933 AND operation_mode = -99999;
934
935 --End bug 4611237
936 EXCEPTION
937 WHEN fnd_api.g_exc_error THEN
938 IF (l_debug = 1) THEN
939 print_debug('Execution error in txn_complete',4);
940 END IF;
941 x_return_status := fnd_api.g_ret_sts_error;
942 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
943
944 WHEN fnd_api.g_exc_unexpected_error THEN
945 IF (l_debug = 1) THEN
946 print_debug('Unexpected error in txn_complete',4);
947 END IF;
948 x_return_status := fnd_api.g_ret_sts_unexp_error ;
949 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
950
951 WHEN OTHERS THEN
952 IF (l_debug = 1) THEN
956 --
953 print_debug('Exception in txn_complete',4);
954 END IF;
955 x_return_status := fnd_api.g_ret_sts_unexp_error ;
957 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
958 THEN
959 fnd_msg_pub.add_exc_msg
960 ( g_pkg_name
961 , 'txn_complete'
962 );
963 END IF;
964
965 -- Get message count and data
966 --fnd_msg_pub.count_and_get
967 -- ( p_count => x_msg_count
968 -- , p_data => x_msg_data
969 -- );
970 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
971
972 END txn_complete;
973
974 PROCEDURE txn_mobile_timeout_cleanup(p_group_id IN NUMBER,
975 p_rti_rec_count IN NUMBER,
976 x_return_status OUT NOCOPY VARCHAR2,
977 x_msg_data OUT NOCOPY VARCHAR2,
978 x_msg_count OUT NOCOPY NUMBER)
979 IS
980 CURSOR c_mmtt_txn_temp_id IS
981 SELECT DISTINCT rti.mmtt_temp_id
982 FROM rcv_transactions_interface rti
983 WHERE rti.group_id = p_group_id;
984
985 l_transaction_type VARCHAR2(100);
986 l_organization_id NUMBER;
987 l_mmtt_transaction_temp_id NUMBER;
988 l_wms_install_status VARCHAR2(1);
989 l_return_status VARCHAR2(5);
990 l_msg_data VARCHAR2(500);
991 l_msg_count NUMBER;
992
993
994 l_patch_j_code BOOLEAN := FALSE;
995 l_mobile_txn_count NUMBER;
996
997 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
998 BEGIN
999
1000 x_return_status := fnd_api.g_ret_sts_success;
1001 IF (l_debug = 1) THEN
1002 print_debug('TXN_COMPLETE - Enter txn_complete : 10: '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1003 print_debug('TXN_COMPLETE - Parameters passed : 10.1: p_group_id - '||p_group_id, 4);
1004 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Parameters passed : 10.1: p_rti_rec_count - '||p_rti_rec_count, 4);
1005 END IF;
1006
1007 --setting a parameter to see if the release level is above J or below J.
1008 IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
1009 (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
1010 l_patch_j_code := TRUE;
1011 ELSE
1012 l_patch_j_code := FALSE;
1013 END IF;
1014
1015 IF (l_patch_j_code = FALSE) THEN
1016 IF (l_debug = 1) THEN
1017 print_debug('TXN_COMPLETE - Release level is prior to J',1);
1018 END IF;
1019 -- for a group id, if it is a row from mobile, all rows will have
1020 -- the same mobile_txn, transaction_type, lpn_id, transfer_lpn_id,
1021 -- content_lpn_id
1022 SELECT count(Nvl(rti.mobile_txn, 'N'))
1023 INTO l_mobile_txn_count
1024 FROM rcv_transactions_interface rti
1025 WHERE rti.group_id = p_group_id
1026 AND processing_mode_code = 'ONLINE'
1027 AND processing_status_code = 'PENDING'
1028 AND transaction_status_code = 'PENDING';
1029
1030 IF (l_mobile_txn_count <> p_rti_rec_count) THEN
1031 IF (l_debug = 1) THEN
1032 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP -count does not match - exiting the procedure : 30',1);
1033 END IF;
1034 RETURN;
1035 END IF;
1036
1037 -- set wms_installed flag
1038 IF wms_install.check_install(x_return_status,
1039 x_msg_count,
1040 x_msg_data,
1041 l_organization_id) THEN
1042 l_wms_install_status := 'I';
1043 ELSE
1044 l_wms_install_status := 'U';
1045 END IF;
1046 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1047 IF (l_debug = 1) THEN
1048 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Could not get wms installed status, exitting - '||x_msg_data||' : 40',1);
1049 END IF;
1050 RAISE fnd_api.g_exc_error; --RETURN;
1051 END IF;
1052
1053 IF l_wms_install_status = 'I' THEN
1054
1055 IF (l_debug = 1) THEN
1056 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - WMS is installed : 50',4);
1057 END IF;
1058
1059 IF l_transaction_type = 'DELIVER' THEN
1060 IF (l_debug = 1) THEN
1061 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Transaction type is DELIVER : 180',4);
1062 END IF;
1063 BEGIN
1064 OPEN c_mmtt_txn_temp_id;
1065 LOOP -- Loop through all the mmtt recs for this group for crossdocking
1066 IF (l_debug = 1) THEN
1067 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - p_group_id:'||p_group_id,4);
1068 END IF;
1069 FETCH c_mmtt_txn_temp_id
1070 INTO l_mmtt_transaction_temp_id;
1071 EXIT WHEN c_mmtt_txn_temp_id%notfound;
1072 IF (l_debug = 1) THEN
1073 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - mmtt_temp_id:'||l_mmtt_transaction_temp_id,4);
1074 END IF;
1075 -- the records will be there in mmtt only if it was a wms
1076 -- enabled org. For an inventory org, there will be no recs.
1077 -- in mmtt so it will exit out of the loop immediately.
1078 wms_task_dispatch_put_away.putaway_cleanup
1079 ( p_temp_id=>l_mmtt_transaction_temp_id
1080 , p_org_id=>l_organization_id
1081 , x_return_status =>x_return_status
1082 , x_msg_count =>x_msg_count
1083 , x_msg_data =>x_msg_data
1087 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Could not archive tasks, exiting - '||x_msg_data||' : 105',1);
1084 );
1085 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1086 IF (l_debug = 1) THEN
1088 END IF;
1089 END IF;
1090 END LOOP;
1091 CLOSE c_mmtt_txn_temp_id;
1092 END;
1093
1094 BEGIN
1095 UPDATE wms_lpn_contents
1096 SET txn_error_flag = 'Y'
1097 WHERE source_header_id = p_group_id;
1098 EXCEPTION
1099 WHEN no_data_found THEN
1100 NULL;
1101 WHEN OTHERS THEN
1102 IF (l_debug = 1) THEN
1103 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Exception while updating wms_lpn_contents to error : 200',4);
1104 END IF;
1105 END;
1106
1107 BEGIN
1108 UPDATE mtl_serial_numbers
1109 SET lpn_txn_error_flag = 'Y'
1110 WHERE ROWID IN (SELECT msn.ROWID
1111 FROM mtl_serial_numbers msn
1112 , rcv_transactions_interface rti
1113 WHERE msn.last_txn_source_id = p_group_id
1114 AND rti.group_id = p_group_id
1115 AND rti.item_id = msn.inventory_item_id);
1116
1117 EXCEPTION
1118 WHEN no_data_found THEN
1119 NULL;
1120 WHEN OTHERS THEN
1121 IF (l_debug = 1) THEN
1122 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Exception while updating mtl_serial_numbers to error : 210',4);
1123 END IF;
1124 END;
1125
1126 ELSIF l_transaction_type = 'RECEIVE' THEN
1127 IF (l_debug = 1) THEN
1128 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Transaction type was RECEIVE : 220',4);
1129 END IF;
1130
1131 rcv_txn_clean_up
1132 (x_return_status => x_return_status,
1133 x_msg_count => x_msg_count,
1134 x_msg_data => x_msg_data,
1135 p_group_id => p_group_id);
1136 print_debug('rcv_txn_clean_up - Finished clean up : 221',4);
1137 END IF;
1138
1139
1140 -- Delete/Clear mtl_serial_numbers_temp rows
1141 -- Delete/Clear mtl_transaction_lots_temp rows
1142 -- If the Transaction Fails
1143
1144 IF (l_debug = 1) THEN
1145 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - cleanup msnt 1',4);
1146 END IF;
1147
1148
1149 delete from mtl_serial_numbers_temp msnt
1150 where msnt.transaction_temp_id in
1151 ( select interface_transaction_id
1152 from rcv_transactions_interface
1153 where group_id = p_group_id )
1154 ;
1155
1156 IF (l_debug = 1) THEN
1157 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - cleanup msnt 2',4);
1158 END IF;
1159
1160 delete from mtl_serial_numbers_temp msnt
1161 where msnt.transaction_temp_id in
1162 ( select mtlt.serial_transaction_temp_id
1163 from mtl_transaction_lots_temp mtlt
1164 where mtlt.transaction_temp_id in (
1165 select interface_transaction_id
1166 from rcv_transactions_interface
1167 where group_id = p_group_id )
1168 );
1169
1170 IF (l_debug = 1) THEN
1171 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - cleanup mtlt 3',4);
1172 END IF;
1173
1174 delete from mtl_transaction_lots_temp mtlt
1175 where mtlt.transaction_temp_id
1176 in ( select interface_transaction_id
1177 from rcv_transactions_interface
1178 where group_id = p_group_id );
1179
1180 IF (l_debug = 1) THEN
1181 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Committing after rollbacking and cleanup',4);
1182 END IF;
1183 END IF; -- l_wms_install_status = 'I'
1184 IF (l_debug = 1) THEN
1185 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Exiting TXN_MOBILE_TIMEOUT_CLEANUP : 230 '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1186 END IF;
1187 END IF; --IF (l_patch_j_code = FALSE) THEN
1188
1189 EXCEPTION
1190 WHEN fnd_api.g_exc_error THEN
1191 IF (l_debug = 1) THEN
1192 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Execution error in TXN_MOBILE_TIMEOUT_CLEANUP',4);
1193 END IF;
1194 x_return_status := fnd_api.g_ret_sts_error;
1195 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1196
1197 WHEN fnd_api.g_exc_unexpected_error THEN
1198 IF (l_debug = 1) THEN
1199 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Unexpected error in TXN_MOBILE_TIMEOUT_CLEANUP',4);
1200 END IF;
1201 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1202 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1203
1204 WHEN OTHERS THEN
1205 IF (l_debug = 1) THEN
1206 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Exception in TXN_MOBILE_TIMEOUT_CLEANUP',4);
1207 END IF;
1208 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1209 --
1210 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1211 THEN
1212 fnd_msg_pub.add_exc_msg
1213 ( g_pkg_name
1214 , 'txn_complete'
1215 );
1216 END IF;
1217
1218 -- Get message count and data
1219 --fnd_msg_pub.count_and_get
1220 -- ( p_count => x_msg_count
1221 -- , p_data => x_msg_data
1222 -- );
1223 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1224
1225 END txn_mobile_timeout_cleanup;
1226
1227 END inv_receiving_transaction;
[Home] [Help]