1 PACKAGE BODY inv_receiving_transaction AS
2 /* $Header: INVRCVFB.pls 120.9.12020000.2 2012/07/09 08:14:40 asugandh 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 l_ser_ctrl_code NUMBER ; --13564609
398
399 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
400 BEGIN
401 x_return_status := fnd_api.g_ret_sts_success;
402 IF (l_debug = 1) THEN
403 print_debug('TXN_COMPLETE - Enter txn_complete : 10: '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
404 print_debug('TXN_COMPLETE - Parameters passed : 10.1: p_group_id - '||p_group_id, 4);
405 print_debug('TXN_COMPLETE - Parameters passed : 10.2: p_txn_status - '||p_txn_status, 4);
406 print_debug('TXN_COMPLETE - Parameters passed : 10.3: p_txn_mode - '||p_txn_mode, 4);
407 END IF;
408
409
410 IF (l_debug = 1) THEN
411 print_debug('TXN_COMPLETE - Release Level is J or Above',1);
412 END IF;
413
414 IF (p_txn_status = 'FALSE') THEN
415 --BUG 5090595 (FP of BUG 5082146)
416 IF (p_txn_mode = 'ONLINE') THEN
417 l_txn_mode_code := 'ONLINE';
418 ELSE
419 BEGIN
420 SELECT 'ONLINE'
421 INTO l_txn_mode_code
422 FROM dual
423 WHERE exists (SELECT 1
424 FROM rcv_transactions_interface
425 WHERE interface_transaction_id = p_group_id
426 AND processing_mode_code = 'ONLINE');
427 EXCEPTION
428 WHEN OTHERS THEN
429 l_txn_mode_code := NULL;
430 END;
431 END IF;
432
433 IF (l_debug = 1) THEN
434 print_debug('TXN_COMPLETE - PROCESSING MODE:'||l_txn_mode_code,1);
435 END IF;
436
437 IF (l_txn_mode_code = 'ONLINE') THEN
438 IF (l_debug = 1) THEN
439 print_debug('TXN_COMPLETE - Txn Failed for Online mode. Rolling back all work by tm',1);
440 END IF;
441 ROLLBACK WORK;
442 END IF;
443 --END BUG 5090595
444
445 IF (p_txn_mode IN ('PREPROCESSOR','PROCESSOR')) THEN
446 FOR l_rti_rec IN (SELECT interface_transaction_id
447 , transaction_type
448 , mmtt_temp_id
449 , processing_mode_code
450 , parent_transaction_id
451 , item_id
452 , lpn_id
453 , item_revision
454 , item_description
455 , to_organization_id
456 FROM rcv_transactions_interface
457 WHERE interface_transaction_id =
458 p_group_id)
459 LOOP
460 IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
461 IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
462 --Call Cleanup Op Instance
463 wms_atf_runtime_pub_apis.cleanup_operation_instance
464 (x_return_status => x_return_status
465 ,x_msg_data => x_msg_data
466 ,x_msg_count => x_msg_count
467 ,x_error_code => l_error_code
468 ,p_source_task_id => l_rti_rec.mmtt_temp_id
469 ,p_activity_type_id => 1);
470 END IF; --IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
471 END IF; --IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
472
473 --update mol and msn
474
475 --In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
476 --is removed. So when unmarking the wme_process_flag here,
477 --we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
478 --use of rti.mmtt_temp_id if present. If not, we can only
479 --update all MOL for the given org/item combination
480 IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
481 UPDATE mtl_txn_request_lines
482 SET wms_process_flag = 1
483 WHERE line_id = (SELECT move_order_line_id
484 FROM mtl_material_transactions_temp
485 WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
486 ELSIF (l_rti_rec.lpn_id IS NOT NULL) THEN
487 UPDATE mtl_txn_request_lines
488 SET wms_process_flag = 1
489 WHERE organization_id = l_rti_rec.to_organization_id
490 AND lpn_id = l_rti_rec.lpn_id
491 AND wms_process_flag = 2;
492 ELSE
493 UPDATE mtl_txn_request_lines
494 SET wms_process_flag = 1
495 WHERE organization_id = l_rti_rec.to_organization_id
496 AND inventory_item_id = l_rti_rec.item_id
497 AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!')
498 AND wms_process_flag = 2;
499 --AND txn_source_id = l_rti_rec.parent_transaction_id;
500 END IF;--IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
501
502 IF (l_debug = 1) THEN
503 print_debug('Number of MOL updated: '||SQL%rowcount,4);
504 END IF;
505 -- 13564609 starts
506 IF (inv_cache.set_item_rec(l_rti_rec.to_organization_id,l_rti_rec.item_id))
507
508 THEN
509 l_ser_ctrl_code := inv_cache.item_rec.serial_number_control_code;
510
511 END IF;
512
513 print_debug('serial control code :'||l_ser_ctrl_code,4);
514 IF ( l_ser_ctrl_code = 5) then
515
516 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
517 along with group_mark_id */
518 -- Bug 6869089
519 update /*+ ROWID */ mtl_serial_numbers msn
520 set group_mark_id = NULL,
521 line_mark_id = NULL,
522 last_update_date = sysdate,
523 last_updated_by = fnd_global.user_id,
524 current_status = (CASE WHEN Nvl(previous_status,-1) < 0 THEN current_status ELSE previous_status END),
525 lot_line_mark_id = NULL
526 where msn.ROWID in ( select msn1.ROWID
527 from mtl_serial_numbers msn1 ,
528 mtl_serial_numbers_interface msni
529 where msn1.inventory_item_id = l_rti_rec.item_id
530 and msni.product_code = 'RCV'
531 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
532 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
533 and length(msn1.serial_number) = length(msni.fm_serial_number)
534 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
535
536 --MSNI could have been moved to MSNT
537 update /*+ ROWID */ mtl_serial_numbers msn
538 set group_mark_id = NULL,
539 line_mark_id = NULL,
540 last_update_date = sysdate,
541 last_updated_by = fnd_global.user_id,
542 current_status = (CASE WHEN Nvl(previous_status,-1) < 0 THEN current_status ELSE previous_status END),
543 lot_line_mark_id = NULL
544 where msn.ROWID in ( select msn1.ROWID
545 from mtl_serial_numbers msn1 ,
546 mtl_serial_numbers_temp msnt
547 where msn1.inventory_item_id = l_rti_rec.item_id
548 and msnt.product_code = 'RCV'
549 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
550 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
551 and length(msn1.serial_number) = length(msnt.fm_serial_number)
552 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
553
554
555 else
556
557
558 update /*+ ROWID */ mtl_serial_numbers msn
559 set group_mark_id = NULL,
560 line_mark_id = NULL,
561 last_update_date = sysdate,
562 last_updated_by = fnd_global.user_id,
563 lot_line_mark_id = NULL
564 where msn.ROWID in ( select msn1.ROWID
565 from mtl_serial_numbers msn1 ,
566 mtl_serial_numbers_interface msni
567 where msn1.inventory_item_id = l_rti_rec.item_id
568 and msni.product_code = 'RCV'
569 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
570 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
571 and length(msn1.serial_number) = length(msni.fm_serial_number)
572 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
573
574 --MSNI could have been moved to MSNT
575 update /*+ ROWID */ mtl_serial_numbers msn
576 set group_mark_id = NULL,
577 line_mark_id = NULL,
578 last_update_date = sysdate,
579 last_updated_by = fnd_global.user_id,
580 lot_line_mark_id = NULL
581 where msn.ROWID in ( select msn1.ROWID
582 from mtl_serial_numbers msn1 ,
583 mtl_serial_numbers_temp msnt
584 where msn1.inventory_item_id = l_rti_rec.item_id
585 and msnt.product_code = 'RCV'
586 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
587 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
588 and length(msn1.serial_number) = length(msnt.fm_serial_number)
589 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
590
591 END IF ;
592
593 -- 13564609 ends
594
595 END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
596 ELSIF (p_txn_mode = 'LPN_GROUP') THEN --IF (p_txn_mode IN ('PREPROCESSOR','PROCESSOR')) THEN
597 FOR l_rti_rec IN (SELECT interface_transaction_id
598 , transaction_type
599 , mmtt_temp_id
600 , processing_mode_code
601 , parent_transaction_id
602 , item_id
603 , to_organization_id
604 , lpn_id
605 , item_description
606 , item_revision
607 FROM rcv_transactions_interface
608 WHERE lpn_group_id =
609 p_group_id)
610 LOOP
611 IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
612 IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
613 --Call Cleanup Op Instance
614 wms_atf_runtime_pub_apis.cleanup_operation_instance
615 (x_return_status => x_return_status
616 ,x_msg_data => x_msg_data
617 ,x_msg_count => x_msg_count
618 ,x_error_code => l_error_code
619 ,p_source_task_id => l_rti_rec.mmtt_temp_id
620 ,p_activity_type_id => 1);
621 END IF; --IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
622 END IF; --IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
623
624 --update mol and msn
625
626 --In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
627 --is removed. So when unmarking the wme_process_flag here,
628 --we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
629 --use of rti.mmtt_temp_id if present. If not, we can only
630 --update all MOL for the given org/item combination
631 IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
632 UPDATE mtl_txn_request_lines
633 SET wms_process_flag = 1
634 WHERE line_id = (SELECT move_order_line_id
635 FROM mtl_material_transactions_temp
636 WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
637 ELSIF (l_rti_rec.lpn_id IS NOT NULL) THEN
638 UPDATE mtl_txn_request_lines
639 SET wms_process_flag = 1
640 WHERE organization_id = l_rti_rec.to_organization_id
641 AND lpn_id = l_rti_rec.lpn_id
642 AND wms_process_flag = 2;
643 ELSE
644 UPDATE mtl_txn_request_lines
645 SET wms_process_flag = 1
646 WHERE organization_id = l_rti_rec.to_organization_id
647 AND inventory_item_id = l_rti_rec.item_id
648 AND wms_process_flag = 2
649 AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!');
650 --AND txn_source_id = l_rti_rec.parent_transaction_id;
651 END IF;--IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
652
653 IF (l_debug = 1) THEN
654 print_debug('Number of MOL updated: '||SQL%rowcount,4);
655 END IF;
656
657 -- 13564609 starts
658 IF (inv_cache.set_item_rec(l_rti_rec.to_organization_id,l_rti_rec.item_id))
659
660 THEN
661 l_ser_ctrl_code := inv_cache.item_rec.serial_number_control_code;
662
663 END IF;
664
665 print_debug('serial control code :'||l_ser_ctrl_code,4);
666 IF ( l_ser_ctrl_code = 5) then
667
668 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
669 along with group_mark_id */
670 -- Bug 6869089
671 update /*+ ROWID */ mtl_serial_numbers msn
672 set group_mark_id = NULL,
673 line_mark_id = NULL,
674 last_update_date = sysdate,
675 last_updated_by = fnd_global.user_id,
676 current_status = (CASE WHEN Nvl(previous_status,-1) < 0 THEN current_status ELSE previous_status END),
677 lot_line_mark_id = NULL
678
679 where msn.ROWID in ( select msn1.ROWID
680 from mtl_serial_numbers msn1 ,
681 mtl_serial_numbers_interface msni
682 where msn1.inventory_item_id = l_rti_rec.item_id
683 and msni.product_code = 'RCV'
684 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
685 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
686 and length(msn1.serial_number) = length(msni.fm_serial_number)
687 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
688
689 --MSNI could have been moved to MSNT
690 update /*+ ROWID */ mtl_serial_numbers msn
691 set group_mark_id = NULL,
692 line_mark_id = NULL,
693 last_update_date = sysdate,
694 last_updated_by = fnd_global.user_id,
695 current_status = (CASE WHEN Nvl(previous_status,-1) < 0 THEN current_status ELSE previous_status END),
696 lot_line_mark_id = NULL
697 where msn.ROWID in ( select msn1.ROWID
698 from mtl_serial_numbers msn1 ,
699 mtl_serial_numbers_temp msnt
700 where msn1.inventory_item_id = l_rti_rec.item_id
701 and msnt.product_code = 'RCV'
702 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
703 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
704 and length(msn1.serial_number) = length(msnt.fm_serial_number)
705 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
706
707
708
709
710 ELSE
711
712 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
713 along with group_mark_id */
714 -- Bug 6869089
715 update /*+ ROWID */ mtl_serial_numbers msn
716 set group_mark_id = NULL,
717 line_mark_id = NULL,
718 last_update_date = sysdate,
719 last_updated_by = fnd_global.user_id,
720 lot_line_mark_id = NULL
721 where msn.ROWID in ( select msn1.ROWID
722 from mtl_serial_numbers msn1 ,
723 mtl_serial_numbers_interface msni
724 where msn1.inventory_item_id = l_rti_rec.item_id
725 and msni.product_code = 'RCV'
726 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
727 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
728 and length(msn1.serial_number) = length(msni.fm_serial_number)
729 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
730
731 --MSNI could have been moved to MSNT
732 update /*+ ROWID */ mtl_serial_numbers msn
733 set group_mark_id = NULL,
734 line_mark_id = NULL,
735 last_update_date = sysdate,
736 last_updated_by = fnd_global.user_id,
737 lot_line_mark_id = NULL
738 where msn.ROWID in ( select msn1.ROWID
739 from mtl_serial_numbers msn1 ,
740 mtl_serial_numbers_temp msnt
741 where msn1.inventory_item_id = l_rti_rec.item_id
742 and msnt.product_code = 'RCV'
743 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
744 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
745 and length(msn1.serial_number) = length(msnt.fm_serial_number)
746 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
747
748 END IF;
749 -- 13564609 ends
750
751 END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
752 ELSIF (p_txn_mode = 'HEADER') THEN --IF (p_txn_mode IN ('PREPROCESSOR','PROCESSOR')) THEN
753 FOR l_rti_rec IN (SELECT interface_transaction_id
754 , transaction_type
755 , mmtt_temp_id
756 , processing_mode_code
757 , parent_transaction_id
758 , item_id
759 , to_organization_id
760 , lpn_id
761 , item_description
762 , item_revision
763 FROM rcv_transactions_interface
764 WHERE header_interface_id = p_group_id)
765 LOOP
766 IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
767 IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
768 --Call Cleanup Op Instance
769 wms_atf_runtime_pub_apis.cleanup_operation_instance
770 (x_return_status => x_return_status
771 ,x_msg_data => x_msg_data
772 ,x_msg_count => x_msg_count
773 ,x_error_code => l_error_code
774 ,p_source_task_id => l_rti_rec.mmtt_temp_id
775 ,p_activity_type_id => 1);
776 END IF; --IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
777 END IF; --IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
778
779 --update mol and msn
780
781 --In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
782 --is removed. So when unmarking the wme_process_flag here,
783 --we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
784 --use of rti.mmtt_temp_id if present. If not, we can only
785 --update all MOL for the given org/item combination
786 IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
787 UPDATE mtl_txn_request_lines
788 SET wms_process_flag = 1
789 WHERE line_id = (SELECT move_order_line_id
790 FROM mtl_material_transactions_temp
791 WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
792 ELSIF (l_rti_rec.lpn_id IS NOT NULL) THEN
793 UPDATE mtl_txn_request_lines
794 SET wms_process_flag = 1
795 WHERE organization_id = l_rti_rec.to_organization_id
796 AND lpn_id = l_rti_rec.lpn_id
797 AND wms_process_flag = 2;
798 ELSE
799 UPDATE mtl_txn_request_lines
800 SET wms_process_flag = 1
801 WHERE organization_id = l_rti_rec.to_organization_id
802 AND inventory_item_id = l_rti_rec.item_id
803 AND wms_process_flag = 2
804 AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!');
805 --AND txn_source_id = l_rti_rec.parent_transaction_id;
806 END IF;--IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
807
808 IF (l_debug = 1) THEN
809 print_debug('Number of MOL updated: '||SQL%rowcount,4);
810 END IF;
811 -- 13564609 starts
812 IF (inv_cache.set_item_rec(l_rti_rec.to_organization_id,l_rti_rec.item_id))
813
814 THEN
815 l_ser_ctrl_code := inv_cache.item_rec.serial_number_control_code;
816
817 END IF;
818
819 print_debug('serial control code :'||l_ser_ctrl_code,4);
820 IF ( l_ser_ctrl_code = 5) then
821
822 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
823 along with group_mark_id */
824 -- Bug 6869089
825 update /*+ ROWID */ mtl_serial_numbers msn
826 set group_mark_id = NULL,
827 line_mark_id = NULL,
828 last_update_date = sysdate,
829 last_updated_by = fnd_global.user_id,
830 current_status = (CASE WHEN Nvl(previous_status,-1) < 0 THEN current_status ELSE previous_status END),
831 lot_line_mark_id = NULL
832
833 where msn.ROWID in ( select msn1.ROWID
834 from mtl_serial_numbers msn1 ,
835 mtl_serial_numbers_interface msni
836 where msn1.inventory_item_id = l_rti_rec.item_id
837 and msni.product_code = 'RCV'
838 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
839 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
840 and length(msn1.serial_number) = length(msni.fm_serial_number)
841 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
842
843 --MSNI could have been moved to MSNT
844 update /*+ ROWID */ mtl_serial_numbers msn
845 set group_mark_id = NULL,
846 line_mark_id = NULL,
847 last_update_date = sysdate,
848 last_updated_by = fnd_global.user_id,
849 current_status = (CASE WHEN Nvl(previous_status,-1) < 0 THEN current_status ELSE previous_status END),
850 lot_line_mark_id = NULL
851 where msn.ROWID in ( select msn1.ROWID
852 from mtl_serial_numbers msn1 ,
853 mtl_serial_numbers_temp msnt
854 where msn1.inventory_item_id = l_rti_rec.item_id
855 and msnt.product_code = 'RCV'
856 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
857 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
858 and length(msn1.serial_number) = length(msnt.fm_serial_number)
859 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
860
861
862
863
864 ELSE
865
866 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
867 along with group_mark_id */
868 -- Bug 6869089
869 update /*+ ROWID */ mtl_serial_numbers msn
870 set group_mark_id = NULL,
871 line_mark_id = NULL,
872 last_update_date = sysdate,
873 last_updated_by = fnd_global.user_id,
874 lot_line_mark_id = NULL
875 where msn.ROWID in ( select msn1.ROWID
876 from mtl_serial_numbers msn1 ,
877 mtl_serial_numbers_interface msni
878 where msn1.inventory_item_id = l_rti_rec.item_id
879 and msni.product_code = 'RCV'
880 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
881 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
882 and length(msn1.serial_number) = length(msni.fm_serial_number)
883 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
884
885 --MSNI could have been moved to MSNT
886 update /*+ ROWID */ mtl_serial_numbers msn
887 set group_mark_id = NULL,
888 line_mark_id = NULL,
889 last_update_date = sysdate,
890 last_updated_by = fnd_global.user_id,
891 lot_line_mark_id = NULL
892 where msn.ROWID in ( select msn1.ROWID
893 from mtl_serial_numbers msn1 ,
894 mtl_serial_numbers_temp msnt
895 where msn1.inventory_item_id = l_rti_rec.item_id
896 and msnt.product_code = 'RCV'
897 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
898 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
899 and length(msn1.serial_number) = length(msnt.fm_serial_number)
900 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
901
902 END IF;
903 -- 13564609 ends
904 END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
905 ELSE --IF (p_txn_mode IN ('PREPROCESSOR','PROCESSOR')) THEN
906 l_prev_lpn_group_id := 0;
907 FOR l_rti_rec IN (SELECT interface_transaction_id
908 , transaction_type
909 , mmtt_temp_id
910 , processing_mode_code
911 , parent_transaction_id
912 , item_id
913 , to_organization_id
914 , lpn_group_id
915 , lpn_id
916 , item_description
917 , item_revision
918 FROM rcv_transactions_interface
919 WHERE group_id =
920 p_group_id)
921 LOOP
922 IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
923 IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
924 --Call Cleanup Op Instance
925 wms_atf_runtime_pub_apis.cleanup_operation_instance
926 (x_return_status => x_return_status
927 ,x_msg_data => x_msg_data
928 ,x_msg_count => x_msg_count
929 ,x_error_code => l_error_code
930 ,p_source_task_id => l_rti_rec.mmtt_temp_id
931 ,p_activity_type_id => 1);
932 END IF; --IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
933 END IF; --IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
934
935 --update mol and msn
936
937 --In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
938 --is removed. So when unmarking the wme_process_flag here,
939 --we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
940 --use of rti.mmtt_temp_id if present. If not, we can only
941 --update all MOL for the given org/item combination
942 IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
943 UPDATE mtl_txn_request_lines
944 SET wms_process_flag = 1
945 WHERE line_id = (SELECT move_order_line_id
946 FROM mtl_material_transactions_temp
947 WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
948 ELSIF (l_rti_rec.lpn_id IS NOT NULL) THEN
949 UPDATE mtl_txn_request_lines
950 SET wms_process_flag = 1
951 WHERE organization_id = l_rti_rec.to_organization_id
952 AND lpn_id = l_rti_rec.lpn_id
953 AND wms_process_flag = 2;
954 ELSE
955 UPDATE mtl_txn_request_lines
956 SET wms_process_flag = 1
957 WHERE organization_id = l_rti_rec.to_organization_id
958 AND inventory_item_id = l_rti_rec.item_id
959 AND wms_process_flag = 2
960 AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!');
961 --AND txn_source_id = l_rti_rec.parent_transaction_id;
962 END IF;--IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
963
964 IF (l_debug = 1) THEN
965 print_debug('Number of MOL updated: '||SQL%rowcount,4);
966 END IF;
967 -- 13564609 starts
968 IF (inv_cache.set_item_rec(l_rti_rec.to_organization_id,l_rti_rec.item_id))
969
970 THEN
971 l_ser_ctrl_code := inv_cache.item_rec.serial_number_control_code;
972
973 END IF;
974
975 print_debug('serial control code :'||l_ser_ctrl_code,4);
976 IF ( l_ser_ctrl_code = 5) then
977
978 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
979 along with group_mark_id */
980 -- Bug 6869089
981 update /*+ ROWID */ mtl_serial_numbers msn
982 set group_mark_id = NULL,
983 line_mark_id = NULL,
984 last_update_date = sysdate,
985 last_updated_by = fnd_global.user_id,
986 current_status = (CASE WHEN Nvl(previous_status,-1) < 0 THEN current_status ELSE previous_status END),
987 lot_line_mark_id = NULL
988 where msn.ROWID in ( select msn1.ROWID
989 from mtl_serial_numbers msn1 ,
990 mtl_serial_numbers_interface msni
991 where msn1.inventory_item_id = l_rti_rec.item_id
992 and msni.product_code = 'RCV'
993 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
994 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
995 and length(msn1.serial_number) = length(msni.fm_serial_number)
996 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
997
998 --MSNI could have been moved to MSNT
999 update /*+ ROWID */ mtl_serial_numbers msn
1000 set group_mark_id = NULL,
1001 line_mark_id = NULL,
1002 last_update_date = sysdate,
1003 last_updated_by = fnd_global.user_id,
1004 current_status = (CASE WHEN Nvl(previous_status,-1) < 0 THEN current_status ELSE previous_status END),
1005 lot_line_mark_id = NULL
1006 where msn.ROWID in ( select msn1.ROWID
1007 from mtl_serial_numbers msn1 ,
1008 mtl_serial_numbers_temp msnt
1009 where msn1.inventory_item_id = l_rti_rec.item_id
1010 and msnt.product_code = 'RCV'
1011 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
1012 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
1013 and length(msn1.serial_number) = length(msnt.fm_serial_number)
1014 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
1015
1016
1017
1018
1019 ELSE
1020
1021 /* Bug 4911281: We have to clear the line_mark_id and lot_line_mark_id
1022 along with group_mark_id */
1023 -- Bug 6869089
1024 update /*+ ROWID */ mtl_serial_numbers msn
1025 set group_mark_id = NULL,
1026 line_mark_id = NULL,
1027 last_update_date = sysdate,
1028 last_updated_by = fnd_global.user_id,
1029 lot_line_mark_id = NULL
1030 where msn.ROWID in ( select msn1.ROWID
1031 from mtl_serial_numbers msn1 ,
1032 mtl_serial_numbers_interface msni
1033 where msn1.inventory_item_id = l_rti_rec.item_id
1034 and msni.product_code = 'RCV'
1035 and msni.product_transaction_id = l_rti_rec.interface_transaction_id
1036 and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
1037 and length(msn1.serial_number) = length(msni.fm_serial_number)
1038 and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
1039
1040 --MSNI could have been moved to MSNT
1041 update /*+ ROWID */ mtl_serial_numbers msn
1042 set group_mark_id = NULL,
1043 line_mark_id = NULL,
1044 last_update_date = sysdate,
1045 last_updated_by = fnd_global.user_id,
1046 lot_line_mark_id = NULL
1047 where msn.ROWID in ( select msn1.ROWID
1048 from mtl_serial_numbers msn1 ,
1049 mtl_serial_numbers_temp msnt
1050 where msn1.inventory_item_id = l_rti_rec.item_id
1051 and msnt.product_code = 'RCV'
1052 and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
1053 and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
1054 and length(msn1.serial_number) = length(msnt.fm_serial_number)
1055 and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
1056
1057 END IF;
1058 -- 13564609 ends
1059
1060 -- Delete WLPNI/MSNI/MSNT/MTLI/MTLT
1061 -- Commenting the following as PO never deletes the RTI row.
1062 /* Bug 4901912 - Uncommenting the deleting of the interface and temp tables as
1063 also deleting the rti if in the online mode. */
1064
1065 IF (l_rti_rec.processing_mode_code = 'ONLINE' ) THEN
1066 IF (l_debug = 1) THEN
1067 print_debug('TXN_COMPLETE - Deleting mtli, msni, mtlt, msnt, wlpni for interface id:'
1068 || l_rti_rec.interface_transaction_id ,1);
1069 END IF;
1070
1071 DELETE FROM mtl_transaction_lots_interface
1072 WHERE product_code = 'RCV'
1073 AND product_transaction_id = l_rti_rec.interface_transaction_id;
1074
1075 DELETE FROM mtl_transaction_lots_temp
1076 WHERE product_code = 'RCV'
1077 AND product_transaction_id = l_rti_rec.interface_transaction_id;
1078
1079 DELETE FROM mtl_serial_numbers_interface
1080 WHERE product_code = 'RCV'
1081 AND product_transaction_id = l_rti_rec.interface_transaction_id;
1082
1083 DELETE FROM mtl_serial_numbers_temp
1084 WHERE product_code = 'RCV'
1085 AND product_transaction_id = l_rti_rec.interface_transaction_id;
1086
1087 IF (l_prev_lpn_group_id <> l_rti_rec.lpn_group_id) THEN
1088 l_prev_lpn_group_id := l_rti_rec.lpn_group_id;
1089
1090 DELETE FROM wms_lpn_interface
1091 WHERE source_group_id = l_rti_rec.lpn_group_id;
1092 END IF; --IF (l_prev_lpn_group_id <> l_rti_rec.lpn_group_id) THEN
1093 END IF; -- l_rti_rec.processing_mode_code = 'ONLINE'
1094
1095 /* End of fix for Bug 4901912 */
1096
1097 END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
1098 END IF; --IF (p_txn_mode IN ('PREPROCESSOR','PROCESSOR')) THEN
1099 COMMIT;
1100 ELSE --IF (p_txn_status = 'FALSE') THEN
1101 BEGIN
1102 IF p_txn_mode = 'LPN_GROUP' THEN
1103 SELECT transaction_type
1104 INTO l_transaction_type
1105 FROM rcv_transactions
1106 WHERE lpn_group_id = p_group_id
1107 AND transaction_date >= (Sysdate - 1) --BUG 3444137: RT
1108 --will have INDEX ON transaction_date AND lpn_group_id
1109 AND transaction_type IN ('CORRECT','RETURN TO VENDOR',
1110 'RETURN TO RECEIVING','RETURN TO CUSTOMER')
1111 AND ROWNUM < 2;
1112 ELSE
1113 SELECT transaction_type
1114 INTO l_transaction_type
1115 FROM rcv_transactions
1116 WHERE group_id = p_group_id
1117 AND transaction_type IN ('CORRECT','RETURN TO VENDOR',
1118 'RETURN TO RECEIVING','RETURN TO CUSTOMER')
1119 AND ROWNUM < 2;
1120 END IF; --IF p_txn_mode = 'LPN_GROUP' THEN
1121 EXCEPTION
1122 WHEN no_data_found THEN
1123 IF (l_debug = 1) THEN
1124 print_debug('No records matched in RT for group_id - '||p_group_id||' : 100',1);
1125 END IF;
1126 RETURN;
1127 END;
1128
1129 --Must call wms_return_sv.txn_complete to take care of deleting
1130 --reservations for non-express case.
1131 IF l_transaction_type in ('CORRECT','RETURN TO VENDOR','RETURN TO RECEIVING','RETURN TO CUSTOMER') THEN
1132
1133 x_return_status := fnd_api.g_ret_sts_success;
1134 wms_return_sv.txn_complete(
1135 p_group_id => p_group_id,
1136 p_txn_status => p_txn_status,
1137 p_txn_mode => p_txn_mode,
1138 x_return_status => x_return_status,
1139 x_msg_data => x_msg_data,
1140 x_msg_count => x_msg_count);
1141
1142 IF ( x_return_status = fnd_api.g_ret_sts_error OR
1143 x_return_status = fnd_api.g_ret_sts_unexp_error ) THEN
1144 IF (l_debug = 1) THEN
1145 print_debug('Error return from wms_return_sv.txn_complete, exitting - '||x_msg_data||' : 101',1);
1146 END IF;
1147 IF p_txn_mode <> 'ONLINE' THEN
1148 create_errors(p_group_id => p_group_id,
1149 p_msg => 'inv_receiving_transaction.txn_complete - 102 -'||x_msg_data);
1150 END IF;
1151 IF x_return_status = fnd_api.g_ret_sts_error THEN
1152 RAISE fnd_api.g_exc_error;
1153 ELSE
1154 RAISE fnd_api.g_exc_unexpected_error;
1155 END IF;
1156 END IF;
1157
1158 x_return_status := fnd_api.g_ret_sts_success;
1159
1160 END IF; --IF l_transaction_type in ('CORRECT','RETURN TO VENDOR','RETURN TO RECEIVING','RETURN TO CUSTOMER') THEN
1161
1162 END IF; --IF (p_txn_status = 'FALSE') THEN
1163
1164 --Begin bug 4611237
1165 --Delete records from WLPNH with context 6, operation_mode of -99999 and
1166 --group_id passed to txn_complete api.
1167
1168 IF (l_debug = 1) THEN
1169 print_debug('Delete records from WLPN with context 6, operation_mode of -99999 and group_id', 4);
1170 END IF;
1171
1172 DELETE FROM wms_lpn_histories
1173 WHERE source_header_id = p_group_id
1174 AND lpn_context = 6
1175 AND operation_mode = -99999;
1176
1177 --End bug 4611237
1178 EXCEPTION
1179 WHEN fnd_api.g_exc_error THEN
1180 IF (l_debug = 1) THEN
1181 print_debug('Execution error in txn_complete',4);
1182 END IF;
1183 x_return_status := fnd_api.g_ret_sts_error;
1184 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1185
1186 WHEN fnd_api.g_exc_unexpected_error THEN
1187 IF (l_debug = 1) THEN
1188 print_debug('Unexpected error in txn_complete',4);
1189 END IF;
1190 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1191 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1192
1193 WHEN OTHERS THEN
1194 IF (l_debug = 1) THEN
1195 print_debug('Exception in txn_complete',4);
1196 END IF;
1197 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1198 --
1199 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1200 THEN
1201 fnd_msg_pub.add_exc_msg
1202 ( g_pkg_name
1203 , 'txn_complete'
1204 );
1205 END IF;
1206
1207 -- Get message count and data
1208 --fnd_msg_pub.count_and_get
1209 -- ( p_count => x_msg_count
1210 -- , p_data => x_msg_data
1211 -- );
1212 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1213
1214 END txn_complete;
1215
1216 PROCEDURE txn_mobile_timeout_cleanup(p_group_id IN NUMBER,
1217 p_rti_rec_count IN NUMBER,
1218 x_return_status OUT NOCOPY VARCHAR2,
1219 x_msg_data OUT NOCOPY VARCHAR2,
1220 x_msg_count OUT NOCOPY NUMBER)
1221 IS
1222 CURSOR c_mmtt_txn_temp_id IS
1223 SELECT DISTINCT rti.mmtt_temp_id
1224 FROM rcv_transactions_interface rti
1225 WHERE rti.group_id = p_group_id;
1226
1227 l_transaction_type VARCHAR2(100);
1228 l_organization_id NUMBER;
1229 l_mmtt_transaction_temp_id NUMBER;
1230 l_wms_install_status VARCHAR2(1);
1231 l_return_status VARCHAR2(5);
1232 l_msg_data VARCHAR2(500);
1233 l_msg_count NUMBER;
1234
1235
1236 l_patch_j_code BOOLEAN := FALSE;
1237 l_mobile_txn_count NUMBER;
1238
1239 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1240 BEGIN
1241
1242 x_return_status := fnd_api.g_ret_sts_success;
1243 IF (l_debug = 1) THEN
1244 print_debug('TXN_COMPLETE - Enter txn_complete : 10: '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1245 print_debug('TXN_COMPLETE - Parameters passed : 10.1: p_group_id - '||p_group_id, 4);
1246 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Parameters passed : 10.1: p_rti_rec_count - '||p_rti_rec_count, 4);
1247 END IF;
1248
1249 --setting a parameter to see if the release level is above J or below J.
1250 IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
1251 (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
1252 l_patch_j_code := TRUE;
1253 ELSE
1254 l_patch_j_code := FALSE;
1255 END IF;
1256
1257 IF (l_patch_j_code = FALSE) THEN
1258 IF (l_debug = 1) THEN
1259 print_debug('TXN_COMPLETE - Release level is prior to J',1);
1260 END IF;
1261 -- for a group id, if it is a row from mobile, all rows will have
1262 -- the same mobile_txn, transaction_type, lpn_id, transfer_lpn_id,
1263 -- content_lpn_id
1264 SELECT count(Nvl(rti.mobile_txn, 'N'))
1265 INTO l_mobile_txn_count
1266 FROM rcv_transactions_interface rti
1267 WHERE rti.group_id = p_group_id
1268 AND processing_mode_code = 'ONLINE'
1269 AND processing_status_code = 'PENDING'
1270 AND transaction_status_code = 'PENDING';
1271
1272 IF (l_mobile_txn_count <> p_rti_rec_count) THEN
1273 IF (l_debug = 1) THEN
1274 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP -count does not match - exiting the procedure : 30',1);
1275 END IF;
1276 RETURN;
1277 END IF;
1278
1279 -- set wms_installed flag
1280 IF wms_install.check_install(x_return_status,
1281 x_msg_count,
1282 x_msg_data,
1283 l_organization_id) THEN
1284 l_wms_install_status := 'I';
1285 ELSE
1286 l_wms_install_status := 'U';
1287 END IF;
1288 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1289 IF (l_debug = 1) THEN
1290 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Could not get wms installed status, exitting - '||x_msg_data||' : 40',1);
1291 END IF;
1292 RAISE fnd_api.g_exc_error; --RETURN;
1293 END IF;
1294
1295 IF l_wms_install_status = 'I' THEN
1296
1297 IF (l_debug = 1) THEN
1298 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - WMS is installed : 50',4);
1299 END IF;
1300
1301 IF l_transaction_type = 'DELIVER' THEN
1302 IF (l_debug = 1) THEN
1303 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Transaction type is DELIVER : 180',4);
1304 END IF;
1305 BEGIN
1306 OPEN c_mmtt_txn_temp_id;
1307 LOOP -- Loop through all the mmtt recs for this group for crossdocking
1308 IF (l_debug = 1) THEN
1309 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - p_group_id:'||p_group_id,4);
1310 END IF;
1311 FETCH c_mmtt_txn_temp_id
1312 INTO l_mmtt_transaction_temp_id;
1313 EXIT WHEN c_mmtt_txn_temp_id%notfound;
1314 IF (l_debug = 1) THEN
1315 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - mmtt_temp_id:'||l_mmtt_transaction_temp_id,4);
1316 END IF;
1317 -- the records will be there in mmtt only if it was a wms
1318 -- enabled org. For an inventory org, there will be no recs.
1319 -- in mmtt so it will exit out of the loop immediately.
1320 wms_task_dispatch_put_away.putaway_cleanup
1321 ( p_temp_id=>l_mmtt_transaction_temp_id
1322 , p_org_id=>l_organization_id
1323 , x_return_status =>x_return_status
1324 , x_msg_count =>x_msg_count
1325 , x_msg_data =>x_msg_data
1326 );
1327 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1328 IF (l_debug = 1) THEN
1329 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Could not archive tasks, exiting - '||x_msg_data||' : 105',1);
1330 END IF;
1331 END IF;
1332 END LOOP;
1333 CLOSE c_mmtt_txn_temp_id;
1334 END;
1335
1336 BEGIN
1337 UPDATE wms_lpn_contents
1338 SET txn_error_flag = 'Y'
1339 WHERE source_header_id = p_group_id;
1340 EXCEPTION
1341 WHEN no_data_found THEN
1342 NULL;
1343 WHEN OTHERS THEN
1344 IF (l_debug = 1) THEN
1345 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Exception while updating wms_lpn_contents to error : 200',4);
1346 END IF;
1347 END;
1348
1349 BEGIN
1350 UPDATE mtl_serial_numbers
1351 SET lpn_txn_error_flag = 'Y'
1352 WHERE ROWID IN (SELECT msn.ROWID
1353 FROM mtl_serial_numbers msn
1354 , rcv_transactions_interface rti
1355 WHERE msn.last_txn_source_id = p_group_id
1356 AND rti.group_id = p_group_id
1357 AND rti.item_id = msn.inventory_item_id);
1358
1359 EXCEPTION
1360 WHEN no_data_found THEN
1361 NULL;
1362 WHEN OTHERS THEN
1363 IF (l_debug = 1) THEN
1364 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Exception while updating mtl_serial_numbers to error : 210',4);
1365 END IF;
1366 END;
1367
1368 ELSIF l_transaction_type = 'RECEIVE' THEN
1369 IF (l_debug = 1) THEN
1370 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Transaction type was RECEIVE : 220',4);
1371 END IF;
1372
1373 rcv_txn_clean_up
1374 (x_return_status => x_return_status,
1375 x_msg_count => x_msg_count,
1376 x_msg_data => x_msg_data,
1377 p_group_id => p_group_id);
1378 print_debug('rcv_txn_clean_up - Finished clean up : 221',4);
1379 END IF;
1380
1381
1382 -- Delete/Clear mtl_serial_numbers_temp rows
1383 -- Delete/Clear mtl_transaction_lots_temp rows
1384 -- If the Transaction Fails
1385
1386 IF (l_debug = 1) THEN
1387 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - cleanup msnt 1',4);
1388 END IF;
1389
1390
1391 delete from mtl_serial_numbers_temp msnt
1392 where msnt.transaction_temp_id in
1393 ( select interface_transaction_id
1394 from rcv_transactions_interface
1395 where group_id = p_group_id )
1396 ;
1397
1398 IF (l_debug = 1) THEN
1399 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - cleanup msnt 2',4);
1400 END IF;
1401
1402 delete from mtl_serial_numbers_temp msnt
1403 where msnt.transaction_temp_id in
1404 ( select mtlt.serial_transaction_temp_id
1405 from mtl_transaction_lots_temp mtlt
1406 where mtlt.transaction_temp_id in (
1407 select interface_transaction_id
1408 from rcv_transactions_interface
1409 where group_id = p_group_id )
1410 );
1411
1412 IF (l_debug = 1) THEN
1413 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - cleanup mtlt 3',4);
1414 END IF;
1415
1416 delete from mtl_transaction_lots_temp mtlt
1417 where mtlt.transaction_temp_id
1418 in ( select interface_transaction_id
1419 from rcv_transactions_interface
1420 where group_id = p_group_id );
1421
1422 IF (l_debug = 1) THEN
1423 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Committing after rollbacking and cleanup',4);
1424 END IF;
1425 END IF; -- l_wms_install_status = 'I'
1426 IF (l_debug = 1) THEN
1427 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Exiting TXN_MOBILE_TIMEOUT_CLEANUP : 230 '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1428 END IF;
1429 END IF; --IF (l_patch_j_code = FALSE) THEN
1430
1431 EXCEPTION
1432 WHEN fnd_api.g_exc_error THEN
1433 IF (l_debug = 1) THEN
1434 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Execution error in TXN_MOBILE_TIMEOUT_CLEANUP',4);
1435 END IF;
1436 x_return_status := fnd_api.g_ret_sts_error;
1437 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1438
1439 WHEN fnd_api.g_exc_unexpected_error THEN
1440 IF (l_debug = 1) THEN
1441 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Unexpected error in TXN_MOBILE_TIMEOUT_CLEANUP',4);
1442 END IF;
1443 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1444 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1445
1446 WHEN OTHERS THEN
1447 IF (l_debug = 1) THEN
1448 print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - Exception in TXN_MOBILE_TIMEOUT_CLEANUP',4);
1449 END IF;
1450 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1451 --
1452 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1453 THEN
1454 fnd_msg_pub.add_exc_msg
1455 ( g_pkg_name
1456 , 'txn_complete'
1457 );
1458 END IF;
1459
1460 -- Get message count and data
1461 --fnd_msg_pub.count_and_get
1462 -- ( p_count => x_msg_count
1463 -- , p_data => x_msg_data
1464 -- );
1465 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1466
1467 END txn_mobile_timeout_cleanup;
1468
1469 END inv_receiving_transaction;
[Home] [Help]