1 PACKAGE BODY WMS_RETURN_SV AS
2 /* $Header: WMSRETNB.pls 120.6.12010000.2 2008/08/19 09:56:00 anviswan ship $ */
3
4 PROCEDURE print_debug(p_err_msg VARCHAR2, p_level NUMBER default 4) IS
5 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6 BEGIN
7
8 inv_mobile_helper_functions.tracelog
9 (p_err_msg => p_err_msg,
10 p_module => 'WMS_RETURN_SV',
11 p_level => p_level);
12
13 END print_debug;
14
15 PROCEDURE maintain_move_orders(
16 p_group_id IN NUMBER,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER) IS
20
21 CURSOR c_create_mo IS
22 SELECT rt.organization_id
23 , rt.po_line_location_id
24 , rt.shipment_line_id
25 , rt.oe_order_line_id
26 , rt.lpn_id
27 , rt.transfer_lpn_id
28 , rsl.item_id
29 , rsl.item_revision
30 , rt.quantity
31 , rt.unit_of_measure
32 , rt.transaction_type
33 , rt.interface_transaction_id
34 , rt.destination_type_code
35 , rt.parent_transaction_id
36 --, rsl.shipment_line_id
37 --, poll.receiving_routing_id routing_id
38 , msi.lot_control_code
39 , DECODE(MSI.RETURN_INSPECTION_REQUIREMENT,1,'Y','N') INSPECTION_REQUIRED_FLAG
40 , rsl.from_organization_id
41 , rsl.asn_line_flag
42 FROM rcv_shipment_lines rsl
43 , mtl_system_items msi
44 , rcv_transactions rt
45 WHERE rt.group_id = p_group_id
46 AND (rt.transaction_type = 'CORRECT'
47 -- return to receiving is also created for a rtv/rtc txn.
48 -- from inventory. But for that we dont want to create
49 -- a move order so should eliminate those txns.
50 -- Those txns. will not have a transfer_lpn_id stamped
51 -- but the pure return_to_receiving txns. will have
52 -- destination_type_code = 'INVENTORY' unlike the pure
53 -- ones which will have destination_type_code = 'RECEIVING'
54 OR (rt.transaction_type = 'RETURN TO RECEIVING'
55 AND rt.destination_type_code = 'RECEIVING'))
56 AND rt.user_entered_flag = 'Y'
57 AND rsl.shipment_line_id = rt.shipment_line_id
58 AND msi.inventory_item_id = rsl.item_id
59 AND msi.organization_id = rt.organization_id;
60
61 CURSOR c_rt_for_mo_udpate IS
62 SELECT rsl.item_id
63 , rt.po_line_location_id
64 , rt.shipment_line_id
65 , rt.oe_order_line_id
66 , rt.quantity rt_quantity
67 , rt.transaction_id
68 , rt.interface_transaction_id
69 , rt.lpn_id
70 , rt.transfer_lpn_id
71 , rt.primary_unit_of_measure
72 , rt.unit_of_measure
73 , rt.organization_id
74 , mtlt.lot_number
75 , mtlt.transaction_quantity
76 , rt.transaction_type
77 , rt.parent_transaction_id
78 , rsl.asn_line_flag
79 , DECODE(MSI.RETURN_INSPECTION_REQUIREMENT,1,'Y','N') INSPECTION_REQUIRED_FLAG
80 FROM mtl_transaction_lots_temp mtlt
81 , rcv_shipment_lines rsl
82 , rcv_transactions rt
83 , mtl_system_items msi
84 WHERE rt.group_id = p_group_id
85 AND (mtlt.transaction_temp_id (+) = rt.interface_transaction_id
86 -- Since mtlt is deleted for a correction record for a
87 -- deliver transaction, that record should be selected
88 -- from the union. So eliminating the selection of that
89 -- record from this part of the union.
90 AND NOT (rt.quantity > 0
91 AND rt.transaction_type = 'CORRECT'
92 AND msi.lot_control_code = 2
93 AND exists (SELECT 1
94 FROM rcv_transactions rt1
95 WHERE rt1.transaction_id = rt.parent_transaction_id
96 AND rt1.transaction_type = 'DELIVER')))
97 AND (rt.transaction_type = 'CORRECT'
98 -- select the return_to_receiving txn created for the
99 -- rtv/rtc transaction from inventory as for this all we
100 -- need to do is update the process_flag and not update any
101 -- mol since rtv/rtc from inventory does not effect mol
102 -- but for that the process_flag on mol was updated to 2.
103 OR (rt.transaction_type = 'RETURN TO RECEIVING'
104 AND rt.destination_type_code = 'INVENTORY')
105 OR (rt.transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
106 -- to eliminate the row being selected for a rtv
107 -- from inventory as for those we dont need to update
108 -- the move order line.
109 AND NOT exists (SELECT 1
110 FROM rcv_transactions rt2
111 WHERE rt2.interface_transaction_id = rt.interface_transaction_id
112 AND rt2.transaction_type = 'RETURN TO RECEIVING'
113 AND rt2.group_id = p_group_id)))
114 AND rt.user_entered_flag = 'Y'
115 AND rsl.shipment_line_id = rt.shipment_line_id
116 AND msi.inventory_item_id = rsl.item_id
117 AND msi.organization_id = rt.organization_id
118 UNION ALL
119 SELECT rsl.item_id
120 , rt.po_line_location_id
121 , rt.shipment_line_id
122 , rt.oe_order_line_id
123 , rt.quantity rt_quantity
124 , rt.transaction_id
125 , rt.interface_transaction_id
126 , rt.lpn_id
127 , rt.transfer_lpn_id
128 , rt.primary_unit_of_measure
129 , rt.unit_of_measure
130 , rt.organization_id
131 , mtln.lot_number
132 , mtln.transaction_quantity
133 , rt.transaction_type
134 , rt.parent_transaction_id
135 , rsl.asn_line_flag
136 , DECODE(MSI.RETURN_INSPECTION_REQUIREMENT,1,'Y','N') INSPECTION_REQUIRED_FLAG
137 FROM mtl_material_transactions mmt
138 , mtl_transaction_lot_numbers mtln
139 , rcv_shipment_lines rsl
140 , rcv_transactions rt
141 , mtl_system_items msi
142 WHERE rt.group_id = p_group_id
143 AND mmt.rcv_transaction_id = rt.transaction_id
144 AND mmt.transaction_id = mtln.transaction_id
145 -- should select in this part of the union only the cases which
146 -- have not been selected on top which are the ones for which the
147 -- row is deleted from mtlt.
148 AND rt.quantity > 0
149 AND rt.transaction_type = 'CORRECT'
150 AND msi.lot_control_code = 2
151 AND exists (SELECT 1
152 FROM rcv_transactions rt1
153 WHERE rt1.transaction_id = rt.parent_transaction_id
154 AND rt1.transaction_type = 'DELIVER')
155 AND rt.user_entered_flag = 'Y'
156 AND rsl.shipment_line_id = rt.shipment_line_id
157 AND msi.inventory_item_id = rsl.item_id
158 AND msi.organization_id = rt.organization_id;
159
160
161 CURSOR c_update_mo(l_transaction_id IN NUMBER
162 , l_item_id IN NUMBER
163 , l_lot_number in VARCHAR2
164 , v_reference IN VARCHAR2
165 , v_reference_id IN NUMBER
166 , v_lpn_id IN NUMBER
167 , v_inspection_status IN NUMBER
168 , v_organization_id IN NUMBER)
169 IS
170 SELECT mol.header_id
171 , mol.line_id
172 , mol.quantity mol_quantity
173 , nvl(mol.quantity_delivered,0) mol_quantity_delivered
174 , (mol.quantity - nvl(mol.quantity_delivered,0)) mol_available_quantity
175 , mol.lot_number
176 , mol.uom_code mol_uom_code
177 , mol.reference
178 , mol.lpn_id
179 , mol.inventory_item_id item_id
180 , rt.quantity rt_quantity
181 , rt.primary_unit_of_measure
182 , rt.unit_of_measure
183 , rt.organization_id
184 FROM mtl_txn_request_lines mol, rcv_transactions rt
185 WHERE rt.transaction_id = l_transaction_id
186 AND mol.organization_id = v_organization_id
187 AND rt.organization_id = v_organization_id
188 AND mol.reference_id = v_reference_id
189 AND mol.reference = v_reference
190 AND mol.inventory_item_id = l_item_id
191 AND mol.lpn_id = v_lpn_id
192 AND Nvl(mol.inspection_status,-1) = Nvl(v_inspection_status,-1)
193 AND nvl(mol.lot_number,'@@@') = nvl(l_lot_number,'@@@')
194 AND nvl(mol.quantity,0) - nvl(mol.quantity_delivered,0) > 0
195 ORDER BY nvl(mol.quantity,0) - nvl(mol.quantity_delivered,0) DESC;
196
197 l_lpn_id NUMBER;
198 l_inspect NUMBER;
199 l_parent_transaction_type VARCHAR2(30);
200 l_rtr_parent_txn_type VARCHAR2(30);
201 l_rtv_parent_txn_type VARCHAR2(30);
202 l_grand_parent_txn_type VARCHAR2(30);
203 l_uom_code VARCHAR2(3);
204 l_move_order_header_id NUMBER;
205 l_mol_unit_of_measure VARCHAR2(25);
206 l_rt_qty_in_mol_uom NUMBER;
207 l_mol_qty_in_primary_uom NUMBER;
208 l_rt_qty_in_primary_uom NUMBER;
209 l_reference_id NUMBER;
210 l_reference VARCHAR2(25);
211
212 l_update_lpn NUMBER;
213 l_routing_id NUMBER;
214 l_transfer_org_id NUMBER;
215 l_inspection_status NUMBER;
216 l_progress VARCHAR2(5);
217 l_return_status VARCHAR2(5);
218 l_msg_data VARCHAR2(500);
219 l_msg_count NUMBER;
220 l_lpn_context NUMBER;--bug3646129
221
222 l_pregen_putaway_tasks_flag NUMBER;
223 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
224 BEGIN
225
226 x_return_status := FND_API.G_RET_STS_SUCCESS;
227 l_return_status := fnd_api.g_ret_sts_success;
228 x_msg_count := 0;
229
230 l_progress := '10';
231 IF (l_debug = 1) THEN
232 print_debug('=== Start maintain_move_orders ===');
233 END IF;
234
235 FOR i IN c_create_mo LOOP
236
237 IF (l_debug = 1) THEN
238 print_debug('Creating MO for id:'||i.interface_transaction_id||':transaction_type='|| i.transaction_type || ', qty=' || i.quantity);
239 END IF;
240
241 IF i.transaction_type = 'RETURN TO RECEIVING' THEN
242 l_progress := '20';
243
244 l_lpn_id := i.transfer_lpn_id;
245 -- Bug #1939258
246 SELECT transaction_type
247 INTO l_rtr_parent_txn_type
248 FROM rcv_transactions
249 WHERE transaction_id = i.parent_transaction_id;
250 IF (l_debug = 1) THEN
251 print_debug('parent transaction_type for return to receiving='|| l_rtr_parent_txn_type);
252 END IF;
253
254 ELSIF i.transaction_type = 'CORRECT' THEN
255
256 l_progress := '30';
257 SELECT transaction_type into l_parent_transaction_type
258 FROM rcv_transactions
259 WHERE transaction_id = i.parent_transaction_id;
260
261 IF (l_debug = 1) THEN
262 print_debug('correction parent transaction_type='|| l_parent_transaction_type);
263 END IF;
264
265 IF l_parent_transaction_type = 'RECEIVE' THEN
266 l_progress := '40';
267
268 IF i.quantity > 0 THEN
269 l_lpn_id := i.transfer_lpn_id;
270 ELSE
271 l_lpn_id := NULL; -- MO is updated
272 END IF;
273
274 ELSIF l_parent_transaction_type = 'DELIVER' THEN
275 l_progress := '50';
276
277 IF i.quantity > 0 THEN
278 l_lpn_id := NULL; -- MO is updated
279 ELSE
280 l_lpn_id := i.lpn_id;
281 END IF;
282
283 ELSIF l_parent_transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') THEN
284 l_progress := '60';
285
286 IF i.quantity > 0 THEN
287 l_lpn_id := NULL; -- MO is updated
288 ELSE
289 l_lpn_id := i.lpn_id;
290 END IF;
291
292 ELSIF l_parent_transaction_type IN ('ACCEPT', 'REJECT') THEN
293 l_progress := '70';
294 IF i.quantity < 0 THEN
295 -- For this we need to create a new MO with an inspection
296 -- status of not inspected and also later modify
297 -- the
298 l_lpn_id := i.lpn_id;
299 ELSE
300 l_lpn_id := i.transfer_lpn_id;
301 END IF;
302 END IF;
303
304 END IF;
305 l_progress := '80';
306 IF l_lpn_id IS NOT NULL
307 AND (i.transaction_type = 'RETURN TO RECEIVING'
308 OR (i.transaction_type = 'CORRECT' AND
309 (l_parent_transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER','DELIVER') AND i.quantity < 0)
310 OR (l_parent_transaction_type = 'RECEIVE' AND i.quantity > 0)
311 OR (l_parent_transaction_type IN ('ACCEPT', 'REJECT')))) THEN
312 l_progress := '90';
313 IF (l_debug = 1) THEN
314 print_debug('lpn_id being used to create a mo:'||l_lpn_id);
315 END IF;
316
317 IF l_parent_transaction_type IN ('ACCEPT', 'REJECT') THEN
318 l_progress := '100';
319 IF i.quantity < 0 THEN
320 -- it is -ve correction of inspected qty
321 -- so create a mo with inspection status of null.
322 l_inspect := 1;
323 ELSIF l_parent_transaction_type = 'ACCEPT' THEN
324 l_inspect := 2;
325 ELSE l_inspect := 3;
326 END IF;
327 ELSE
328 l_progress := '110';
329 IF i.po_line_location_id IS NOT NULL THEN
330 BEGIN
331 l_progress := '112';
332 SELECT receiving_routing_id
333 INTO l_routing_id
334 FROM po_line_locations_all
335 WHERE line_location_id = i.po_line_location_id;
336 EXCEPTION
337 WHEN OTHERS THEN NULL;
338 END;
339 l_progress := '114';
340 ELSIF i.oe_order_line_id IS NOT NULL THEN
341 IF Nvl(i.inspection_required_flag,'N') = 'Y' THEN
342 l_routing_id := 2;
343 ELSE
344 BEGIN
345 l_progress := '116';
346 SELECT Nvl(receiving_routing_id,1)
347 INTO l_routing_id
348 FROM rcv_parameters
349 WHERE organization_id = i.organization_id;
350 EXCEPTION
351 WHEN OTHERS THEN NULL;
352 END;
353 l_progress := '117';
354 END IF;
355 ELSE
356 -- it is a intransit shipment.
357 l_transfer_org_id := i.from_organization_id;
358 BEGIN
359 l_progress := '118';
360 SELECT routing_header_id
361 INTO l_routing_id
362 FROM rcv_shipment_lines
363 WHERE shipment_line_id = i.shipment_line_id;
364 EXCEPTION
365 WHEN OTHERS THEN NULL;
366 END;
367 l_progress := '119';
368 END IF;
369 l_progress := '120';
370 IF (l_debug = 1) THEN
371 print_debug('routing id found was :'||l_routing_id);
372 END IF;
373 IF l_routing_id = 2 THEN
374 -- Bug #1939258
375 -- IF l_parent_transaction_type = 'DELIVER' THEN
376 IF l_parent_transaction_type = 'DELIVER' OR
377 (i.transaction_type = 'RETURN TO RECEIVING' AND
378 l_rtr_parent_txn_type = 'DELIVER') THEN
379 -- If it is a deliver txn and we are doing a -ve
380 -- correction then the inspection_status need to
381 -- be determined based on the transaction_type
382 -- of the parent_transaction_id of the deliver txn
383 BEGIN
384 l_progress := '130';
385 SELECT transaction_type
386 INTO l_grand_parent_txn_type
387 FROM rcv_transactions rt
388 WHERE transaction_id = (SELECT rt2.parent_transaction_id
389 FROM rcv_transactions rt2
390 WHERE rt2.transaction_id = i.parent_transaction_id);
391 EXCEPTION
392 WHEN OTHERS THEN
393 IF (l_debug = 1) THEN
394 print_debug('Could not get the grand parent txn. type:'||i.parent_transaction_id);
395 END IF;
396 l_inspect := NULL;
397 END;
398 l_progress := '140';
399 IF (l_debug = 1) THEN
400 print_debug('Grand parent txn. type:'||l_grand_parent_txn_type);
401 END IF;
402 IF l_grand_parent_txn_type = 'ACCEPT' THEN
403 l_inspect := 2;
404 ELSIF l_grand_parent_txn_type = 'REJECT' THEN
405 l_inspect := 3;
406 ELSE
407 l_inspect := 1;
408 END IF;
409 ELSE
410 l_inspect := 1;
411 END IF;
412 ELSE
413 l_inspect := NULL;
414 END IF;
415 END IF;
416 l_progress := '150';
417
418 SELECT uom_code INTO l_uom_code FROM mtl_item_uoms_view
419 WHERE organization_id = i.organization_id
420 AND unit_of_measure = i.unit_of_measure
421 AND inventory_item_id = i.item_id;
422 l_progress := '160';
423 IF (l_debug = 1) THEN
424 print_debug('Calling create mo for correction..asn_line_flag:'||i.asn_line_flag);
425 END IF;
426 IF i.asn_line_flag IS NOT NULL THEN
427 IF i.asn_line_flag = 'Y' THEN
428 INV_RCV_STD_RCPT_APIS.create_mo_for_correction(p_move_order_header_id => l_move_order_header_id,
429 p_po_line_location_id => NULL,
430 p_shipment_line_id => i.shipment_line_id,
431 p_oe_order_line_id => NULL,
432 p_routing => 1,
433 p_lot_control_code => i.lot_control_code,
434 p_org_id => i.organization_id,
435 p_item_id => i.item_id,
436 p_qty => abs(i.quantity),
437 p_uom_code => l_uom_code,
438 p_lpn => l_lpn_id,
439 p_revision => i.item_revision,
440 p_inspect => l_inspect,
441 p_txn_source_id => i.interface_transaction_id,
442 x_status => x_return_status,
443 x_message => x_msg_data,
444 p_transfer_org_id => l_transfer_org_id,
445 p_wms_process_flag => 1
446 );
447 ELSE
448 INV_RCV_STD_RCPT_APIS.create_mo_for_correction(p_move_order_header_id => l_move_order_header_id,
449 p_po_line_location_id => i.po_line_location_id,
450 p_shipment_line_id => i.shipment_line_id,
451 p_oe_order_line_id => i.oe_order_line_id,
452 p_routing => 1,
453 p_lot_control_code => i.lot_control_code,
454 p_org_id => i.organization_id,
455 p_item_id => i.item_id,
456 p_qty => abs(i.quantity),
457 p_uom_code => l_uom_code,
458 p_lpn => l_lpn_id,
459 p_revision => i.item_revision,
460 p_inspect => l_inspect,
461 p_txn_source_id => i.interface_transaction_id,
462 x_status => x_return_status,
463 x_message => x_msg_data,
464 p_transfer_org_id => l_transfer_org_id,
465 p_wms_process_flag => 1
466 );
467 END IF;
468 END IF;
469 l_progress := '170';
470
471 -- Make a call to pregenerate suggestions for the LPN for
472 -- which the move order is being created.
473 BEGIN
474 IF (l_debug = 1) THEN
475 print_debug('Calling the pregeneration API',4);
476 END IF;
477 wms_putaway_suggestions.start_pregenerate_program(p_org_id =>i.organization_id,
478 p_lpn_id => l_lpn_id,
479 x_return_status => l_return_status,
480 x_msg_count => l_msg_count,
481 x_msg_data => l_msg_data);
482 IF (l_debug = 1) THEN
483 print_debug('After calling the pregen API'||l_return_status||':'||l_msg_data||':'||l_msg_count,4);
484 END IF;
485 EXCEPTION
486 WHEN OTHERS THEN
487 IF (l_debug = 1) THEN
488 print_debug('Exception in calling the pregen API',1);
489 END IF;
490 END;
491
492 -- Donot need to do this since it will get updated in
493 -- the end anyway.
494 --UPDATE mtl_txn_request_lines
495 --SET wms_process_flag = 1
496 --WHERE txn_source_id = i.interface_transaction_id;
497 END IF;
498 l_update_lpn := l_lpn_id;
499 IF i.transaction_type = 'RETURN TO RECEIVING' THEN
500 l_update_lpn := i.lpn_id;
501 END IF;
502 -- In mark_returns and pack_into_receiving we update
503 -- mol.txn_source_line_detail_id so use that to update the
504 -- wms_process_flag since the new ones are anyway created with a
505 -- wms_process_flag = 1.
506 IF (l_debug = 1) THEN
507 print_debug('Updating MOLs for:'||l_update_lpn);
508 END IF;
509 UPDATE mtl_txn_request_lines
510 SET wms_process_flag = 1
511 , txn_source_line_detail_id = NULL
512 WHERE lpn_id = l_update_lpn
513 AND txn_source_line_detail_id = i.interface_transaction_id;
514 END LOOP; -- c_create_mo
515
516 IF (l_debug = 1) THEN
517 print_debug('Finished creating new MOLs');
518 END IF;
519 l_progress := '180';
520
521 FOR i IN c_rt_for_mo_udpate LOOP
522 IF (l_debug = 1) THEN
523 print_debug('Updating MOL for:'||i.transaction_id||':'||i.transaction_type);
524 END IF;
525 IF i.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') THEN
526
527 l_lpn_id := i.lpn_id;
528
529 SELECT transaction_type INTO l_rtv_parent_txn_type
530 FROM rcv_transactions
531 WHERE transaction_id = i.parent_transaction_id;
532 l_progress := '210';
533 IF (l_debug = 1) THEN
534 print_debug('RTV parent txn type='|| l_rtv_parent_txn_type);
535 END IF;
536
537 ELSIF i.transaction_type = 'RETURN TO RECEIVING' THEN
538 -- this is for the updation of mol.process_flag to 1 from 2
539 -- as for a return to receiving transaction created as part
540 -- of a rtv/rtc from inventory we dont need to update or create
541 -- any mol but we do need to update the process_flag as that
542 -- was updated.
543 l_lpn_id := NULL;
544 UPDATE mtl_txn_request_lines
545 SET wms_process_flag = 1
546 , txn_source_line_detail_id = NULL
547 WHERE lpn_id = i.lpn_id
548 AND txn_source_line_detail_id = i.interface_transaction_id;
549
550 ELSIF i.transaction_type = 'CORRECT' THEN
551
552 l_progress := '250';
553 SELECT transaction_type into l_parent_transaction_type
554 FROM rcv_transactions
555 WHERE transaction_id = i.parent_transaction_id;
556 l_progress := '260';
557 IF (l_debug = 1) THEN
558 print_debug('parent transaction_type='|| l_parent_transaction_type);
559 END IF;
560
561 IF l_parent_transaction_type = 'RECEIVE' THEN
562
563 IF i.rt_quantity < 0 THEN
564 l_lpn_id := i.transfer_lpn_id;
565 ELSE
566 l_lpn_id := NULL; -- MO is created
567 END IF;
568
569 ELSIF l_parent_transaction_type = 'DELIVER' THEN
570
571 IF i.rt_quantity < 0 THEN
572 l_lpn_id := NULL; -- MO is created
573 ELSE
574 l_lpn_id := i.lpn_id;
575 END IF;
576
577 ELSIF l_parent_transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') THEN
578
579 IF i.rt_quantity < 0 THEN
580 l_lpn_id := NULL; -- MO is created
581 ELSE
582 l_lpn_id := i.lpn_id;
583 END IF;
584
585 ELSIF l_parent_transaction_type IN ('ACCEPT', 'REJECT') THEN
586 -- For inspected qty. we have already created the new mo
587 -- for the new quantity with proper inspection status
588 -- but we still need to update the old mo.
589 IF i.rt_quantity < 0 THEN
590 l_lpn_id := i.transfer_lpn_id;
591 ELSE
592 l_lpn_id := i.lpn_id;
593 END IF;
594
595 END IF;
596
597 END IF;
598 l_progress := '270';
599 IF l_lpn_id IS NOT NULL THEN
600 IF (l_debug = 1) THEN
601 print_debug('lpn_id being used to update a mo:'||l_lpn_id);
602 END IF;
603 IF i.po_line_location_id IS NOT NULL THEN
604 l_reference := 'PO_LINE_LOCATION_ID';
605 l_reference_id := i.po_line_location_id;
606 ELSIF i.oe_order_line_id IS NOT NULL THEN
607 l_reference := 'ORDER_LINE_ID';
608 l_reference_id := i.oe_order_line_id;
609 ELSE
610 l_reference := 'SHIPMENT_LINE_ID';
611 l_reference_id := i.shipment_line_id;
612 END IF;
613
614 --If received against an ASN then use shipment_line_id instead of po_line_location_id
615 IF i.asn_line_flag IS NOT NULL THEN
616 IF i.asn_line_flag = 'Y' THEN
617 l_reference := 'SHIPMENT_LINE_ID';
618 l_reference_id := i.shipment_line_id;
619 END IF;
620 END IF;
621
622 IF (l_debug = 1) THEN
623 print_debug('referece:'||l_reference||':referece_id:'||l_reference_id);
624 END IF;
625 /* Converting Receiving Txn qty to Primary UOM */
626 l_rt_qty_in_primary_uom := 0;
627 l_progress := '280';
628 po_uom_s.uom_convert(abs(i.rt_quantity),
629 i.unit_of_measure,
630 i.item_id,
631 i.primary_unit_of_measure,
632 l_rt_qty_in_primary_uom);
633 l_progress := '290';
634 IF (l_debug = 1) THEN
635 print_debug('l_rt_qty_in_primary_uom=' || l_rt_qty_in_primary_uom || ', rt_quantity=' || i.rt_quantity || ', unit_of_measure=' || i.unit_of_measure || ', primary_unit_of_measure=' || i.primary_unit_of_measure);
636 END IF;
637
638 -- For MOL update of ACCEPT/REJECT transaction we have to select
639 -- MOL with proper status.
640 IF (l_parent_transaction_type IN ('ACCEPT', 'REJECT')
641 OR l_rtv_parent_txn_type IN ('ACCEPT', 'REJECT')) THEN
642 IF (l_parent_transaction_type IN ('ACCEPT','REJECT')) THEN
643 IF i.rt_quantity > 0 THEN
644 IF (l_debug = 1) THEN
645 print_debug('+ve correction for inspect for mol update');
646 END IF;
647 l_inspection_status := 1;
648 ELSIF l_parent_transaction_type = 'ACCEPT' THEN
649 l_inspection_status := 2;
650 ELSE
651 l_inspection_status := 3;
652 END IF;
653 ELSIF l_rtv_parent_txn_type IN ('ACCEPT','REJECT') THEN
654 IF (l_rtv_parent_txn_type = 'ACCEPT') THEN
655 l_inspection_status := 2;
656 ELSE
657 l_inspection_status := 3;
658 END IF;
659 END IF;
660 ELSIF (l_parent_transaction_type IN ('RECEIVE', 'DELIVER')
661 OR l_rtv_parent_txn_type = 'RECEIVE') THEN
662 -- Inspection status should be null or 1 based on the routing
663 -- determined for the receipt being corrected which should be
664 -- the same as it was when the receipt took place.
665 l_progress := '300';
666 IF i.po_line_location_id IS NOT NULL THEN
667 l_progress := '310';
668 BEGIN
669 SELECT receiving_routing_id
670 INTO l_routing_id
671 FROM po_line_locations_all
672 WHERE line_location_id = i.po_line_location_id;
673 EXCEPTION
674 WHEN OTHERS THEN NULL;
675 END;
676 l_progress := '320';
677 ELSIF i.oe_order_line_id IS NOT NULL THEN
678 IF Nvl(i.inspection_required_flag,'N') = 'Y' THEN
679 l_routing_id := 2;
680 ELSE
681 BEGIN
682 l_progress := '330';
683 SELECT Nvl(receiving_routing_id,1)
684 INTO l_routing_id
685 FROM rcv_parameters
686 WHERE organization_id = i.organization_id;
687 EXCEPTION
688 WHEN OTHERS THEN NULL;
689 END;
690 l_progress := '340';
691 END IF;
692 ELSE
693 -- it is a intransit shipment.
694 BEGIN
695 l_progress := '350';
696 SELECT routing_header_id
697 INTO l_routing_id
698 FROM rcv_shipment_lines
699 WHERE shipment_line_id = i.shipment_line_id;
700 EXCEPTION
701 WHEN OTHERS THEN NULL;
702 END;
703 l_progress := '360';
704 END IF;
705
706 l_progress := '370';
707 IF (l_debug = 1) THEN
708 print_debug('routing id found was :'||l_routing_id);
709 END IF;
710 IF l_routing_id = 2 THEN
711 IF l_parent_transaction_type = 'DELIVER' THEN
712 -- If it is a deliver txn and we are doing a -ve
713 -- correction then the inspection_status need to
714 -- be determined based on the transaction_type
715 -- of the parent_transaction_id of the deliver txn
716 BEGIN
717 l_progress := '380';
718 SELECT transaction_type
719 INTO l_grand_parent_txn_type
720 FROM rcv_transactions rt
721 WHERE transaction_id = (SELECT rt2.parent_transaction_id
722 FROM rcv_transactions rt2
723 WHERE rt2.transaction_id = i.parent_transaction_id);
724 EXCEPTION
725 WHEN OTHERS THEN
726 IF (l_debug = 1) THEN
727 print_debug('Could not get the grand parent txn. type:'||i.parent_transaction_id);
728 END IF;
729 l_inspection_status := NULL;
730 END;
731 l_progress := '390';
732 IF (l_debug = 1) THEN
733 print_debug('Grand parent txn. type:'||l_grand_parent_txn_type);
734 END IF;
735 IF l_grand_parent_txn_type = 'ACCEPT' THEN
736 l_inspection_status := 2;
737 ELSIF l_grand_parent_txn_type = 'REJECT' THEN
738 l_inspection_status := 3;
739 ELSE
740 l_inspection_status := 1;
741 END IF;
742 ELSE
743 l_inspection_status := 1;
744 END IF;
745 ELSE
746 l_inspection_status := NULL;
747 END IF;
748 END IF;
749 IF (l_debug = 1) THEN
750 print_debug('Opening MO cursor for:'||i.transaction_id||':'||
751 i.item_id||':'||i.lot_number||':'||l_reference||':'||
752 l_reference_id||':'||l_lpn_id||':'||l_inspection_status||':'||
753 i.organization_id);
754 END IF;
755 FOR j IN c_update_mo(i.transaction_id, i.item_id, i.lot_number,
756 l_reference, l_reference_id, l_lpn_id,
757 l_inspection_status, i.organization_id) LOOP
758 IF (l_debug = 1) THEN
759 print_debug('Opened update MOL for line_id:'||j.line_id);
760 END IF;
761 -- Converting Move Order Line qty to Primary UOM
762 l_progress := '450';
763 SELECT unit_of_measure INTO l_mol_unit_of_measure
764 FROM mtl_item_uoms_view
765 WHERE organization_id = i.organization_id
766 AND uom_code = j.mol_uom_code
767 AND inventory_item_id = i.item_id;
768
769 l_progress := '460';
770 po_uom_s.uom_convert(abs(j.mol_available_quantity),
771 l_mol_unit_of_measure,
772 i.item_id,
773 i.primary_unit_of_measure,
774 l_mol_qty_in_primary_uom);
775 l_progress := '470';
776
777 po_uom_s.uom_convert(abs(l_rt_qty_in_primary_uom),
778 i.primary_unit_of_measure,
779 i.item_id,
780 l_mol_unit_of_measure,
781 l_rt_qty_in_mol_uom);
782
783 l_progress := '480';
784 IF (l_debug = 1) THEN
785 print_debug('l_mol_qty_in_primary_uom=' || l_mol_qty_in_primary_uom
786 || ', l_rt_qty_in_mol_uom=' || l_rt_qty_in_mol_uom);
787 END IF;
788
789 -- Only for those cases in which MOL qty. has to be decreased.
790 -- Which are -ve of receive, +ve and -ve of accept/reject
791 -- +ve of deliver, +ve of RTV/RTC
792 -- RTV/RTC also need to decrease the MOL.
793 IF (i.transaction_type IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
794 OR (i.transaction_type = 'CORRECT' AND
795 ((l_parent_transaction_type IN ('RECEIVE','ACCEPT','REJECT')
796 AND i.rt_quantity < 0)
797 OR (l_parent_transaction_type IN ('ACCEPT', 'REJECT',
798 'DELIVER',
799 'RETURN TO VENDOR',
800 'RETURN TO CUSTOMER')
801 AND i.rt_quantity > 0)))) THEN
802 IF (l_debug = 1) THEN
803 print_debug('MOL quantity has to be reduced');
804 END IF;
805 IF l_mol_qty_in_primary_uom >= l_rt_qty_in_primary_uom THEN
806
807 /* We have enough qty in MO Line, hence consume it and exit */
808 /* Substract the MO Line qty with l_mol_qty_in_primary_uom */
809
810 UPDATE mtl_txn_request_lines
811 SET quantity = quantity - l_rt_qty_in_mol_uom
812 , primary_quantity = primary_quantity - l_rt_qty_in_primary_uom
813 WHERE header_id = j.header_id
814 AND nvl(lot_number,'@@@') = nvl(j.lot_number,'@@@')
815 AND line_id = j.line_id;
816
817 IF (l_debug = 1) THEN
818 print_debug('Before update of mtrl within IF');
819 END IF;
820
821
822 -- 4389811 updating line status to 5 for closed MO
823 UPDATE mtl_txn_request_lines
824 SET line_status=5
825 WHERE header_id = j.header_id
826 AND line_id = j.line_id
827 AND ( nvl(quantity,0) = 0 OR
828 nvl(quantity,0)=nvl(quantity_delivered,0)
829 ) ;
830
831 IF (l_debug = 1) THEN
832 print_debug('After update of mtrl within IF');
833 END IF;
834
835 l_rt_qty_in_primary_uom := 0;
836 IF (l_debug = 1) THEN
837 print_debug('updated mol:'||j.line_id||' and exiting');
838 END IF;
839 exit;
840
841 ELSE
842 /* We don't have enough qty in MO Line, hence consume it and proceed to next loop */
843 /* Substract the MO Line qty with l_mol_qty_in_primary_uom */
844
845 UPDATE mtl_txn_request_lines
846 SET quantity = quantity - abs(j.mol_available_quantity) --l_rt_qty_in_mol_uom
847 , primary_quantity = primary_quantity - l_mol_qty_in_primary_uom
848 WHERE header_id = j.header_id
849 AND nvl(lot_number,'@@@') = nvl(j.lot_number,'@@@')
850 AND line_id = j.line_id;
851
852 IF (l_debug = 1) THEN
853 print_debug(' Before update of mtrl within ELSE');
854 END IF;
855
856 -- 4389811 updating line status to 5 for closed MO
857 UPDATE mtl_txn_request_lines
858 SET line_status=5
859 WHERE header_id = j.header_id
860 AND line_id = j.line_id
861 AND ( nvl(quantity,0) = 0 OR
862 nvl(quantity,0)=nvl(quantity_delivered,0)
863 );
864
865 IF (l_debug = 1) THEN
866 print_debug(' After update of mtrl within ELSE');
867 END IF;
868
869 IF (l_debug = 1) THEN
870 print_debug('updated mol:'||j.line_id);
871 END IF;
872 l_rt_qty_in_primary_uom := l_rt_qty_in_primary_uom - l_mol_qty_in_primary_uom;
873
874 END IF;
875 ELSE
876 IF (l_debug = 1) THEN
877 print_debug('MOL quantity has to be increased');
878 print_debug('We create new MOL for inc. qty. so should not have come here');
879 END IF;
880 END IF;
881
882 END LOOP; -- c_update_mo
883 IF (l_debug = 1) THEN
884 print_debug('finished finding mol for update');
885 END IF;
886 IF l_rt_qty_in_primary_uom > 0 THEN
887 IF (l_debug = 1) THEN
888 print_debug('Should not have happened. Not enough quantity in MOL');
889 END IF;
890 /* There is no enought qty in all the MO Lines and hence through an exception */
891 x_return_status := FND_API.G_RET_STS_ERROR;
892 RAISE fnd_api.g_exc_error; --return;
893 END IF;
894
895 UPDATE mtl_txn_request_lines
896 SET wms_process_flag = 1
897 , txn_source_line_detail_id = NULL
898 WHERE lpn_id = l_lpn_id
899 AND txn_source_line_detail_id = i.interface_transaction_id;
900
901 -- Make a call to pregenerate suggestions for the LPN for
902 -- which the move order is being updated after deleting the
903 -- existing suggestions.
904 BEGIN
905
906 SELECT pregen_putaway_tasks_flag
907 INTO l_pregen_putaway_tasks_flag
908 FROM mtl_parameters
909 WHERE organization_id = i.organization_id;
910
911 IF (l_debug = 1) THEN
912 print_debug('l_pregen_putaway_tasks_flag: ' || l_pregen_putaway_tasks_flag);
913 END IF;
914
915 IF l_pregen_putaway_tasks_flag = 1 THEN
916 IF (l_debug = 1) THEN
917 print_debug('Calling the suggestion clean up API',4);
918 END IF;
919 wms_putaway_suggestions.cleanup_suggestions(p_org_id=>i.organization_id,
920 p_lpn_id => l_lpn_id,
921 x_return_status => l_return_status,
922 x_msg_count => l_msg_count,
923 x_msg_data => l_msg_data);
924 IF (l_debug = 1) THEN
925 print_debug('After calling the suggestion clean up API'||l_return_status||':'||l_msg_data||':'||l_msg_count,4);
926 END IF;
927 IF l_return_status = fnd_api.g_ret_sts_error THEN
928 IF (l_debug = 1) THEN
929 print_debug('Error while cleaning up the suggestions',2);
930 print_debug('Not calling to pregenerate the suggestions',4);
931 END IF;
932 ELSE
933 /* BUG 3646129 In WMSPRGEB.pls wms_putaway_suggestions.start_pregenerate_program() *
934 * new error conditions have been added which check for the lpn_context of lpn and *
935 * will return error if the lpn_context of the passed lpn is othere than 2 or 3 so *
936 * changes are being made to call this program only if the lpn_context is 2 or 3 */
937
938 SELECT lpn_context
939 INTO l_lpn_context
940 FROM wms_license_plate_numbers
941 WHERE lpn_id = l_lpn_id;
942 IF l_lpn_context in (2,3) THEN
943 wms_putaway_suggestions.start_pregenerate_program(p_org_id =>i.organization_id,
944 p_lpn_id => l_lpn_id,
945 x_return_status => l_return_status,
946 x_msg_count => l_msg_count,
947 x_msg_data => l_msg_data);
948 IF (l_debug = 1) THEN
949 print_debug('After calling the pregen API'||l_return_status||':'||l_msg_data||':'||l_msg_count,4);
950 END IF;
951 ELSE
952 print_debug('lpn_context not in 2 or 3 not calling to pregenerate the suggestions',4);
953 END IF; -- BUG 3646129
954 END IF;
955 END IF;
956 EXCEPTION
957 WHEN OTHERS THEN
958 IF (l_debug = 1) THEN
959 print_debug('Exception in calling the pregen/cleanup API',1);
960 END IF;
961 END;
962
963 END IF; -- l_lpn_id IS NULL
964
965 END LOOP; -- c_rt_for_mo_udpate
966 IF (l_debug = 1) THEN
967 print_debug('Finished updating mol');
968 END IF;
969 EXCEPTION
970 WHEN FND_API.g_exc_error THEN
971 IF (l_debug = 1) THEN
972 print_debug('maintain_move_orders : execution error');
973 END IF;
974
975 x_return_status := FND_API.G_RET_STS_ERROR;
976 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
977 WHEN OTHERS THEN
978 IF (l_debug = 1) THEN
979 print_debug('Maintain MO - exception when others at:'||l_progress||': ' || sqlerrm || ':' || sqlcode);
980 END IF;
981 x_return_status := FND_API.G_RET_STS_ERROR;
982 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
983 x_msg_data := x_msg_data||sqlerrm;
984 END maintain_move_orders;
985
986 PROCEDURE PackUnpack_Container
987 ( x_return_status OUT NOCOPY VARCHAR2,
988 x_msg_count OUT NOCOPY NUMBER,
989 x_msg_data OUT NOCOPY VARCHAR2,
990 p_lpn_id IN NUMBER,
991 p_content_item_id IN NUMBER := NULL,
992 p_revision IN VARCHAR2 := NULL,
993 p_lot_number IN VARCHAR2 := NULL,
994 p_from_serial_number IN VARCHAR2 := NULL,
995 p_to_serial_number IN VARCHAR2 := NULL,
996 p_quantity IN NUMBER := 1,
997 p_uom IN VARCHAR2 := NULL,
998 p_organization_id IN NUMBER,
999 p_subinventory IN VARCHAR2 := NULL,
1000 p_locator_id IN NUMBER := NULL,
1001 p_operation IN NUMBER,
1002 p_cost_group_id IN NUMBER := NULL,
1003 p_source_header_id IN NUMBER := NULL,
1004 p_source_name IN VARCHAR2 := NULL
1005 )
1006 IS
1007
1008 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1009 l_transactable_flag VARCHAR2(1) := NULL; --bug 5068433
1010 l_validation_level NUMBER := fnd_api.g_valid_level_full; --bug 5068433
1011 BEGIN
1012
1013 IF (l_debug = 1) THEN
1014 print_debug('=== Calling WMS_Container_PUB.PackUnpack_Container ===');
1015 print_debug('p_lpn_id =>' || p_lpn_id);
1016 print_debug('p_lot_number =>' || p_lot_number);
1017 print_debug('p_from_serial_number =>' || p_from_serial_number);
1018 print_debug('p_to_serial_number =>' || p_to_serial_number);
1019 print_debug('p_quantity =>' || p_quantity);
1020 print_debug('p_uom =>' || p_uom);
1021 print_debug('p_subinventory =>' || p_subinventory);
1022 print_debug('p_locator_id =>' || p_locator_id);
1023 print_debug('p_operation =>' || p_operation);
1024 print_debug('p_source_header_id =>' || p_source_header_id);
1025 print_debug('p_source_name =>' || p_source_name);
1026 print_debug('P_COST_GROUP_ID =>' || P_COST_GROUP_ID);
1027 END IF;
1028
1029 --this block is added for bug 5068433
1030 BEGIN
1031
1032 SELECT mtl_transactions_enabled_flag
1033 INTO l_transactable_flag
1034 FROM mtl_system_items_b
1035 WHERE inventory_item_id = p_content_item_id
1036 AND organization_id = p_organization_id;
1037
1038 EXCEPTION
1039
1040 WHEN OTHERS THEN
1041 l_transactable_flag := 'Y';
1042 END;
1043
1044 IF (l_debug = 1) THEN
1045 print_debug('l_transactable_flag =>' || l_transactable_flag);
1046 END IF;
1047
1048 IF l_transactable_flag = 'N' THEN --bug 5048633
1049 l_validation_level := fnd_api.g_valid_level_none;
1050 END IF;
1051
1052
1053 WMS_Container_PUB.PackUnpack_Container(
1054 p_api_version => 1.0,
1055 p_validation_level => l_validation_level,--bug 5048633
1056 x_return_status => x_return_status,
1057 x_msg_count => x_msg_count,
1058 x_msg_data => x_msg_data,
1059 p_lpn_id => p_lpn_id,
1060 p_content_item_id => p_content_item_id,
1061 p_revision => p_revision,
1062 p_lot_number => p_lot_number,
1063 p_from_serial_number => p_from_serial_number,
1064 p_to_serial_number => p_to_serial_number,
1065 p_quantity => p_quantity,
1066 p_uom => p_uom,
1067 p_organization_id => p_organization_id,
1068 p_subinventory => p_subinventory,
1069 p_locator_id => p_locator_id,
1070 p_operation => p_operation,
1071 p_source_header_id => p_source_header_id,
1072 p_source_name => p_source_name,
1073 p_cost_group_id => p_cost_group_id
1074 );
1075
1076 IF (l_debug = 1) THEN
1077 print_debug('***************Output Parameters********************');
1078 print_debug('x_return_status =>' || x_return_status);
1079 print_debug('x_msg_count =>' || x_msg_count);
1080 print_debug('x_msg_data =>' || x_msg_data);
1081 END IF;
1082
1083 --IF (x_return_status <> FND_API.G_RET_STS_SUCCESS or x_msg_count > 0) THEN
1084 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1085 x_return_status := 'E';
1086 IF (l_debug = 1) THEN
1087 print_debug('Errored out...');
1088 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1089 print_debug(x_msg_data);
1090 END IF;
1091 END IF;
1092
1093 END PackUnpack_Container;
1094
1095 /* Called from INVRCVFB.pls
1096 ** This procedure is used to unpack LPN for Return To Vendor and Correction
1097 ** Transactions with parent transaction type = RECEIVING as Inventory Manager
1098 ** is not called for these transactions.
1099 */
1100
1101 PROCEDURE txn_complete(
1102 p_group_id IN NUMBER,
1103 p_txn_status IN VARCHAR2, -- TRUE/FALSE
1104 p_txn_mode IN VARCHAR2, -- ONLINE/IMMEDIATE
1105 x_return_status OUT NOCOPY VARCHAR2,
1106 x_msg_data OUT NOCOPY VARCHAR2,
1107 x_msg_count OUT NOCOPY NUMBER) IS
1108
1109 /* The first part of the cursor is for Plain and Lot controlled items.
1110 ** The second part of the cursor is for Serial and Lot/Serial controlled items.
1111 */
1112
1113 CURSOR c_lpn_cnts IS
1114 SELECT wlpnc.organization_id
1115 , wlpn.subinventory_code subinventory
1116 , wlpn.locator_id
1117 , rt.lpn_id lpn_id
1118 , rt.transfer_lpn_id
1119 , wlpnc.inventory_item_id
1120 , wlpnc.revision
1121 , wlpnc.lot_number
1122 , to_char(null) serial_number
1123 , wlpnc.quantity
1124 , wlpnc.uom_code
1125 , rt.transaction_type
1126 , rt.interface_transaction_id
1127 , wlpnc.COST_GROUP_ID cg_id
1128 , rt.destination_type_code
1129 , rt.quantity rt_quantity
1130 , rt.parent_transaction_id
1131 FROM wms_license_plate_numbers wlpn, wms_lpn_contents wlpnc, rcv_transactions rt
1132 WHERE rt.group_id = p_group_id
1133 AND (((( rt.transaction_type = 'RETURN TO VENDOR'
1134 AND rt.lpn_id IS NOT NULL -- 3603808
1135 )
1136 OR
1137 ( rt.transaction_type = 'RETURN TO CUSTOMER'
1138 -- AND rt.transfer_lpn_id IS NOT NULL fix for 4389811
1139 AND rt.lpn_id IS NOT NULL
1140 ))
1141 -- to eliminate the row being selected for a rtv
1142 -- from inventory as pack unpack for that is already
1143 -- taken care of in inventory tm.
1144 AND NOT exists (SELECT 1
1145 FROM rcv_transactions rt2
1146 WHERE rt2.interface_transaction_id = rt.interface_transaction_id
1147 AND rt2.transaction_type = 'RETURN TO RECEIVING'
1148 AND rt2.group_id = p_group_id))
1149 OR (rt.transaction_type = 'CORRECT')
1150 OR (rt.transaction_type = 'RETURN TO RECEIVING'
1151 AND rt.transfer_lpn_id IS NOT NULL
1152 AND rt.lpn_id IS NOT NULL))
1153 AND rt.user_entered_flag = 'Y'
1154 AND wlpnc.source_name = rt.transaction_type
1155 AND wlpnc.source_header_id = rt.interface_transaction_id
1156 AND nvl(wlpnc.serial_summary_entry,2) <> 1
1157 AND wlpn.lpn_id = wlpnc.parent_lpn_id
1158 UNION ALL
1159 SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
1160 msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
1161 msn.inventory_item_id, msn.revision, msn.lot_number,
1162 msn.serial_number, to_number(null) quantity, wlpnc.uom_code uom_code, rt.transaction_type,
1163 rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1164 rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1165 FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions rt
1166 WHERE msn.last_txn_source_name = rt.transaction_type
1167 AND msn.last_txn_source_id = rt.interface_transaction_id
1168 AND rt.group_id = p_group_id
1169 AND (((( rt.transaction_type = 'RETURN TO VENDOR' -- 3603808
1170 AND rt.lpn_id IS NOT NULL
1171 )
1172 OR
1173 ( rt.transaction_type = 'RETURN TO CUSTOMER'
1174 -- AND rt.transfer_lpn_id IS NOT NULL fix for 4389811
1175 AND rt.lpn_id IS NOT NULL
1176 ))
1177 AND NOT exists (SELECT 1
1178 FROM rcv_transactions rt2
1179 WHERE rt2.interface_transaction_id = rt.interface_transaction_id
1180 AND rt2.transaction_type = 'RETURN TO RECEIVING'
1181 AND rt2.group_id = p_group_id))
1182 OR (rt.transaction_type = 'CORRECT') OR (rt.transaction_type = 'RETURN TO RECEIVING'
1183 AND rt.transfer_lpn_id IS NOT NULL
1184 AND rt.lpn_id IS NOT NULL))
1185 AND rt.user_entered_flag = 'Y'
1186 AND wlpnc.parent_lpn_id = msn.lpn_id
1187 AND wlpnc.inventory_item_id = msn.inventory_item_id;
1188
1189 -- Bug# 3281512 - Performance Fixes
1190 -- Also select for the item_id column in RTI so we can use
1191 -- this in looking up the values in MSN.
1192 CURSOR c_failure IS
1193 SELECT transaction_type, interface_transaction_id, item_id
1194 FROM rcv_transactions_interface rti
1195 WHERE rti.group_id = p_group_id
1196 AND rti.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER','RETURN TO RECEIVING','CORRECT');
1197
1198 CURSOR c_newly_packed IS
1199 SELECT wlpnc.organization_id
1200 , rti.lpn_id lpn_id
1201 , rti.transfer_lpn_id
1202 , wlpnc.inventory_item_id
1203 , wlpnc.revision
1204 , wlpnc.lot_number
1205 , to_char(null) serial_number
1206 , wlpnc.quantity
1207 , wlpnc.uom_code
1208 , rti.transaction_type
1209 , rti.interface_transaction_id
1210 , rti.destination_type_code
1211 , rti.quantity rti_quantity
1212 , rti.parent_transaction_id
1213 FROM wms_lpn_contents wlpnc, rcv_transactions_interface rti, rcv_transactions rt
1214 WHERE rti.group_id = p_group_id
1215 AND rti.transaction_type = 'CORRECT'
1216 AND rt.transaction_id = rti.parent_transaction_id
1217 AND ((rt.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') AND rti.quantity < 0) OR
1218 (rt.transaction_type = 'RECEIVE' AND rti.quantity > 0))
1219 AND wlpnc.source_name = rti.transaction_type
1220 AND wlpnc.source_header_id = rti.interface_transaction_id
1221 AND nvl(wlpnc.serial_summary_entry,2) <> 1
1222 UNION ALL
1223 SELECT msn.current_organization_id organization_id
1224 , rti.lpn_id
1225 , rti.transfer_lpn_id
1226 , msn.inventory_item_id
1227 , msn.revision
1228 , msn.lot_number
1229 , msn.serial_number
1230 , to_number(null) quantity
1231 , wlpnc.uom_code
1232 , rti.transaction_type
1233 , rti.interface_transaction_id
1234 , rti.destination_type_code
1235 , rti.quantity rti_quantity
1236 , rti.parent_transaction_id
1237 FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions_interface rti, rcv_transactions rt
1238 WHERE msn.last_txn_source_name = rti.transaction_type
1239 AND msn.last_txn_source_id = rti.interface_transaction_id
1240 AND rti.group_id = p_group_id
1241 AND rti.transaction_type = 'CORRECT'
1242 AND rt.transaction_id = rti.parent_transaction_id
1243 AND ((rt.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') AND rti.quantity < 0) OR
1244 (rt.transaction_type = 'RECEIVE' AND rti.quantity > 0))
1245 AND wlpnc.parent_lpn_id = msn.lpn_id
1246 AND wlpnc.inventory_item_id = msn.inventory_item_id;
1247
1248 CURSOR c_neg_deliver_ser IS
1249 SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
1250 msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
1251 msn.inventory_item_id, msn.revision, msn.lot_number,
1252 msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1253 rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1254 rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1255 FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1256 WHERE rt.group_id = p_group_id
1257 AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1258 OR (rt.transaction_type = 'RETURN TO RECEIVING'
1259 AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
1260 AND msn.current_subinventory_code = rt.from_subinventory
1261 AND Nvl(msn.current_locator_id,-1) = Nvl(rt.from_locator_id,-1)
1262 AND msn.current_organization_id = rt.organization_id
1263 AND rsl.shipment_header_id = rt.shipment_header_id
1264 AND rsl.shipment_line_id = rt.shipment_line_id
1265 AND msn.inventory_item_id = rsl.item_id
1266 AND rt.user_entered_flag = 'Y'
1267 AND msn.current_status = 4
1268 AND exists (SELECT '1' FROM rcv_transactions rt2
1269 WHERE rt2.transaction_id = rt.parent_transaction_id
1270 AND rt2.transaction_type = 'DELIVER');
1271
1272 CURSOR c_neg_deliver_ser_lpng IS
1273 SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
1274 msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
1275 msn.inventory_item_id, msn.revision, msn.lot_number,
1276 msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1277 rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1278 rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1279 FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1280 WHERE rt.transaction_date >= (Sysdate - 1)
1281 AND rt.lpn_group_id = p_group_id
1282 AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1283 OR (rt.transaction_type = 'RETURN TO RECEIVING'
1284 AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
1285 AND msn.current_subinventory_code = rt.from_subinventory
1286 AND Nvl(msn.current_locator_id,-1) = Nvl(rt.from_locator_id,-1)
1287 AND msn.current_organization_id = rt.organization_id
1288 AND rsl.shipment_header_id = rt.shipment_header_id
1289 AND rsl.shipment_line_id = rt.shipment_line_id
1290 AND msn.inventory_item_id = rsl.item_id
1291 AND rt.user_entered_flag = 'Y'
1292 AND msn.current_status = 4
1293 AND exists (SELECT '1' FROM rcv_transactions rt2
1294 WHERE rt2.transaction_id = rt.parent_transaction_id
1295 AND rt2.transaction_type = 'DELIVER');
1296
1297 -- Added new cursor to address the Bug 4489361
1298 CURSOR c_neg_deliver_ser_1159 IS
1299 SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
1300 msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
1301 msn.inventory_item_id, msn.revision, msn.lot_number,
1302 msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1303 rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1304 rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1305 FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1306 WHERE rt.group_id = p_group_id
1307 AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1308 OR (rt.transaction_type = 'RETURN TO RECEIVING'
1309 AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
1310 AND msn.current_subinventory_code = rt.subinventory
1311 AND Nvl(msn.current_locator_id,-1) = Nvl(rt.locator_id,-1)
1312 AND msn.current_organization_id = rt.organization_id
1313 AND rsl.shipment_header_id = rt.shipment_header_id
1314 AND rsl.shipment_line_id = rt.shipment_line_id
1315 AND msn.inventory_item_id = rsl.item_id
1316 AND rt.user_entered_flag = 'Y'
1317 AND msn.current_status = 4
1318 AND exists (SELECT '1' FROM rcv_transactions rt2
1319 WHERE rt2.transaction_id = rt.parent_transaction_id
1320 AND rt2.transaction_type = 'DELIVER');
1321
1322 ret boolean;
1323 l_lpn_id number;
1324 l_pack_lpn NUMBER;
1325 l_unpack_lpn NUMBER;
1326 l_parent_transaction_type VARCHAR2(25);
1327 l_routing_header_id NUMBER;
1328 l_insp_status NUMBER;
1329 l_status NUMBER;
1330
1331 /* New vars needed for deleting reservations*/
1332 l_qry_res_rec INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
1333 l_res_rec_to_delete INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
1334 l_src_doc_code VARCHAR2(10);
1335 l_dem_src_type_id NUMBER;
1336 l_mtl_reservation_tbl INV_RESERVATION_GLOBAL.MTL_RESERVATION_TBL_TYPE;
1337 l_mtl_reservation_tbl_count NUMBER;
1338 l_dummy_sn INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
1339 l_error_code NUMBER;
1340 l_res_lpn_id NUMBER;
1341 l_unreserve_qty NUMBER;
1342 l_express_return VARCHAR2(1);
1343
1344 -- Bug# 3631611: Performance Fixes
1345 -- Break the c_reservation_csr into two cursors, one where
1346 -- p_txn_mode = 'LPN_GROUP' and one for all other cases.
1347 CURSOR c_reservation_csr IS
1348 SELECT rt.source_document_code,
1349 rt.organization_id,
1350 rsl.item_id,
1351 rt.subinventory,
1352 rt.locator_id,
1353 rt.from_subinventory,
1354 rt.from_locator_id,
1355 rt.lpn_id,
1356 rt.quantity
1357 FROM rcv_transactions rt, rcv_shipment_lines rsl
1358 WHERE rt.group_id = p_group_id
1359 AND p_txn_mode <> 'LPN_GROUP'
1360 AND rt.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
1361 AND rt.shipment_line_id = rsl.shipment_line_id;
1362
1363 CURSOR c_reservation_lpn_grp_csr IS
1364 SELECT rt.source_document_code,
1365 rt.organization_id,
1366 rsl.item_id,
1367 rt.subinventory,
1368 rt.locator_id,
1369 rt.from_subinventory,
1370 rt.from_locator_id,
1371 rt.lpn_id,
1372 rt.quantity
1373 FROM rcv_transactions rt, rcv_shipment_lines rsl
1374 WHERE rt.transaction_date >= (SYSDATE-1)
1375 AND rt.lpn_group_id = p_group_id
1376 AND p_txn_mode = 'LPN_GROUP'
1377 AND rt.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
1378 AND rt.shipment_line_id = rsl.shipment_line_id;
1379
1380 -- Bug# 3631611: Performance Fixes
1381 -- Also define a cursor record type to fetch the results into.
1382 -- Since both cursors have the same return values, we can use this
1383 -- record type for both.
1384 l_res_csr c_reservation_csr%ROWTYPE;
1385
1386 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1387 l_wms_po_j_higher BOOLEAN := FALSE;
1388
1389 -- Bug# 3281512 - Performance Fixes
1390 -- Cursor to get rid of the hash join problem
1391 CURSOR c_interface_txn_id IS
1392 SELECT interface_transaction_id
1393 FROM rcv_transactions
1394 WHERE group_id = p_group_id;
1395 l_interface_txn_id NUMBER;
1396 BEGIN
1397
1398 x_return_status := FND_API.G_RET_STS_SUCCESS;
1399 IF (l_debug = 1) THEN
1400 print_debug('======== Start txn_complete =========');
1401 print_debug(' p_group_id => ' || p_group_id);
1402 print_debug(' p_txn_status => ' || p_txn_status);
1403 print_debug(' p_txn_mode => ' || p_txn_mode);
1404 END IF;
1405
1406 /* FP-J Lot/Serial Support Enhancement
1407 * Read the currentand PO patch levels and set the flag (that would be used to
1408 * match the Lot Number and the LPN) accordingly
1409 */
1410 IF ((WMS_UI_TASKS_APIS.g_wms_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j) AND
1411 (WMS_UI_TASKS_APIS.g_po_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j_po)) THEN
1412 l_wms_po_j_higher := TRUE;
1413 IF (l_debug = 1) THEN
1414 print_debug('WMS and PO patch levels are J or higher', 4);
1415 END IF;
1416 ELSE
1417 l_wms_po_j_higher := FALSE;
1418 IF (l_debug = 1) THEN
1419 print_debug('Either WMS or/and PO patch level(s) are lower than J', 4);
1420 END IF;
1421 END IF;
1422
1423 IF (p_txn_status = 'TRUE') THEN
1424 print_debug('Within p_txn_status=TRUE', 4);
1425 IF (l_wms_po_j_higher = FALSE) THEN
1426
1427 print_debug('Within l_wms_po_j_higher = FALSE', 4);
1428 FOR i IN c_lpn_cnts LOOP
1429
1430 IF (l_debug = 1) THEN
1431 print_debug('Txn Type=' || i.transaction_type || ', Destination=' || i.destination_type_code);
1432 print_debug('Txn qty=' || i.rt_quantity);
1433 print_debug('From LPN=' || i.lpn_id);
1434 print_debug('To LPN=' || i.transfer_lpn_id);
1435 END IF;
1436
1437 IF i.transaction_type <> 'CORRECT'
1438 AND i.transaction_type <> 'RETURN TO RECEIVING' THEN
1439 -- dont want to call pup for return to receiving as it is done
1440 -- in inventory tm.
1441 IF (l_debug = 1) THEN
1442 print_debug('transaction_type <> CORRECT, RETURN TO RECEIVING');
1443 END IF;
1444 -- Need to call it only for a rtv from receiving as other
1445 -- cases are taken care of in inventory tm.
1446 -- Even a rtv/rtc from inventory creates 2 txns. - return
1447 -- to receiving and rtv/rtc for same transaction_interface_id
1448 -- and the same group_id. Unpacking for it has already happened
1449 -- in the inventory TM (INVTRXWB.pls) so here should not call
1450 -- it again for rtv/rtc from inventory.
1451 PackUnpack_Container(
1452 x_return_status => x_return_status,
1453 x_msg_count => x_msg_count,
1454 x_msg_data => x_msg_data,
1455 p_lpn_id => i.lpn_id,
1456 p_content_item_id => i.inventory_item_id,
1457 p_revision => i.revision,
1458 p_lot_number => i.lot_number,
1459 p_from_serial_number => i.serial_number,
1460 p_to_serial_number => i.serial_number,
1461 p_quantity => i.quantity,
1462 p_uom => i.uom_code,
1463 p_organization_id => i.organization_id,
1464 p_subinventory => NULL,
1465 p_locator_id => NULL,
1466 p_operation => 2, -- unpack
1467 p_source_header_id => i.interface_transaction_id,
1468 p_source_name => i.transaction_type,
1469 p_cost_group_id => i.cg_id
1470 );
1471 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1472 IF (l_debug = 1) THEN
1473 print_debug('Error while unpacking for rtv/rtc from receiving');
1474 END IF;
1475 RAISE fnd_api.g_exc_error; --return;
1476 END IF;
1477
1478 ELSIF i.transaction_type = 'CORRECT' THEN -- <> CORRECT
1479 IF (l_debug = 1) THEN
1480 print_debug('transaction_type = CORRECT');
1481 END IF;
1482
1483 BEGIN
1484 SELECT transaction_type, routing_header_id
1485 INTO l_parent_transaction_type, l_routing_header_id
1486 FROM rcv_transactions
1487 WHERE transaction_id = i.parent_transaction_id;
1488 IF (l_debug = 1) THEN
1489 print_debug('l_parent_transaction_type=' || l_parent_transaction_type);
1490 END IF;
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 l_parent_transaction_type := NULL;
1494 IF (l_debug = 1) THEN
1495 print_debug('Error l_parent_transaction_type=' || l_parent_transaction_type);
1496 END IF;
1497 END;
1498
1499 IF i.lpn_id IS NOT NULL AND i.transfer_lpn_id IS NOT NULL AND
1500 l_parent_transaction_type in ('ACCEPT','REJECT') THEN
1501
1502 /* Accept or Reject */
1503 IF (l_debug = 1) THEN
1504 print_debug('Correct Txn = Accept or Reject');
1505 END IF;
1506 IF i.rt_quantity < 0 THEN
1507 l_unpack_lpn := i.transfer_lpn_id;
1508 l_pack_lpn := i.lpn_id;
1509 ELSE
1510 l_unpack_lpn := i.lpn_id;
1511 l_pack_lpn := i.transfer_lpn_id;
1512 END IF;
1513
1514 PackUnpack_Container(
1515 x_return_status => x_return_status,
1516 x_msg_count => x_msg_count,
1517 x_msg_data => x_msg_data,
1518 p_lpn_id => l_unpack_lpn,
1519 p_content_item_id => i.inventory_item_id,
1520 p_revision => i.revision,
1521 p_lot_number => i.lot_number,
1522 p_from_serial_number => i.serial_number,
1523 p_to_serial_number => i.serial_number,
1524 p_quantity => Abs(i.quantity),
1525 p_uom => i.uom_code,
1526 p_organization_id => i.organization_id,
1527 p_subinventory => NULL,
1528 p_locator_id => NULL,
1529 p_operation => 2, -- unpack
1530 p_source_header_id => i.interface_transaction_id,
1531 p_source_name => i.transaction_type,
1532 p_cost_group_id => i.cg_id
1533 );
1534
1535 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1536 IF (l_debug = 1) THEN
1537 print_debug('Error while unpacking in txn_complete for correction OF ACCEPT/REJECT');
1538 END IF;
1539 RAISE fnd_api.g_exc_error; --return;
1540 END IF;
1541
1542 PackUnpack_Container(
1543 x_return_status => x_return_status,
1544 x_msg_count => x_msg_count,
1545 x_msg_data => x_msg_data,
1546 p_lpn_id => l_pack_lpn,
1547 p_content_item_id => i.inventory_item_id,
1548 p_revision => i.revision,
1549 p_lot_number => i.lot_number,
1550 p_from_serial_number => i.serial_number,
1551 p_to_serial_number => i.serial_number,
1552 p_quantity => Abs(i.quantity),
1553 p_uom => i.uom_code,
1554 p_organization_id => i.organization_id,
1555 p_subinventory => NULL,
1556 p_locator_id => NULL,
1557 p_operation => 1, -- pack
1558 p_source_header_id => NULL,
1559 p_source_name => NULL,
1560 p_cost_group_id => NULL
1561 );
1562
1563 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1564 IF (l_debug = 1) THEN
1565 print_debug('Error while packing in txn_complete for correction OF ACCEPT/REJECT');
1566 END IF;
1567 RAISE fnd_api.g_exc_error; --return;
1568 END IF;
1569
1570 -- Update the context for the pack lpn to 'resides in
1571 -- receiving' as packunpack api may have changed it to
1572 -- 'Defined but not used'
1573
1574 UPDATE wms_license_plate_numbers
1575 SET lpn_context = wms_container_pub.lpn_context_rcv
1576 WHERE lpn_id = l_pack_lpn;
1577
1578 ELSIF ((i.lpn_id IS NOT NULL OR i.transfer_lpn_id IS NOT NULL) AND
1579 l_parent_transaction_type in ('RECEIVE','RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
1580
1581 --IF i.lpn_id IS NOT NULL AND i.transfer_lpn_id IS NULL THEN
1582 -- Making it more explicit.
1583 IF (l_parent_transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
1584 AND i.rt_quantity > 0)
1585 OR (l_parent_transaction_type = 'RECEIVE' AND i.rt_quantity < 0) THEN
1586
1587 /* +ve Correct on RTV/RTC or -ve correction on Receive Txns */
1588 IF l_parent_transaction_type = 'RECEIVE' THEN
1589 l_lpn_id := i.transfer_lpn_id;
1590 ELSE
1591 l_lpn_id := i.lpn_id;
1592 END IF;
1593
1594 IF (l_debug = 1) THEN
1595 print_debug('Correct Txn = +ve Correct RTV/RTC or -ve on Receive Txns');
1596 END IF;
1597
1598 PackUnpack_Container(
1599 x_return_status => x_return_status,
1600 x_msg_count => x_msg_count,
1601 x_msg_data => x_msg_data,
1602 p_lpn_id => l_lpn_id,
1603 p_content_item_id => i.inventory_item_id,
1604 p_revision => i.revision,
1605 p_lot_number => i.lot_number,
1606 p_from_serial_number => i.serial_number,
1607 p_to_serial_number => i.serial_number,
1608 p_quantity => abs(i.quantity),
1609 p_uom => i.uom_code,
1610 p_organization_id => i.organization_id,
1611 p_subinventory => NULL,
1612 p_locator_id => NULL,
1613 p_operation => 2, -- unpack
1614 p_source_header_id => i.interface_transaction_id,
1615 p_source_name => i.transaction_type,
1616 p_cost_group_id => i.cg_id
1617 );
1618
1619 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1620 IF (l_debug = 1) THEN
1621 print_debug('Error in unpacking for Correct Txn = +ve Correct RTV/RTC or -ve on Receive Txns');
1622 END IF;
1623 RAISE fnd_api.g_exc_error; --return;
1624 END IF;
1625
1626 --ELSIF i.lpn_id IS NULL AND i.transfer_lpn_id IS NOT NULL THEN
1627 ELSIF (l_parent_transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
1628 AND i.rt_quantity < 0)
1629 OR (l_parent_transaction_type = 'RECEIVE' AND i.rt_quantity > 0) THEN
1630
1631 /* -ve Correct on RTV/RTC or +ve on Receive Txns */
1632 -- For a negative correction on RTV/RTC, the correction
1633 -- FORM has lpn_id that gets populated. Also for the
1634 -- negative correction of RTV we pack material into the
1635 -- lpn_id entered on the form.
1636 IF i.rt_quantity < 0 THEN l_lpn_id := i.lpn_id;
1637 ELSE l_lpn_id := i.transfer_lpn_id;
1638 END IF;
1639
1640 IF (l_debug = 1) THEN
1641 print_debug('Correct Txn = -ve Correct on RTV/RTC or +ve on Receive txns FOR lpn:'||l_lpn_id);
1642 END IF;
1643
1644 /* Need to unmark the contents that are already packed into receiving */
1645
1646 PackUnpack_Container(
1647 x_return_status => x_return_status,
1648 x_msg_count => x_msg_count,
1649 x_msg_data => x_msg_data,
1650 p_lpn_id => l_lpn_id,
1651 p_content_item_id => i.inventory_item_id,
1652 p_revision => i.revision,
1653 p_lot_number => i.lot_number,
1654 p_from_serial_number => i.serial_number,
1655 p_to_serial_number => i.serial_number,
1656 p_quantity => abs(i.quantity),
1657 p_uom => i.uom_code,
1658 p_organization_id => i.organization_id,
1659 p_subinventory => NULL,
1660 p_locator_id => NULL,
1661 p_operation => 2, -- unpack
1662 p_source_header_id => i.interface_transaction_id,
1663 p_source_name => i.transaction_type,
1664 p_cost_group_id => i.cg_id
1665 );
1666
1667 PackUnpack_Container(
1668 x_return_status => x_return_status,
1669 x_msg_count => x_msg_count,
1670 x_msg_data => x_msg_data,
1671 p_lpn_id => l_lpn_id,
1672 p_content_item_id => i.inventory_item_id,
1673 p_revision => i.revision,
1674 p_lot_number => i.lot_number,
1675 p_from_serial_number => i.serial_number,
1676 p_to_serial_number => i.serial_number,
1677 p_quantity => abs(i.quantity),
1678 p_uom => i.uom_code,
1679 p_organization_id => i.organization_id,
1680 p_subinventory => NULL,
1681 p_locator_id => NULL,
1682 p_operation => 1, -- pack
1683 p_source_header_id => NULL,
1684 p_source_name => NULL,
1685 p_cost_group_id => NULL
1686 );
1687
1688 -- If the parent transaction is a receive txn,
1689 -- update the lpn_context as while unpacking, the
1690 -- packunpack api might have changed the context
1691 -- to 'Defined But not used'
1692 IF (l_parent_transaction_type = 'RECEIVE') THEN
1693 UPDATE wms_license_plate_numbers
1694 SET lpn_context = wms_container_pub.lpn_context_rcv
1695 WHERE lpn_id = l_lpn_id;
1696 END IF;
1697
1698 END IF;
1699
1700 END IF;
1701 END IF; -- <> CORRECT
1702
1703 IF (i.serial_number IS NOT NULL
1704 AND i.transaction_type = 'CORRECT'
1705 AND l_parent_transaction_type IN ('RECEIVE','RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
1706 -- The status of the serial number should be changes to issued
1707 -- out of stores (4) if the serial number is going out of receiving
1708 -- to the vendor which will be +ve correction of RTV and
1709 -- -ve correction of a receive transaction.
1710 -- For -ve corrections of rtvs and +ve correction of receive
1711 -- transaction the serial number is received into receiving
1712 -- so should have a current_status which is same as it has
1713 -- when received through mobile which is 5.
1714 IF (l_parent_transaction_type = 'RECEIVE' AND i.rt_quantity>0)
1715 OR (l_parent_transaction_type IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
1716 AND i.rt_quantity < 0) THEN
1717
1718 /* FP-J Lot/Serial Support Enhancement
1719 * If WMS and PO J are installed, then the current status for serials in
1720 * receiving should be "Resides in Receiving"
1721 * If WMS or PO patch levels are less than J, then retain the current
1722 * status of "Resides in Intransit"
1723 */
1724 IF (l_wms_po_j_higher) THEN
1725 l_status := 7;
1726 ELSE
1727 l_status := 5;
1728 END IF;
1729 /* FP-J Lot/Serial Support Enhancement */
1730 ELSE
1731 l_status := 4;
1732 END IF;
1733
1734 IF (l_parent_transaction_type = 'RECEIVE' AND i.rt_quantity>0)
1735 THEN
1736 IF (l_routing_header_id = 2) THEN
1737 l_insp_status := 1;
1738 ELSE
1739 l_insp_status := NULL;
1740 END IF;
1741 END IF;
1742
1743 -- updating status of the sn. to 5 for -ve correction of rtv
1744 -- since this means that the sn are to be packed in receiving.
1745 UPDATE mtl_serial_numbers
1746 SET current_status = l_status
1747 , inspection_status = l_insp_status
1748 , last_txn_source_name = NULL
1749 , last_txn_source_id = NULL
1750 , group_mark_id = NULL
1751 , line_mark_id = NULL
1752 , cost_group_id = NULL
1753 WHERE serial_number = i.serial_number
1754 AND inventory_item_id = i.inventory_item_id;
1755 IF (l_debug = 1) THEN
1756 print_debug('Updated sn for correction of rtv and receive to status:'||l_status);
1757 END IF;
1758 ELSIF (i.serial_number IS NOT NULL
1759 AND i.transaction_type = 'CORRECT'
1760 AND l_parent_transaction_type IN ('ACCEPT','REJECT')) THEN
1761 IF i.rt_quantity < 0 THEN
1762 l_status := 1;
1763 ELSE
1764 IF l_parent_transaction_type = 'ACCEPT' THEN
1765 l_status := 2;
1766 ELSE
1767 l_status := 3;
1768 END IF;
1769 END IF;
1770 -- Leave the current status of the serial number same
1771 -- as the serial number continues to stay in receiving
1772 -- need to change just the inspection status appropriately.
1773 UPDATE mtl_serial_numbers
1774 SET inspection_status = l_status
1775 , last_txn_source_name = NULL
1776 , last_txn_source_id = NULL
1777 , group_mark_id = NULL
1778 , line_mark_id = NULL
1779 , cost_group_id = NULL
1780 WHERE serial_number = i.serial_number
1781 AND inventory_item_id = i.inventory_item_id;
1782 IF (l_debug = 1) THEN
1783 print_debug('Updated inspection status of sn for correction of accept/reject');
1784 END IF;
1785 ELSIF (i.serial_number IS NOT NULL
1786 AND i.lpn_id IS NOT NULL
1787 AND i.transfer_lpn_id IS NULL
1788 AND i.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
1789
1790 /* Not for Accept or Reject */
1791 l_status := 4;
1792 UPDATE mtl_serial_numbers
1793 SET current_status = l_status
1794 , last_txn_source_name = NULL
1795 , last_txn_source_id = NULL
1796 , group_mark_id = NULL
1797 , line_mark_id = NULL
1798 , cost_group_id = NULL
1799 WHERE serial_number = i.serial_number
1800 AND inventory_item_id = i.inventory_item_id;
1801
1802 ELSIF (i.serial_number IS NOT NULL
1803 AND i.transaction_type in ('RETURN TO RECEIVING')) THEN
1804 --l_status := 5;
1805 /* FP-J Lot/Serial Support Enhancement
1806 * If WMS and PO J are installed, then the current status for serials in
1807 * receiving should be "Resides in Receiving"
1808 * If WMS or PO patch levels are less than J, then retain the current
1809 * status of "Resides in Intransit"
1810 */
1811 IF (l_wms_po_j_higher) THEN
1812 l_status := 7;
1813 ELSE
1814 l_status := 5;
1815 END IF;
1816
1817 UPDATE mtl_serial_numbers
1818 SET current_status = l_status
1819 , last_txn_source_name = NULL
1820 , last_txn_source_id = NULL
1821 , group_mark_id = NULL
1822 , line_mark_id = NULL
1823 , cost_group_id = NULL
1824 WHERE serial_number = i.serial_number
1825 AND inventory_item_id = i.inventory_item_id;
1826
1827 END IF;
1828
1829 END LOOP;
1830 END IF; --IF (l_wms_po_j_higher = FALSE) THEN
1831 --Check the express return profile value
1832 --FND_PROFILE.GET('WMS_EXPRESS_RETURN', l_express_return);
1833 --IF NVL(l_express_return,'Y') = 'N' THEN
1834 BEGIN
1835 -- Bug# 3631611: Performance Fixes
1836 -- Open the appropriate cursor based on p_txn_mode
1837 IF (p_txn_mode = 'LPN_GROUP') THEN
1838 OPEN c_reservation_lpn_grp_csr;
1839 ELSE
1840 OPEN c_reservation_csr;
1841 END IF;
1842 LOOP
1843 IF (p_txn_mode = 'LPN_GROUP') THEN
1844 FETCH c_reservation_lpn_grp_csr INTO l_res_csr;
1845 EXIT WHEN c_reservation_lpn_grp_csr%NOTFOUND;
1846 ELSE
1847 FETCH c_reservation_csr INTO l_res_csr;
1848 EXIT WHEN c_reservation_csr%NOTFOUND;
1849 END IF;
1850 --FOR l_res_csr IN c_reservation_csr LOOP
1851 l_src_doc_code := l_res_csr.source_document_code;
1852 -- Relieve the reservations created against this record
1853 IF l_src_doc_code = 'PO' THEN
1854 l_dem_src_type_id := 1;
1855 ELSIF l_src_doc_code = 'INTREQ' THEN
1856 l_dem_src_type_id := 7;
1857 ELSIF l_src_doc_code = 'RMA' THEN
1858 l_dem_src_type_id := 12;
1859 ELSE
1860 l_dem_src_type_id := 10;
1861 END IF;
1862
1863 --Form the query criteria for checking a reservation record
1864 l_qry_res_rec.demand_source_type_id := l_dem_src_type_id;
1865 l_qry_res_rec.organization_id := l_res_csr.organization_id;
1866 l_qry_res_rec.inventory_item_id := l_res_csr.item_id;
1867 IF (l_wms_po_j_higher) THEN
1868 l_qry_res_rec.subinventory_code := l_res_csr.from_subinventory;
1869 l_qry_res_rec.locator_id := l_res_csr.from_locator_id;
1870 ELSE
1871 l_qry_res_rec.subinventory_code := l_res_csr.subinventory;
1872 l_qry_res_rec.locator_id := l_res_csr.locator_id;
1873 END IF;
1874 l_qry_res_rec.lpn_id := l_res_csr.lpn_id;
1875
1876 IF (l_debug = 1) THEN
1877 print_debug('TXN_COMPLETE: Querying reservation using following parameters');
1878 print_debug('TXN_COMPLETE: Demand source type id:'||l_qry_res_rec.demand_source_type_id);
1879 print_debug('TXN_COMPLETE: Organization id:'||l_qry_res_rec.organization_id);
1880 print_debug('TXN_COMPLETE: Item id:'||l_qry_res_rec.inventory_item_id);
1881 print_debug('TXN_COMPLETE: Subinventory Code:'||l_qry_res_rec.subinventory_code);
1882 print_debug('TXN_COMPLETE: Locator id:'||l_qry_res_rec.locator_id);
1883 print_debug('TXN_COMPLETE: LPN id:'||l_qry_res_rec.lpn_id);
1884 END IF;
1885
1886 --Query all the reservation records for the above combinations
1887 INV_RESERVATION_PUB.QUERY_RESERVATION(
1888 p_api_version_number => 1.0,
1889 x_return_status => x_return_status,
1890 x_msg_count => x_msg_count,
1891 x_msg_data => x_msg_data,
1892 p_query_input => l_qry_res_rec,
1893 x_mtl_reservation_tbl => l_mtl_reservation_tbl,
1894 x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count,
1895 x_error_code => l_error_code
1896 );
1897
1898 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1899 IF (l_debug = 1) THEN
1900 print_debug('TXN_COMPLETE: Error while calling query_reservations');
1901 END IF;
1902 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count, p_data => x_msg_data);
1903 RAISE fnd_api.g_exc_error; --return;
1904 END IF;
1905
1906 IF (l_debug = 1) THEN
1907 print_debug('TXN_COMPLETE: No. of reservation recs found:'||l_mtl_reservation_tbl_count);
1908 END IF;
1909
1910 --Check all the records for the given demand source, header and line.
1911 FOR l_counter IN 1 .. l_mtl_reservation_tbl_count LOOP
1912 l_res_lpn_id := l_mtl_reservation_tbl(l_counter).lpn_id;
1913 l_unreserve_qty := l_res_csr.quantity;
1914
1915 --If a record whose LPN matches the LPN being returned
1916 IF (l_res_lpn_id = l_res_csr.lpn_id) THEN
1917 --Check the reservation quantity. If it is lesser than the quantity to be returned
1918 --update the reservation. If the entire quantity is to be returned, then
1919 --clear the reservation
1920 l_res_rec_to_delete := l_mtl_reservation_tbl(l_counter);
1921 IF (l_debug = 1) THEN
1922 print_debug('TXN_COMPLETE: Deleting the reservation...');
1923 END IF;
1924 INV_RESERVATION_PUB.DELETE_RESERVATION(
1925 p_api_version_number => 1.0,
1926 p_init_msg_lst => FND_API.G_FALSE,
1927 x_return_status => x_return_status,
1928 x_msg_count => x_msg_count,
1929 x_msg_data => x_msg_data,
1930 p_rsv_rec => l_res_rec_to_delete,
1931 p_serial_number => l_dummy_sn
1932 );
1933 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1934 IF (l_debug = 1) THEN
1935 print_debug('TXN_COMPLETE: Error while deleting reservations');
1936 END IF;
1937 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count, p_data => x_msg_data);
1938 RAISE fnd_api.g_exc_error; --return;
1939 END IF;
1940 IF (l_debug = 1) THEN
1941 print_debug('TXN_COMPLETE: Deleted the reservation record successfully');
1942 END IF;
1943 END IF; --End If the lpn_id of the reservation record matches
1944 END LOOP; --END delete all the reservation records
1945 END LOOP; --End for all the records for the group_id
1946 -- Bug# 3631611: Performance Fixes
1947 -- Close the appropriate reservation cursor that was opened
1948 IF (p_txn_mode = 'LPN_GROUP') THEN
1949 CLOSE c_reservation_lpn_grp_csr;
1950 ELSE
1951 CLOSE c_reservation_csr;
1952 END IF;
1953 EXCEPTION
1954 WHEN NO_DATA_FOUND THEN
1955 NULL;
1956 WHEN OTHERS THEN
1957 -- Bug# 3631611: Performance Fixes
1958 -- In case of exceptions, make sure these cursors are closed if open
1959 IF (c_reservation_lpn_grp_csr%ISOPEN) THEN
1960 CLOSE c_reservation_lpn_grp_csr;
1961 END IF;
1962 IF (c_reservation_csr%ISOPEN) THEN
1963 CLOSE c_reservation_csr;
1964 END IF;
1965 END;
1966 --END IF; --End If the "Express Returns" profile is "N"
1967
1968 -- If a -ve correction or return to receiving was performed on the DELIVER transaction then we
1969 -- need to update the current_status, sub and locator for the serial
1970 -- numbers - Bug 2490630
1971 IF (p_txn_mode <> 'LPN_GROUP') THEN
1972 -- Start of fix for the bug 4489361
1973 IF NOT l_wms_po_j_higher THEN
1974
1975 FOR irec IN c_neg_deliver_ser_1159 LOOP
1976 IF ((irec.transaction_type = 'CORRECT' AND irec.rt_quantity < 0)
1977 OR (irec.transaction_type = 'RETURN TO RECEIVING')) THEN
1978
1979 UPDATE mtl_serial_numbers
1980 SET
1981 current_status = 5
1982 , current_subinventory_code = NULL
1983 , current_locator_id = NULL
1984 , last_txn_source_name = NULL
1985 , last_txn_source_id = NULL
1986 , group_mark_id = NULL
1987 , line_mark_id = NULL
1988 , cost_group_id = NULL
1989 WHERE serial_number = irec.serial_number
1990 AND inventory_item_id = irec.inventory_item_id ;
1991
1992 IF (l_debug = 1) THEN
1993 print_debug('TXN_COMPLETE: Deliver Transaction. 1159.. updated serial... '||irec.serial_number);
1994 END IF;
1995 END IF; -- Txn type Check
1996 END LOOP;
1997 ELSE -- Check for l_wms_po_j_higher --End of fix for the bug 4489361
1998
1999 FOR irec IN c_neg_deliver_ser LOOP
2000 IF ((irec.transaction_type = 'CORRECT' AND irec.rt_quantity < 0)
2001 OR (irec.transaction_type = 'RETURN TO RECEIVING')) THEN
2002 /* FP-J Lot/Serial Support Enhancement
2003 * If WMS and PO J are installed, then the current status for serials in
2004 * receiving should be "Resides in Receiving"
2005 * If WMS or PO patch levels are less than J, then retain the current
2006 * status of "Resides in Intransit"
2007 */
2008 IF (l_wms_po_j_higher) THEN
2009 l_status := 7;
2010 ELSE
2011 l_status := 5;
2012 END IF;
2013 UPDATE mtl_serial_numbers
2014 SET
2015 --current_status = 5
2016 current_status = l_status
2017 , current_subinventory_code = NULL
2018 , current_locator_id = NULL
2019 , last_txn_source_name = NULL
2020 , last_txn_source_id = NULL
2021 , group_mark_id = NULL
2022 , line_mark_id = NULL
2023 , cost_group_id = NULL
2024 WHERE serial_number = irec.serial_number
2025 AND inventory_item_id = irec.inventory_item_id
2026 AND exists (SELECT '1' FROM rcv_serials_supply rss
2027 WHERE rss.serial_num = serial_number
2028 AND rss.supply_type_code = 'RECEIVING');
2029 IF (l_debug = 1) THEN
2030 print_debug('TXN_COMPLETE: Deliver Transaction... updated serial... '||irec.serial_number);
2031 END IF;
2032 END IF;
2033 END LOOP;
2034 END IF ; -- Check for l_wms_po_j_higher
2035 ELSE --IF (p_txn_mode <> 'LPN_GROUP') THEN
2036 FOR irec IN c_neg_deliver_ser_lpng LOOP
2037 IF ((irec.transaction_type = 'CORRECT' AND irec.rt_quantity < 0)
2038 OR (irec.transaction_type = 'RETURN TO RECEIVING')) THEN
2039 /* FP-J Lot/Serial Support Enhancement
2040 * If WMS and PO J are installed, then the current status for serials in
2041 * receiving should be "Resides in Receiving"
2042 * If WMS or PO patch levels are less than J, then retain the current
2043 * status of "Resides in Intransit"
2044 */
2045 IF (l_wms_po_j_higher) THEN
2046 l_status := 7;
2047 ELSE
2048 l_status := 5;
2049 END IF;
2050 UPDATE mtl_serial_numbers
2051 SET
2052 --current_status = 5
2053 current_status = l_status
2054 , current_subinventory_code = NULL
2055 , current_locator_id = NULL
2056 , last_txn_source_name = NULL
2057 , last_txn_source_id = NULL
2058 , group_mark_id = NULL
2059 , line_mark_id = NULL
2060 , cost_group_id = NULL
2061 WHERE serial_number = irec.serial_number
2062 AND inventory_item_id = irec.inventory_item_id
2063 AND exists (SELECT '1' FROM rcv_serials_supply rss
2064 WHERE rss.serial_num = serial_number
2065 AND rss.supply_type_code = 'RECEIVING');
2066 IF (l_debug = 1) THEN
2067 print_debug('TXN_COMPLETE: Deliver Transaction... updated serial... '||irec.serial_number);
2068 END IF;
2069 END IF;
2070 END LOOP;
2071 END IF; --IF (p_txn_mode <> 'LPN_GROUP') THEN
2072
2073
2074 -- End Changes for Bug 2490630
2075 IF (l_wms_po_j_higher = FALSE) THEN
2076 maintain_move_orders(
2077 p_group_id => p_group_id,
2078 x_return_status => x_return_status,
2079 x_msg_data => x_msg_data,
2080 x_msg_count => x_msg_count);
2081
2082 -- Need to clean up the Lot/Serial Temp tables.
2083 -- For Inventory destination txns, this would have already been
2084 -- done by Inventory Mgr., but we are checking that here
2085 -- as there is no harm in doing it again
2086
2087 -- Bug# 3281512 - Performance Fixes
2088 -- Open up a cursor to retrieve all of the interface_transaction_id
2089 -- values to avoid the hash join.
2090 OPEN c_interface_txn_id;
2091 LOOP
2092 FETCH c_interface_txn_id INTO l_interface_txn_id;
2093 EXIT WHEN c_interface_txn_id%NOTFOUND;
2094
2095 BEGIN
2096 DELETE FROM MTL_SERIAL_NUMBERS_TEMP
2097 WHERE TRANSACTION_TEMP_ID = l_interface_txn_id;
2098
2099 DELETE FROM MTL_TRANSACTION_LOTS_TEMP
2100 WHERE TRANSACTION_TEMP_ID = l_interface_txn_id;
2101
2102 EXCEPTION
2103 WHEN OTHERS THEN NULL;
2104 END;
2105 END LOOP;
2106 CLOSE c_interface_txn_id;
2107
2108 END IF; --IF (l_wms_po_j_higher = FALSE) THEN
2109
2110 ELSIF (p_txn_status = 'FALSE') THEN -- p_txn_status = 'FALSE'
2111
2112 IF (l_debug = 1) THEN
2113 print_debug('txn_complete- failure: Transaction failed and hence doing the following...');
2114 print_debug('txn_complete- failure: Unpack Contents/serials that were marked');
2115 print_debug('txn_complete- failure: Update Contents/Serials that were marked, erasing Source_Name');
2116 END IF;
2117
2118 /* For +ve correction on 'RECEIVE' and -ve correction on RTV/RTC, material gets into Receiving.
2119 ** So, contents/new Serials would have been created before calling transaction processor.
2120 ** Now unpacking them which in effect removes them
2121 */
2122 FOR i IN c_newly_packed LOOP
2123 PackUnpack_Container(
2124 x_return_status => x_return_status,
2125 x_msg_count => x_msg_count,
2126 x_msg_data => x_msg_data,
2127 p_lpn_id => i.lpn_id,
2128 p_content_item_id => i.inventory_item_id,
2129 p_revision => i.revision,
2130 p_lot_number => i.lot_number,
2131 p_from_serial_number => i.serial_number,
2132 p_to_serial_number => i.serial_number,
2133 p_quantity => i.quantity,
2134 p_uom => i.uom_code,
2135 p_organization_id => i.organization_id,
2136 p_subinventory => NULL,
2137 p_locator_id => NULL,
2138 p_operation => 2, -- unpack
2139 p_source_header_id => NULL,
2140 p_source_name => NULL,
2141 P_COST_GROUP_ID => NULL);
2142
2143 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2144 IF (l_debug = 1) THEN
2145 print_debug('Error in txn_complete while unpacking for txn failure');
2146 END IF;
2147 RAISE fnd_api.g_exc_error; --return; -- error while unpacking
2148 end if;
2149
2150 END LOOP;
2151
2152 /* In all other cases(other than the cases above), contents/new serials would be
2153 ** marked and hence remove the marks (source_name).
2154 ** Also, bring back the previous_status of the serial as the current status
2155 ** and set the lpn_txn_error_flag of the serial
2156 */
2157
2158 FOR i IN c_failure LOOP
2159
2160 UPDATE wms_lpn_contents
2161 SET source_name = NULL
2162 WHERE source_name = i.transaction_type
2163 AND source_header_id = i.interface_transaction_id;
2164
2165 -- Bug# 3281512 - Performance Fixes
2166 -- Also go against the inventory_item_id in the MSN table
2167 -- otherwise an index is not used and a full table scan will occur.
2168 -- Only update MSN if an item ID exists on the RTI record.
2169 IF (i.item_id IS NOT NULL) THEN
2170 UPDATE mtl_serial_numbers
2171 SET last_txn_source_name = NULL,
2172 current_status = nvl(previous_status,current_status),
2173 lpn_txn_error_flag = 'Y'
2174 WHERE last_txn_source_name = i.transaction_type
2175 AND last_txn_source_id = i.interface_transaction_id
2176 AND inventory_item_id = i.item_id;
2177 END IF;
2178
2179 END LOOP;
2180
2181
2182 IF (l_debug = 1) THEN
2183 print_debug('Exiting txn_complete- failure');
2184 END IF;
2185 END IF;
2186 IF (l_debug = 1) THEN
2187 print_debug('Exiting txn_complete');
2188 END IF;
2189
2190 EXCEPTION
2191 WHEN FND_API.g_exc_error THEN
2192 IF (l_debug = 1) THEN
2193 print_debug('txn_complete : execution error');
2194 END IF;
2195
2196 x_return_status := FND_API.G_RET_STS_ERROR;
2197 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2198 WHEN OTHERS THEN
2199 IF (l_debug = 1) THEN
2200 print_debug('In exception when others: ' || sqlerrm || ':' || sqlcode);
2201 END IF;
2202 x_return_status := FND_API.G_RET_STS_ERROR;
2203 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2204 x_msg_data := x_msg_data||sqlerrm;
2205 END txn_complete;
2206
2207 /* This function is called from LOV Cursor procedure 'GET_RETURN_LPN' of
2208 ** WMSLPNLB.pls to determine if the LPN is fully marked or partially marked.
2209 */
2210
2211 FUNCTION GET_LPN_MARKED_STATUS (p_lpn_id IN NUMBER, p_org_id IN NUMBER) RETURN VARCHAR2 IS
2212 /* Returns NONE or PARTIAL or FULL */
2213
2214 v_dummy VARCHAR2(1);
2215 v_is_marked boolean := FALSE;
2216 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2217 BEGIN
2218
2219 BEGIN
2220
2221 SELECT '1' into v_dummy
2222 FROM mtl_serial_numbers
2223 WHERE lpn_id = p_lpn_id
2224 AND current_organization_id = p_org_id
2225 AND nvl(last_txn_source_name,'@@@') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2226 AND rownum <= 1;
2227
2228 v_is_marked := TRUE;
2229
2230 EXCEPTION
2231 WHEN NO_DATA_FOUND THEN
2232 NULL;
2233 END;
2234
2235 if not v_is_marked then
2236
2237 BEGIN
2238
2239 SELECT '1' INTO v_dummy
2240 FROM wms_lpn_contents
2241 WHERE nvl(serial_summary_entry,2) <> 1
2242 AND parent_lpn_id = p_lpn_id
2243 AND organization_id = p_org_id
2244 AND nvl(source_name,'@@@') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2245 AND rownum <= 1;
2246
2247 v_is_marked := TRUE;
2248
2249 EXCEPTION
2250 WHEN NO_DATA_FOUND THEN
2251 null;
2252 END;
2253
2254 end if;
2255
2256 if not v_is_marked then
2257 RETURN 'NONE';
2258 end if;
2259
2260 BEGIN
2261
2262 SELECT '1' into v_dummy
2263 FROM mtl_serial_numbers
2264 WHERE lpn_id = p_lpn_id
2265 AND current_organization_id = p_org_id
2266 AND nvl(last_txn_source_name,'@@@') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2267 AND rownum <= 1;
2268
2269 IF (l_debug = 1) THEN
2270 print_debug('Lpn:' || p_lpn_id || ' having serial items is marked partially');
2271 END IF;
2272 return 'PARTIAL';
2273
2274 EXCEPTION
2275 WHEN NO_DATA_FOUND THEN
2276 NULL;
2277 END;
2278
2279 BEGIN
2280
2281 SELECT '1' INTO v_dummy
2282 FROM wms_lpn_contents
2283 WHERE nvl(serial_summary_entry,2) <> 1
2284 AND parent_lpn_id = p_lpn_id
2285 AND ORGANIZATION_ID = p_org_id
2286 AND nvl(source_name,'@@@') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2287 AND rownum <= 1;
2288
2289 IF (l_debug = 1) THEN
2290 print_debug('Lpn:' || p_lpn_id || 'having non serial items is marked partially');
2291 END IF;
2292 return 'PARTIAL';
2293
2294 EXCEPTION
2295 WHEN NO_DATA_FOUND THEN
2296 null;
2297 END;
2298 IF (l_debug = 1) THEN
2299 print_debug('Lpn:' || p_lpn_id || ', entire lpn is marked for return');
2300 END IF;
2301 return 'FULL';
2302 EXCEPTION
2303 WHEN OTHERS THEN
2304 return 'PARTIAL';
2305 END;
2306
2307 /*
2308 ** This Procedure is called from the Returns Form to Mark the LPN Contents
2309 ** that are selected for return.
2310 */
2311
2312 PROCEDURE MARK_RETURNS (
2313 x_return_status OUT NOCOPY VARCHAR2,
2314 x_msg_count OUT NOCOPY NUMBER,
2315 x_msg_data OUT NOCOPY VARCHAR2,
2316 p_rcv_trx_interface_id IN NUMBER,
2317 p_ret_transaction_type IN VARCHAR2,
2318 p_lpn_id IN NUMBER,
2319 p_item_id IN NUMBER,
2320 p_item_revision IN VARCHAR2,
2321 p_quantity IN NUMBER,
2322 p_uom IN VARCHAR2,
2323 p_serial_controlled IN NUMBER,
2324 p_lot_controlled IN NUMBER,
2325 p_org_id IN NUMBER,
2326 p_subinventory IN VARCHAR2,
2327 p_locator_id IN NUMBER
2328 ) IS
2329
2330 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2331 l_lot_number VARCHAR2(80);
2332 l_from_serial_number VARCHAR2(50);
2333 l_to_serial_number VARCHAR2(50);
2334 l_quantity number;
2335 l_lpn_context NUMBER := 0;
2336 l_cost_group_id number;
2337 TYPE c_ref_type IS REF CURSOR;
2338 c_ref c_ref_type;
2339 l_position VARCHAR2(4) := '0000';
2340 l_primary_uom VARCHAR2(10);
2341 l_uom VARCHAR2(3);--BUG 4939647: For non-serial controlled item, always pass the txn uom
2342
2343 -- bug 4411792
2344 l_lpn_update WMS_CONTAINER_PUB.LPN;
2345 l_return_status VARCHAR2(1);
2346 l_msg_count NUMBER;
2347 l_msg_data VARCHAR2(2000);
2348
2349 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2350 BEGIN
2351
2352 l_position := '0010';
2353 x_return_status := FND_API.G_RET_STS_SUCCESS;
2354 IF (l_debug = 1) THEN
2355 print_debug('Enter MARK_RETURNS');
2356 print_debug('p_serial_controlled => ' || p_serial_controlled);
2357 print_debug('p_lot_controlled => ' || p_lot_controlled);
2358 END IF;
2359
2360 BEGIN
2361 SELECT lpn_context
2362 INTO l_lpn_context
2363 FROM wms_license_plate_numbers
2364 WHERE organization_id = p_org_id
2365 AND lpn_id = p_lpn_id;
2366 EXCEPTION
2367 WHEN no_data_found THEN
2368 NULL;
2369 END;
2370
2371
2372 --Get primary UOM - for bug fix 3609203
2373 BEGIN
2374 SELECT primary_uom_code
2375 INTO l_primary_uom
2376 FROM mtl_system_items
2377 WHERE inventory_item_id = p_item_id
2378 AND organization_id = p_org_id ;
2379
2380 END; --get primary uom
2381
2382
2383 IF p_lot_controlled = 2 AND p_serial_controlled in (2,5) THEN
2384
2385 /* Lot/Serial Controlled */
2386 l_position := '0020';
2387
2388 open c_ref for SELECT msn.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2389 MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID
2390 FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT
2391 WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2392 AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
2393 AND MSN.INVENTORY_ITEM_ID = p_item_id
2394 AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2395 AND MSN.LOT_NUMBER = MTLT.LOT_NUMBER
2396 AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
2397 AND MSN.SERIAL_NUMBER <= MSNT.TO_SERIAL_NUMBER;
2398 -- AND length(MSN.SERIAL_NUMBER) = length(MSNT.FM_SERIAL_NUMBER);
2399 -- It is not possible to use length function here because table MTL_TRANSACTION_LOTS_TEMP
2400 -- has a field 'length' and it would result in a compilation error if function length
2401 -- is used.
2402
2403 l_uom := l_primary_uom;
2404 ELSIF p_lot_controlled = 2 AND p_serial_controlled not in (2,5) THEN
2405
2406 /* Lot Controlled */
2407 l_position := '0030';
2408
2409 /* For a given LPN, Lot and Item combination there could be more
2410 ** than one record in wms_lpn_contents, but we need
2411 ** only one record per LPN, Lot and Item combination as an output of this cursor
2412 */
2413
2414 open c_ref for SELECT DISTINCT MTLT.LOT_NUMBER, NULL FM_SERIAL_NUMBER,
2415 NULL TO_SERIAL_NUMBER, MTLT.transaction_quantity quantity,wlpnc.cost_group_id
2416 FROM WMS_LPN_CONTENTS WLPNC, MTL_TRANSACTION_LOTS_TEMP MTLT
2417 WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2418 AND WLPNC.LOT_NUMBER = MTLT.LOT_NUMBER
2419 AND WLPNC.PARENT_LPN_ID = p_lpn_id
2420 AND WLPNC.INVENTORY_ITEM_ID = P_ITEM_ID
2421 AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER');
2422
2423 l_uom := p_uom;
2424 ELSIF p_lot_controlled <> 2 AND p_serial_controlled in (2,5) THEN
2425
2426 /* Serial Controlled */
2427 l_position := '0040';
2428 open c_ref for SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2429 MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID
2430 FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP msnt,
2431 wms_lpn_contents wlpnc
2432 WHERE MSNT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2433 AND MSN.INVENTORY_ITEM_ID = p_item_id
2434 AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2435 AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
2436 AND MSN.SERIAL_NUMBER <= MSNT.to_serial_number
2437 AND msn.lpn_id = wlpnc.parent_lpn_id
2438 AND wlpnc.parent_lpn_id = p_lpn_id
2439 AND wlpnc.inventory_item_id = msn.inventory_item_id
2440 AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER')
2441 AND length(MSN.SERIAL_NUMBER) = length(MSNT.FM_SERIAL_NUMBER);
2442
2443 l_uom := l_primary_uom;
2444 ELSE
2445
2446 l_position := '0050';
2447 open c_ref for SELECT NULL , NULL , NULL , rti.quantity, wlpnc.cost_group_id
2448 FROM WMS_LPN_CONTENTS WLPNC, RCV_TRANSACTIONS_INTERFACE RTI
2449 WHERE RTI.INTERFACE_TRANSACTION_ID = p_rcv_trx_interface_id
2450 AND WLPNC.PARENT_LPN_ID = p_lpn_id
2451 AND WLPNC.INVENTORY_ITEM_ID = RTI.ITEM_ID
2452 AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER')
2453 AND rownum <= 1;
2454
2455 /* For a given LPN and Item combination there could be more
2456 ** than one record in wms_lpn_contents, but we need
2457 ** only one record as an output of this cursor
2458 */
2459 l_uom := p_uom;
2460 END IF;
2461 l_position := '0060';
2462
2463 LOOP
2464 l_position := '0070';
2465
2466 FETCH c_ref into l_lot_number, l_from_serial_number, l_to_serial_number, l_quantity, l_cost_group_id;
2467 l_position := '0080';
2468
2469
2470 IF c_ref%NOTFOUND THEN
2471 IF (l_debug = 1) THEN
2472 print_debug('Contents not found');
2473 END IF;
2474 EXIT;
2475 END IF;
2476 l_position := '0090';
2477
2478 PackUnpack_Container(
2479 x_return_status => x_return_status,
2480 x_msg_count => x_msg_count,
2481 x_msg_data => x_msg_data,
2482 p_lpn_id => p_lpn_id,
2483 p_content_item_id => p_item_id,
2484 p_revision => p_item_revision,
2485 p_lot_number => l_lot_number,
2486 p_from_serial_number => l_from_serial_number,
2487 p_to_serial_number => l_to_serial_number,
2488 p_quantity => abs(l_quantity),
2489 p_uom => l_uom,--BUG 4939647: For non-serial controlled item,always pass the txn uom
2490 p_organization_id => p_org_id,
2491 p_subinventory => p_subinventory,
2492 p_locator_id => p_locator_id,
2493 p_operation => 2, -- unpack
2494 p_source_header_id => NULL,
2495 p_source_name => NULL,
2496 P_COST_GROUP_ID => l_cost_group_id
2497 );
2498 l_position := '0100';
2499
2500 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2501 IF (l_debug = 1) THEN
2502 print_debug('Error in mark_returns while unpacking');
2503 END IF;
2504 RAISE fnd_api.g_exc_error; --return; -- error while unpacking
2505 end if;
2506
2507 PackUnpack_Container(
2508 x_return_status => x_return_status,
2509 x_msg_count => x_msg_count,
2510 x_msg_data => x_msg_data,
2511 p_lpn_id => p_lpn_id,
2512 p_content_item_id => p_item_id,
2513 p_revision => p_item_revision ,
2514 p_lot_number => l_lot_number,
2515 p_from_serial_number => l_from_serial_number,
2516 p_to_serial_number => l_to_serial_number,
2517 p_quantity => abs(l_quantity),
2518 p_uom => l_uom,--R12: For non-serial controlled item,always pass the txn uom
2519 p_organization_id => p_org_id,
2520 p_subinventory => p_subinventory,
2521 p_locator_id => p_locator_id,
2522 p_operation => 1, -- pack
2523 p_source_header_id => p_rcv_trx_interface_id,
2524 p_source_name => p_ret_transaction_type,
2525 P_COST_GROUP_ID => l_cost_group_id
2526 );
2527
2528 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2529 IF (l_debug = 1) THEN
2530 print_debug('Error in mark_returns while packing');
2531 END IF;
2532 RAISE fnd_api.g_exc_error; --return; -- error while packing
2533 end if;
2534
2535 END LOOP;
2536
2537 IF (l_lpn_context <> 0) THEN
2538
2539 -- 4411792 Diecrt update to wlpn is replaced by the below call
2540
2541 l_lpn_update.lpn_id := p_lpn_id ;
2542 l_lpn_update.organization_id := p_org_id ;
2543 l_lpn_update.lpn_context := l_lpn_context ;
2544
2545 wms_container_pvt.Modify_LPN
2546 (
2547 p_api_version => 1.0
2548 , p_validation_level => fnd_api.g_valid_level_none
2549 , x_return_status => l_return_status
2550 , x_msg_count => l_msg_count
2551 , x_msg_data => l_msg_data
2552 , p_lpn => l_lpn_update
2553 ) ;
2554
2555 l_lpn_update := NULL;
2556
2557 -- Bug 4411792
2558 --UPDATE wms_license_plate_numbers
2559 --SET lpn_context = l_lpn_context
2560 --WHERE organization_id = p_org_id
2561 --AND lpn_id = p_lpn_id;
2562 END IF;
2563
2564 UPDATE mtl_txn_request_lines
2565 SET wms_process_flag = 2
2566 , txn_source_line_detail_id = p_rcv_trx_interface_id
2567 WHERE lpn_id = p_lpn_id;
2568
2569 l_position := '0200';
2570 IF (l_debug = 1) THEN
2571 print_debug('Exit MARK_RETURNS');
2572 END IF;
2573
2574 EXCEPTION
2575 WHEN FND_API.g_exc_error THEN
2576 IF (l_debug = 1) THEN
2577 print_debug('mark_returns : execution error');
2578 END IF;
2579
2580 x_return_status := FND_API.G_RET_STS_ERROR;
2581 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2582 when others then
2583 IF (l_debug = 1) THEN
2584 print_debug('Error(' || l_position || '):' || sqlerrm);
2585 END IF;
2586 x_return_status := FND_API.G_RET_STS_ERROR;
2587 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2588 x_msg_data := x_msg_data||sqlerrm;
2589 END MARK_RETURNS;
2590
2591
2592 /*
2593 ** This Procedure is called from the Corrections Form to Pack the LPN Contents
2594 ** that are selected for +ve correction into Receiving.
2595 */
2596
2597 PROCEDURE PACK_INTO_RECEIVING (
2598 x_return_status OUT NOCOPY VARCHAR2,
2599 x_msg_count OUT NOCOPY NUMBER,
2600 x_msg_data OUT NOCOPY VARCHAR2,
2601 p_rcv_trx_interface_id IN NUMBER,
2602 p_ret_transaction_type IN VARCHAR2,
2603 p_lpn_id IN NUMBER,
2604 p_item_id IN NUMBER,
2605 p_item_revision IN VARCHAR2,
2606 p_quantity IN NUMBER,
2607 p_uom IN VARCHAR2,
2608 p_serial_controlled IN NUMBER,
2609 p_lot_controlled IN NUMBER,
2610 p_org_id IN NUMBER
2611 ) IS
2612
2613 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2614 l_lot_number VARCHAR2(80);
2615 l_from_serial_number VARCHAR2(50);
2616 l_to_serial_number VARCHAR2(50);
2617 l_quantity number;
2618 l_cost_group_id number;
2619 TYPE c_ref_type IS REF CURSOR;
2620 c_ref c_ref_type;
2621 l_position VARCHAR2(4) := '0000';
2622
2623 l_wms_po_j_higher BOOLEAN;
2624
2625 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2626 BEGIN
2627
2628 l_position := '0010';
2629 x_return_status := FND_API.G_RET_STS_SUCCESS;
2630
2631 /* FP-J Lot/Serial Support Enhancement
2632 * Read the currentand PO patch levels and set the flag (that would be used to
2633 * match the Lot Number and the LPN) accordingly
2634 */
2635 IF ((WMS_UI_TASKS_APIS.g_wms_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j) AND
2636 (WMS_UI_TASKS_APIS.g_po_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j_po)) THEN
2637 l_wms_po_j_higher := TRUE;
2638 IF (l_debug = 1) THEN
2639 print_debug('PACK_INTO_RECEIVING:WMS and PO patch levels are J or higher', 4);
2640 END IF;
2641 ELSE
2642 l_wms_po_j_higher := FALSE;
2643 IF (l_debug = 1) THEN
2644 print_debug('PACK_INTO_RECEIVING:Either WMS or/and PO patch level(s) are lower than J', 4);
2645 END IF;
2646 END IF;
2647
2648 IF (l_wms_po_j_higher = FALSE) THEN
2649
2650 IF (l_debug = 1) THEN
2651 print_debug('Enter PACK_INTO_RECEIVING');
2652 print_debug('p_serial_controlled => ' || p_serial_controlled);
2653 print_debug('p_lot_controlled => ' || p_lot_controlled);
2654 END IF;
2655
2656 IF p_lot_controlled = 2 AND p_serial_controlled in (2,5) THEN
2657 l_position := '0020';
2658 open c_ref for SELECT mtlt.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2659 MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity
2660 FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT
2661 WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2662 AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
2663 AND MSN.INVENTORY_ITEM_ID = p_item_id
2664 AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2665 --AND MSN.LOT_NUMBER = MTLT.LOT_NUMBER
2666 AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
2667 AND MSN.SERIAL_NUMBER <= MSNT.TO_SERIAL_NUMBER;
2668 -- AND length(MSN.SERIAL_NUMBER) = length(MSNT.FM_SERIAL_NUMBER);
2669 -- It is not possible to use length function here because table MTL_TRANSACTION_LOTS_TEMP
2670 -- has a field 'length' and it would result in a compilation error if function length
2671 -- is used.
2672
2673 ELSIF p_lot_controlled = 2 AND p_serial_controlled not in (2,5) THEN
2674 l_position := '0030';
2675 open c_ref for SELECT MTLT.LOT_NUMBER, NULL FM_SERIAL_NUMBER,
2676 NULL TO_SERIAL_NUMBER, MTLT.TRANSACTION_QUANTITY quantity
2677 FROM MTL_TRANSACTION_LOTS_TEMP MTLT
2678 WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id;
2679
2680 ELSIF p_lot_controlled <> 2 AND p_serial_controlled in (2,5) THEN
2681 l_position := '0040';
2682 open c_ref for SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2683 MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity
2684 FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT
2685 WHERE MSNT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2686 AND MSN.INVENTORY_ITEM_ID = p_item_id
2687 AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2688 AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
2689 AND MSN.SERIAL_NUMBER <= MSNT.TO_SERIAL_NUMBER
2690 AND length(MSN.SERIAL_NUMBER) = length(MSNT.FM_SERIAL_NUMBER);
2691
2692 ELSE
2693 l_position := '0050';
2694 open c_ref for SELECT NULL , NULL , NULL , rti.quantity
2695 FROM RCV_TRANSACTIONS_INTERFACE RTI
2696 WHERE RTI.INTERFACE_TRANSACTION_ID = p_rcv_trx_interface_id;
2697
2698 END IF;
2699 l_position := '0060';
2700
2701 LOOP
2702 l_position := '0070';
2703
2704 FETCH c_ref into l_lot_number, l_from_serial_number, l_to_serial_number, l_quantity;
2705 l_position := '0080';
2706
2707 IF c_ref%NOTFOUND THEN EXIT; END IF;
2708 l_position := '0090';
2709
2710 /* Update the previous_status of serial to current_status before doing anything.
2711 ** This is needed so that current_status can be put back to previous_status
2712 ** if the txn fails. Bringing back the status is done in txn_complete
2713 ** if txn fails.
2714 */
2715 IF l_from_serial_number IS NOT NULL THEN
2716 UPDATE mtl_serial_numbers
2717 SET previous_status = current_status
2718 WHERE serial_number = l_from_serial_number
2719 AND inventory_item_id = p_item_id;
2720 END IF;
2721
2722 PackUnpack_Container(
2723 x_return_status => x_return_status,
2724 x_msg_count => x_msg_count,
2725 x_msg_data => x_msg_data,
2726 p_lpn_id => p_lpn_id,
2727 p_content_item_id => p_item_id,
2728 p_revision => p_item_revision ,
2729 p_lot_number => l_lot_number,
2730 p_from_serial_number => l_from_serial_number,
2731 p_to_serial_number => l_to_serial_number,
2732 p_quantity => abs(l_quantity),
2733 p_uom => p_uom,
2734 p_organization_id => p_org_id,
2735 p_subinventory => NULL,
2736 p_locator_id => NULL,
2737 p_operation => 1, -- pack
2738 p_source_header_id => p_rcv_trx_interface_id,
2739 p_source_name => p_ret_transaction_type,
2740 P_COST_GROUP_ID => NULL
2741 );
2742
2743 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2744 IF (l_debug = 1) THEN
2745 print_debug('Error in PACK_INTO_RECEIVING while packing');
2746 END IF;
2747 RAISE fnd_api.g_exc_error; --return; -- error while packing
2748 end if;
2749
2750 END LOOP;
2751
2752 UPDATE wms_license_plate_numbers
2753 SET lpn_context = wms_container_pub.lpn_context_rcv
2754 WHERE lpn_id = p_lpn_id;
2755
2756 UPDATE mtl_txn_request_lines
2757 SET wms_process_flag = 2
2758 , txn_source_line_detail_id = p_rcv_trx_interface_id
2759 WHERE lpn_id = p_lpn_id;
2760
2761 l_position := '0200';
2762 IF (l_debug = 1) THEN
2763 print_debug('Exit PACK_INTO_RECEIVING');
2764 END IF;
2765 END IF;--IF (l_wms_po_j_higher = FALSE) THEN
2766
2767 EXCEPTION
2768 WHEN FND_API.g_exc_error THEN
2769 IF (l_debug = 1) THEN
2770 print_debug('maintain_move_orders : execution error');
2771 END IF;
2772
2773 x_return_status := FND_API.G_RET_STS_ERROR;
2774 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2775 when others then
2776 x_return_status := FND_API.G_RET_STS_ERROR;
2777 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2778 x_msg_data := x_msg_data||sqlerrm;
2779 IF (l_debug = 1) THEN
2780 print_debug('Error(' || l_position || '):' || sqlerrm);
2781 END IF;
2782 END PACK_INTO_RECEIVING;
2783
2784 /*
2785 ** This procedure is called from Mobile Returns when the input LPN
2786 ** is totally marked for Return.
2787 */
2788
2789 PROCEDURE PROCESS_WHOLE_LPN_RETURN (
2790 x_return_status OUT NOCOPY VARCHAR2
2791 , x_msg_count OUT NOCOPY NUMBER
2792 , x_msg_data OUT NOCOPY VARCHAR2
2793 , p_org_id IN NUMBER
2794 , p_lpn_id IN NUMBER
2795 , p_txn_proc_mode IN VARCHAR2
2796 , p_group_id IN NUMBER
2797 ) IS
2798 l_rtiid NUMBER;
2799
2800 TYPE c_ref_type IS REF CURSOR;
2801 c_ref c_ref_type;
2802 c_ref_ser c_ref_type;
2803
2804 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2805 BEGIN
2806
2807 IF (l_debug = 1) THEN
2808 print_debug('Enter PROCESS_WHOLE_LPN_RETURN');
2809 print_debug('Called PROCESS_WHOLE_LPN_RETURN with the parameters');
2810 END IF;
2811
2812 IF (l_debug = 1) THEN
2813 print_debug('p_org_id =>' || p_org_id);
2814 print_debug('p_lpn_id =>' || p_lpn_id);
2815 print_debug('p_txn_proc_mode =>' || p_txn_proc_mode);
2816 print_debug('p_group_id =>' || p_group_id);
2817 END IF;
2818
2819 x_return_status := fnd_api.g_ret_sts_success;
2820
2821 open c_ref for SELECT SOURCE_HEADER_ID
2822 FROM WMS_LPN_CONTENTS WLPNC
2823 WHERE WLPNC.ORGANIZATION_ID = p_org_id
2824 AND WLPNC.PARENT_LPN_ID = p_lpn_id
2825 AND NVL(SERIAL_SUMMARY_ENTRY,2) <> 1 -- Non Serial Contents Records(value=2)
2826 AND WLPNC.SOURCE_NAME IN ('RETURN TO VENDOR',
2827 'RETURN TO CUSTOMER',
2828 'RETURN TO RECEIVING');
2829
2830 LOOP
2831 FETCH c_ref into l_rtiid;
2832 IF c_ref%NOTFOUND THEN
2833 EXIT;
2834 END IF;
2835 IF (l_debug = 1) THEN
2836 print_debug('l_rtiid =>' || l_rtiid);
2837 END IF;
2838
2839 UPDATE RCV_TRANSACTIONS_INTERFACE
2840 SET GROUP_ID = p_group_id,
2841 PROCESSING_MODE_CODE = p_txn_proc_mode,
2842 MOBILE_TXN = 'Y'
2843 WHERE INTERFACE_TRANSACTION_ID = l_rtiid;
2844 END LOOP;
2845 IF (l_debug = 1) THEN
2846 print_debug('END OF LOOP PROCESS WHOLE LPN RETURN - Non Serial Contents Records');
2847 END IF;
2848
2849 open c_ref_ser for SELECT LAST_TXN_SOURCE_ID
2850 FROM MTL_SERIAL_NUMBERS MSN
2851 WHERE MSN.LPN_ID = p_lpn_id
2852 AND MSN.LAST_TXN_SOURCE_NAME IN ('RETURN TO VENDOR',
2853 'RETURN TO CUSTOMER',
2854 'RETURN TO RECEIVING');
2855
2856 LOOP
2857 FETCH c_ref_ser into l_rtiid;
2858 IF c_ref_ser%NOTFOUND THEN
2859 EXIT;
2860 END IF;
2861 IF (l_debug = 1) THEN
2862 print_debug('l_rtiid =>' || l_rtiid);
2863 END IF;
2864
2865 UPDATE RCV_TRANSACTIONS_INTERFACE
2866 SET GROUP_ID = p_group_id,
2867 PROCESSING_MODE_CODE = p_txn_proc_mode,
2868 MOBILE_TXN = 'Y'
2869 WHERE INTERFACE_TRANSACTION_ID = l_rtiid;
2870 END LOOP;
2871 IF (l_debug = 1) THEN
2872 print_debug('END OF LOOP PROCESS WHOLE LPN RETURN - Serial Records');
2873 END IF;
2874
2875 EXCEPTION
2876 WHEN fnd_api.g_exc_error THEN
2877
2878 x_return_status := fnd_api.g_ret_sts_error;
2879 IF (l_debug = 1) THEN
2880 print_debug('x_return_status =>' || x_return_status);
2881 END IF;
2882
2883 -- Get message count and data
2884 fnd_msg_pub.count_and_get
2885 ( p_count => x_msg_count
2886 , p_data => x_msg_data
2887 );
2888
2889 IF (c_ref%isopen) THEN
2890 CLOSE c_ref;
2891 END IF;
2892 IF (c_ref_ser%isopen) THEN
2893 CLOSE c_ref_ser;
2894 END IF;
2895
2896
2897 WHEN fnd_api.g_exc_unexpected_error THEN
2898
2899 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2900 IF (l_debug = 1) THEN
2901 print_debug('x_return_status =>' || x_return_status);
2902 END IF;
2903
2904 -- Get message count and data
2905 fnd_msg_pub.count_and_get
2906 ( p_count => x_msg_count
2907 , p_data => x_msg_data
2908 );
2909
2910 IF (c_ref%isopen) THEN
2911 CLOSE c_ref;
2912 END IF;
2913 IF (c_ref_ser%isopen) THEN
2914 CLOSE c_ref_ser;
2915 END IF;
2916
2917
2918 WHEN others THEN
2919
2920 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2921 IF (l_debug = 1) THEN
2922 print_debug('x_return_status =>' || x_return_status);
2923 END IF;
2924 --
2925 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2926 THEN
2927 fnd_msg_pub.add_exc_msg
2928 ( g_pkg_name
2929 , 'main_process'
2930 );
2931 END IF;
2932
2933 -- Get message count and data
2934 fnd_msg_pub.count_and_get
2935 ( p_count => x_msg_count
2936 , p_data => x_msg_data
2937 );
2938
2939 IF (c_ref%isopen) THEN
2940 CLOSE c_ref;
2941 END IF;
2942 IF (c_ref_ser%isopen) THEN
2943 CLOSE c_ref_ser;
2944 END IF;
2945
2946 END PROCESS_WHOLE_LPN_RETURN;
2947
2948
2949 /*
2950 ** This procedure is called from Mobile Returns when the input LPN
2951 ** is partially marked for Return.
2952 */
2953
2954 PROCEDURE PROCESS_RETURNS (
2955 x_return_status OUT NOCOPY VARCHAR2
2956 , x_msg_count OUT NOCOPY NUMBER
2957 , x_msg_data OUT NOCOPY VARCHAR2
2958 , p_org_id IN NUMBER
2959 , p_lpn_id IN NUMBER
2960 , p_item_id IN NUMBER
2961 , p_item_revision IN VARCHAR2
2962 , p_uom IN VARCHAR2
2963 , p_lot_code IN VARCHAR2
2964 , p_serial_code IN VARCHAR2
2965 , p_quantity IN NUMBER
2966 , p_serial_controlled IN NUMBER
2967 , p_lot_controlled IN NUMBER
2968 , p_txn_proc_mode IN VARCHAR2
2969 , p_group_id IN NUMBER
2970 , p_to_lpn_id IN NUMBER
2971 ) IS
2972 l_rtiid NUMBER;
2973
2974 TYPE c_ref_type IS REF CURSOR;
2975 c_ref c_ref_type;
2976
2977 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2978 BEGIN
2979
2980 x_return_status := fnd_api.g_ret_sts_success;
2981
2982 IF (l_debug = 1) THEN
2983 print_debug('Enter PROCESS_RETURNS');
2984 print_debug('Called PROCESS_RETURNS with the parameters');
2985 END IF;
2986
2987 IF (l_debug = 1) THEN
2988 print_debug('p_org_id =>' || p_org_id);
2989 print_debug('p_lpn_id =>' || p_lpn_id);
2990 print_debug('p_item_id =>' || p_item_id);
2991 print_debug('p_item_revision =>' || p_item_revision);
2992 print_debug('p_uom =>' || p_uom);
2993 print_debug('p_lot_code =>' || p_lot_code);
2994 print_debug('p_serial_code =>' || p_serial_code);
2995 print_debug('p_quantity =>' || p_quantity);
2996 print_debug('p_serial_controlled =>' || p_serial_controlled);
2997 print_debug('p_lot_controlled =>' || p_lot_controlled);
2998 print_debug('p_txn_proc_mode =>' || p_txn_proc_mode);
2999 print_debug('p_group_id =>' || p_group_id);
3000 print_debug('p_to_lpn_id =>' || p_to_lpn_id);
3001 END IF;
3002
3003 IF p_lot_controlled = 2 AND p_serial_controlled = 2 THEN -- NonSerial and NonLot
3004 open c_ref for SELECT SOURCE_HEADER_ID
3005 FROM WMS_LPN_CONTENTS WLPNC
3006 WHERE WLPNC.ORGANIZATION_ID = p_org_id
3007 AND WLPNC.PARENT_LPN_ID = p_lpn_id
3008 AND WLPNC.INVENTORY_ITEM_ID = p_item_id
3009 AND ((WLPNC.revision = p_item_revision AND p_item_revision IS NOT NULL) OR
3010 (WLPNC.REVISION IS NULL AND p_item_revision IS NULL))
3011 AND WLPNC.SOURCE_NAME IN ('RETURN TO VENDOR',
3012 'RETURN TO CUSTOMER',
3013 'RETURN TO RECEIVING');
3014
3015 ELSIF p_serial_controlled = 1 THEN -- Serial, Lot control doesn't matter
3016 open c_ref for SELECT LAST_TXN_SOURCE_ID
3017 FROM MTL_SERIAL_NUMBERS MSN
3018 WHERE MSN.LPN_ID = p_lpn_id
3019 AND MSN.INVENTORY_ITEM_ID = p_item_id
3020 AND ((MSN.REVISION = p_item_revision AND p_item_revision IS NOT NULL) OR
3021 (MSN.REVISION IS NULL AND p_item_revision IS NULL))
3022 AND MSN.LAST_TXN_SOURCE_NAME IN ('RETURN TO VENDOR',
3023 'RETURN TO CUSTOMER',
3024 'RETURN TO RECEIVING')
3025 AND MSN.SERIAL_NUMBER = p_serial_code;
3026
3027 ELSIF p_lot_controlled = 1 AND p_serial_controlled = 2 THEN -- Lot Only
3028 open c_ref for SELECT SOURCE_HEADER_ID
3029 FROM WMS_LPN_CONTENTS WLPNC
3030 WHERE WLPNC.ORGANIZATION_ID = p_org_id
3031 AND WLPNC.PARENT_LPN_ID = p_lpn_id
3032 AND WLPNC.INVENTORY_ITEM_ID = p_item_id
3033 AND ((WLPNC.REVISION = p_item_revision AND p_item_revision IS NOT NULL) OR
3034 (WLPNC.REVISION IS NULL AND p_item_revision IS NULL))
3035 AND WLPNC.SOURCE_NAME IN ('RETURN TO VENDOR',
3036 'RETURN TO CUSTOMER',
3037 'RETURN TO RECEIVING')
3038 AND WLPNC.LOT_NUMBER = p_lot_code;
3039
3040 END IF;
3041
3042 LOOP
3043 FETCH c_ref into l_rtiid;
3044 IF c_ref%NOTFOUND THEN
3045 EXIT;
3046 END IF;
3047 IF (l_debug = 1) THEN
3048 print_debug('l_rtiid =>' || l_rtiid);
3049 END IF;
3050
3051 UPDATE RCV_TRANSACTIONS_INTERFACE
3052 SET GROUP_ID = p_group_id,
3053 PROCESSING_MODE_CODE = p_txn_proc_mode,
3054 MOBILE_TXN = 'Y'
3055 WHERE INTERFACE_TRANSACTION_ID = l_rtiid;
3056
3057 IF (l_debug = 1) THEN
3058 print_debug('yes, p_to_lpn_id not zero ' || to_char(p_to_lpn_id));
3059 END IF;
3060 IF p_to_lpn_id <> 0 THEN
3061 IF (l_debug = 1) THEN
3062 print_debug('yes, p_to_lpn_id not zero ' || to_char(p_to_lpn_id));
3063 END IF;
3064 UPDATE RCV_TRANSACTIONS_INTERFACE
3065 SET TRANSFER_LPN_ID = p_to_lpn_id
3066 WHERE INTERFACE_TRANSACTION_ID = l_rtiid
3067 AND NVL(TRANSACTION_TYPE, '@@@') = 'RETURN TO RECEIVING';
3068 END IF;
3069
3070 END LOOP;
3071 COMMIT;
3072 IF (l_debug = 1) THEN
3073 print_debug('END OF LOOP PROCESS RETURNS');
3074 END IF;
3075
3076 EXCEPTION
3077 WHEN fnd_api.g_exc_error THEN
3078
3079 x_return_status := fnd_api.g_ret_sts_error;
3080 IF (l_debug = 1) THEN
3081 print_debug('x_return_status =>' || x_return_status);
3082 END IF;
3083
3084 -- Get message count and data
3085 fnd_msg_pub.count_and_get
3086 ( p_count => x_msg_count
3087 , p_data => x_msg_data
3088 );
3089
3090 IF (c_ref%isopen) THEN
3091 CLOSE c_ref;
3092 END IF;
3093
3094
3095 WHEN fnd_api.g_exc_unexpected_error THEN
3096
3097 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3098 IF (l_debug = 1) THEN
3099 print_debug('x_return_status =>' || x_return_status);
3100 END IF;
3101
3102 -- Get message count and data
3103 fnd_msg_pub.count_and_get
3104 ( p_count => x_msg_count
3105 , p_data => x_msg_data
3106 );
3107
3108 IF (c_ref%isopen) THEN
3109 CLOSE c_ref;
3110 END IF;
3111
3112
3113 WHEN others THEN
3114
3115 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3116 IF (l_debug = 1) THEN
3117 print_debug('x_return_status =>' || x_return_status);
3118 END IF;
3119 --
3120 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3121 THEN
3122 fnd_msg_pub.add_exc_msg
3123 ( g_pkg_name
3124 , 'main_process'
3125 );
3126 END IF;
3127
3128 -- Get message count and data
3129 fnd_msg_pub.count_and_get
3130 ( p_count => x_msg_count
3131 , p_data => x_msg_data
3132 );
3133
3134 IF (c_ref%isopen) THEN
3135 CLOSE c_ref;
3136 END IF;
3137
3138
3139 END PROCESS_RETURNS;
3140
3141 /*
3142 ** This Function is called from procedure 'GET_TRX_VALUES' to get Receiving
3143 ** Processing Mode.
3144 */
3145
3146 FUNCTION GET_TRX_PROC_MODE RETURN VARCHAR2 IS
3147 /*
3148 ** Function will return Receiving Transaction Processor Mode (RCV_TP_MODE)
3149 ** If Transaction Processor Mode is NULL then
3150 ** Default the Mode to 'ONLINE'
3151 ** Function will be referencing a 'FND_PROFILE.GET' procedure defined
3152 ** by AOL grp . It will return the value of the PROFILE being asked for,
3153 ** or will return 'ONLINE' if profile 'RCV_TP_MODE' is NULL.
3154 */
3155 transaction_processor_value VARCHAR2(10);
3156
3157 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3158 BEGIN
3159 fnd_profile.get('RCV_TP_MODE',transaction_processor_value);
3160
3161 if transaction_processor_value is null then
3162 transaction_processor_value := 'ONLINE';
3163 end if;
3164
3165 return(transaction_processor_value);
3166
3167 EXCEPTION
3168 WHEN OTHERS THEN
3169 IF (l_debug = 1) THEN
3170 print_debug('Failure getting transaction processing mode');
3171 END IF;
3172 RAISE;
3173
3174 END GET_TRX_PROC_MODE;
3175
3176 /*
3177 ** This procedure is called from Mobile Returns to determine the
3178 ** Receiving Processing Mode and Group ID from sequence that are used
3179 ** to stamp on RTI. This single wrapper procedure is created so that Mobile
3180 ** Returns visits Database only once to get both Receiving Processing Mode
3181 ** and Group ID.
3182 */
3183
3184 PROCEDURE GET_TRX_VALUES(
3185 transaction_processor_value OUT NOCOPY VARCHAR2
3186 , group_id OUT NOCOPY NUMBER) IS
3187 l_groupid NUMBER;
3188 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3189 BEGIN
3190 transaction_processor_value := GET_TRX_PROC_MODE;
3191 SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL INTO l_groupid FROM DUAL;
3192
3193 group_id := l_groupid;
3194 EXCEPTION
3195 WHEN OTHERS THEN
3196 IF (l_debug = 1) THEN
3197 print_debug('Failure getting TXN PROC MODE and GROUPID');
3198 END IF;
3199 RAISE;
3200
3201 END GET_TRX_VALUES;
3202
3203 /*
3204 ** This procedure is called from Mobile Returns to launch the Receiving
3205 ** Processor after setting the input group ID and receiving processing mode.
3206 */
3207
3208 PROCEDURE RCV_PROCESS_WRAPPER(
3209 x_return_status OUT NOCOPY VARCHAR2
3210 , x_msg_data OUT NOCOPY VARCHAR2
3211 , p_trx_proc_mode IN VARCHAR2
3212 , p_group_id IN NUMBER) IS
3213
3214 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3215 BEGIN
3216 inv_rcv_common_apis.g_po_startup_value.transaction_mode := p_trx_proc_mode;
3217 inv_rcv_common_apis.g_rcv_global_var.interface_group_id := p_group_id;
3218 INV_RCV_MOBILE_PROCESS_TXN.rcv_process_receive_txn(x_return_status, x_msg_data);
3219 END RCV_PROCESS_WRAPPER;
3220
3221 /*
3222 ** This procedure is called from Mobile Returns to get the suggested 'To LPN'
3223 ** if any, for the input From LPN and Item.
3224 */
3225
3226 PROCEDURE GET_SUGGESTED_TO_LPN(
3227 x_lpn_lov OUT NOCOPY t_genref
3228 , p_org_id IN NUMBER
3229 , p_lpn_id IN NUMBER
3230 , p_item_id IN NUMBER
3231 , p_revision IN VARCHAR2) IS
3232 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3233 BEGIN
3234 OPEN x_lpn_lov FOR
3235 select distinct wlpnc.license_plate_number
3236 from wms_license_plate_numbers wlpnc, rcv_transactions_interface rti
3237 where rti.lpn_id = p_lpn_id
3238 and rti.item_id = p_item_id
3239 and nvl(rti.item_revision, '@@@') = nvl(p_revision, '@@@')
3240 and nvl(rti.transaction_type, '@@@') = 'RETURN TO RECEIVING'
3241 and rti.transfer_lpn_id is not null
3242 and wlpnc.lpn_id = rti.transfer_lpn_id
3243 and wlpnc.organization_id = p_org_id;
3244 END GET_SUGGESTED_TO_LPN;
3245
3246 /* This procedure is used to create a reservation during a Return. Called
3247 ** from RCVTXERE.pld after creating an rcv_transaction_interface_record
3248 */
3249 PROCEDURE CREATE_RETURN_RESV(
3250 x_return_status OUT NOCOPY VARCHAR2,
3251 x_msg_count OUT NOCOPY VARCHAR2,
3252 x_msg_data OUT NOCOPY VARCHAR2,
3253 p_org_id IN NUMBER,
3254 p_item_id IN NUMBER,
3255 p_revision IN VARCHAR2,
3256 p_subinventory_code IN VARCHAR2,
3257 p_locator_id IN NUMBER,
3258 p_lpn_id IN NUMBER,
3259 p_reservation_qty IN NUMBER,
3260 p_unit_of_measure IN VARCHAR2,
3261 p_requirement_date IN DATE,
3262 p_dem_src_type_id IN NUMBER,
3263 p_dem_src_hdr_id IN NUMBER,
3264 p_dem_src_line_id IN NUMBER,
3265 p_intf_txn_id IN NUMBER
3266 ) IS
3267
3268 /* Reservation Data Structures */
3269 l_mtl_reservation_tbl_count NUMBER;
3270 l_reservation_record INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
3271 l_qry_reservation_record INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
3272 l_upd_reservation_record INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
3273 l_upd_reservation_tbl INV_RESERVATION_GLOBAL.MTL_RESERVATION_TBL_TYPE;
3274 l_upd_reservation_tbl_cnt NUMBER := 0;
3275 l_dummy_sn INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
3276 l_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE; --Lot Number
3277 l_uom_code VARCHAR2(3); --UOM Code
3278 l_quantity_reserved NUMBER; --Quantity that was reserved
3279 l_reservation_id NUMBER; --Reservation Id
3280 l_error_code NUMBER;
3281 l_item_primary_uom VARCHAR2(3);
3282 l_primary_res_qty NUMBER;
3283 l_res_lpn_id NUMBER;
3284 l_create_res BOOLEAN := TRUE;
3285 l_lot_control_code NUMBER := 1;
3286
3287 CURSOR c_lots IS
3288 SELECT lot_number, primary_quantity
3289 FROM mtl_transaction_lots_temp
3290 WHERE product_code = 'RCV'
3291 AND product_transaction_id = p_intf_txn_id;
3292
3293 CURSOR c_lots_old IS
3294 SELECT lot_number, primary_quantity
3295 FROM mtl_transaction_lots_temp
3296 WHERE transaction_temp_id = p_intf_txn_id;
3297
3298 l_wms_po_j_higher BOOLEAN;
3299
3300 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3301 BEGIN
3302 x_return_status := fnd_api.g_ret_sts_success;
3303
3304 IF (l_debug = 1) THEN
3305 print_debug('CREATE_RETURN_RESV:Interface Transaction ID:'||p_intf_txn_id,1);
3306 END IF;
3307
3308 --Get the UOM code based on the Unit of Measure passed
3309 --For expense items, UOM would be null
3310 SELECT uom_code
3311 INTO l_uom_code
3312 FROM mtl_item_uoms_view
3313 WHERE inventory_item_id = p_item_id
3314 AND organization_id = p_org_id
3315 AND unit_of_measure = p_unit_of_measure;
3316
3317 SELECT primary_uom_code, lot_control_code
3318 INTO l_item_primary_uom, l_lot_control_code
3319 FROM mtl_system_items
3320 WHERE inventory_item_id = p_item_id
3321 AND organization_id = p_org_id;
3322
3323 -- BEGIN
3324 -- SELECT wlc.lot_number
3325 -- INTO l_lot_number
3326 --FROM wms_lpn_contents wlc,
3327 -- mtl_system_items msi
3328 --WHERE wlc.parent_lpn_id = p_lpn_id
3329 --AND wlc.organization_id = p_org_id
3330 --AND wlc.inventory_item_id = p_item_id
3331 --AND msi.inventory_item_id = p_item_id
3332 --AND msi.organization_id = p_org_id
3333 --AND msi.lot_control_code = 2
3334 --AND ROWNUM < 2;
3335
3336 -- EXCEPTION
3337 -- WHEN NO_DATA_FOUND THEN
3338 -- NULL;
3339 -- END;
3340
3341 IF ((WMS_UI_TASKS_APIS.g_wms_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j) AND
3342 (WMS_UI_TASKS_APIS.g_po_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j_po)) THEN
3343 l_wms_po_j_higher := TRUE;
3344 IF (l_debug = 1) THEN
3345 print_debug('CREATE_RETURN_RESV:WMS and PO patch levels are J or higher', 4);
3346 END IF;
3347 ELSE
3348 l_wms_po_j_higher := FALSE;
3349 IF (l_debug = 1) THEN
3350 print_debug('CREATE_RETURN_RESV:Either WMS or/and PO patch level(s) are lower than J', 4);
3351 END IF;
3352 END IF;
3353
3354 IF (l_wms_po_j_higher) THEN
3355 OPEN c_lots;
3356 ELSE
3357 OPEN c_lots_old;
3358 END IF;
3359
3360 LOOP
3361 --if the item is lot controlled then fetch from the cursor otherwise
3362 --just use the existing data and exit at the end.
3363 IF (l_lot_control_code = 2) THEN
3364 IF (l_wms_po_j_higher) THEN
3365 FETCH c_lots INTO l_lot_number, l_primary_res_qty;
3366
3367 EXIT WHEN c_lots%NOTFOUND;
3368 ELSE
3369 FETCH c_lots_old INTO l_lot_number, l_primary_res_qty;
3370
3371 EXIT WHEN c_lots_old%NOTFOUND;
3372 END IF;
3373 END IF;
3374
3375 --Check if there exists a reservation record for the current combination
3376 IF (l_debug = 1) THEN
3377 print_debug('CREATE_RETURN_RESV:Lot Number:'||l_lot_number);
3378 print_debug('CREATE_RETURN_RESV:Prim Qty:'||l_primary_res_qty);
3379 print_debug('CREATE_RETURN_RESV:Check if the reservation already exists');
3380 END IF;
3381 l_qry_reservation_record.organization_id := p_org_id;
3382 l_qry_reservation_record.inventory_item_id := p_item_id;
3383 l_qry_reservation_record.demand_source_header_id := p_dem_src_hdr_id;
3384 l_qry_reservation_record.demand_source_line_id := p_dem_src_line_id;
3385 l_qry_reservation_record.demand_source_type_id := p_dem_src_type_id;
3386 l_qry_reservation_record.lpn_id := p_lpn_id;
3387 l_qry_reservation_record.lot_number := l_lot_number;
3388 l_reservation_record.lpn_id := p_lpn_id;
3389
3390 --Query all the reservation records for the above combinations
3391 INV_RESERVATION_PUB.QUERY_RESERVATION(
3392 p_api_version_number => 1.0,
3393 x_return_status => x_return_status,
3394 x_msg_count => x_msg_count,
3395 x_msg_data => x_msg_data,
3396 p_query_input => l_qry_reservation_record,
3397 x_mtl_reservation_tbl => l_upd_reservation_tbl,
3398 x_mtl_reservation_tbl_count => l_upd_reservation_tbl_cnt,
3399 x_error_code => l_error_code
3400 );
3401
3402 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3403 IF (l_debug = 1) THEN
3404 print_debug('CREATE_RETURN_RESV:Error while calling query_reservations');
3405 END IF;
3406 RAISE FND_API.G_EXC_ERROR;
3407 END IF;
3408
3409 IF (l_debug = 1) THEN
3410 print_debug('CREATE_RETURN_RESV:There are ' || l_upd_reservation_tbl_cnt || ' reservation records');
3411 END IF;
3412 --If there exists a reservation for this combination then update the reservation quantity
3413 IF l_upd_reservation_tbl_cnt > 0 THEN
3414 FOR l_count IN 1 .. l_upd_reservation_tbl_cnt LOOP
3415 l_upd_reservation_record := l_upd_reservation_tbl(l_count);
3416 l_res_lpn_id := l_upd_reservation_record.lpn_id;
3417 IF (l_res_lpn_id <> p_lpn_id) THEN
3418 l_create_res := TRUE;
3419 ELSE
3420 l_create_res := FALSE;
3421
3422 --Get the quantity that was already reserved
3423 l_quantity_reserved := l_upd_reservation_record.reservation_quantity;
3424 IF (l_debug = 1) THEN
3425 print_debug('CREATE_RETURN_RESV:Quantity that was reserved so far: ' || l_quantity_reserved);
3426 END IF;
3427 IF (l_lot_control_code = 1) THEN
3428 IF l_uom_code <> l_item_primary_uom THEN
3429 l_primary_res_qty := INV_CONVERT.INV_UM_CONVERT(
3430 item_id => p_item_id,
3431 precision => null,
3432 from_quantity => p_reservation_qty,
3433 from_unit => l_uom_code,
3434 to_unit => l_item_primary_uom,
3435 from_name => null,
3436 to_name => null);
3437 IF (l_primary_res_qty = -99999) THEN
3438 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
3439 fnd_message.set_token('UOM', l_item_primary_uom);
3440 fnd_message.set_token('ROUTINE', 'Create Reservation');
3441 fnd_msg_pub.ADD;
3442 RAISE fnd_api.g_exc_error;
3443 END IF;
3444 ELSE --IF l_uom_code <> l_item_primary_uom THEN
3445 l_primary_res_qty := p_reservation_qty;
3446 END IF; --IF l_uom_code <> l_item_primary_uom THEN
3447 END IF; --IF (l_lot_control_code = 1) THEN
3448
3449 IF (l_debug = 1) THEN
3450 print_debug('CREATE_RETURN_RESV:Quantity entered: ' || l_primary_res_qty);
3451 END IF;
3452 --Add the new quantity to the quantity that was already reserved
3453 l_upd_reservation_record.reservation_quantity :=
3454 l_upd_reservation_record.reservation_quantity + l_primary_res_qty;
3455
3456 l_upd_reservation_record.primary_reservation_quantity :=
3457 l_upd_reservation_record.primary_reservation_quantity + l_primary_res_qty;
3458
3459 --Update the reservation record with the new quantity
3460 INV_RESERVATION_PUB.UPDATE_RESERVATION(
3461 p_api_version_number => 1.0,
3462 p_init_msg_lst => FND_API.G_FALSE,
3463 x_return_status => x_return_status,
3464 x_msg_count => x_msg_count,
3465 x_msg_data => x_msg_data,
3466 p_original_rsv_rec => l_upd_reservation_tbl(l_count),
3467 p_to_rsv_rec => l_upd_reservation_record,
3468 p_original_serial_number => l_dummy_sn,
3469 p_to_serial_number => l_dummy_sn,
3470 p_validation_flag => FND_API.G_TRUE);
3471
3472 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3473 IF (l_debug = 1) THEN
3474 print_debug('CREATE_RETURN_RESV:error in update_reservation');
3475 END IF;
3476 FND_MESSAGE.SET_NAME('INB', 'INV_UPD_RSV_FAILED');
3477 FND_MSG_PUB.ADD;
3478 RAISE fnd_api.g_exc_error;
3479 END IF;
3480 IF (l_debug = 1) THEN
3481 print_debug('CREATE_RETURN_RESV:successfully updated a reservation record: ' ||sql%rowcount);
3482 END IF;
3483 EXIT;
3484 END IF; -- End if the lpn_id is the same as the one being returned
3485 END LOOP;
3486 ELSE
3487 l_create_res := TRUE;
3488 END IF; -- End if there exists a record in mtl_reservations
3489
3490 --There exist no reservations for this combinations. Create one
3491 IF l_create_res = TRUE THEN
3492 IF (l_debug = 1) THEN
3493 print_debug('CREATE_RETURN_RESV:No reservation exists for the LPN. Have to create one...');
3494 END IF;
3495 l_reservation_record.organization_id := p_org_id;
3496 l_reservation_record.inventory_item_id := p_item_id;
3497 l_reservation_record.revision := p_revision;
3498
3499 IF (l_lot_control_code = 1) THEN
3500 --Convert the quantity into the primary UOM code of the item
3501 IF l_uom_code <> l_item_primary_uom THEN
3502 l_primary_res_qty := INV_CONVERT.INV_UM_CONVERT(
3503 item_id => p_item_id,
3504 precision => null,
3505 from_quantity => p_reservation_qty,
3506 from_unit => l_uom_code,
3507 to_unit => l_item_primary_uom,
3508 from_name => null,
3509 to_name => null);
3510 IF (l_primary_res_qty = -99999) THEN
3511 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
3512 fnd_message.set_token('UOM', l_item_primary_uom);
3513 fnd_message.set_token('ROUTINE', 'Create Reservation');
3514 fnd_msg_pub.add;
3515 RAISE fnd_api.g_exc_error;
3516 END IF;
3517 ELSE --IF l_uom_code <> l_item_primary_uom THEN
3518 l_primary_res_qty := p_reservation_qty;
3519 END IF; --IF l_uom_code <> l_item_primary_uom THEN
3520 END IF; --IF (l_lot_control_code = 1) THEN
3521
3522 --The reservation UOM code and the primary reservation UOM are set to
3523 --the primary UOM code of the item since it is a dummy reservation
3524 l_reservation_record.reservation_uom_id := NULL;
3525 l_reservation_record.reservation_uom_code := l_item_primary_uom;
3526 l_reservation_record.primary_uom_id := NULL;
3527 l_reservation_record.primary_uom_code := l_item_primary_uom;
3528
3529 --Reservation quantity is set to the quantity after conversion to
3530 --the primary UOM code of the item
3531 l_reservation_record.primary_reservation_quantity := l_primary_res_qty;
3532 l_reservation_record.reservation_quantity := l_primary_res_qty;
3533 l_reservation_record.demand_source_header_id := p_dem_src_hdr_id;
3534 l_reservation_record.demand_source_line_id := p_dem_src_line_id;
3535 l_reservation_record.demand_source_type_id := p_dem_src_type_id;
3536
3537 l_reservation_record.ship_ready_flag := 2;
3538 l_reservation_record.attribute1 := NULL;
3539 l_reservation_record.attribute2 := NULL;
3540 l_reservation_record.attribute3 := NULL;
3541 l_reservation_record.attribute4 := NULL;
3542 l_reservation_record.attribute5 := NULL;
3543 l_reservation_record.attribute6 := NULL;
3544 l_reservation_record.attribute7 := NULL;
3545 l_reservation_record.attribute8 := NULL;
3546 l_reservation_record.attribute9 := NULL;
3547 l_reservation_record.attribute10 := NULL;
3548 l_reservation_record.attribute11 := NULL;
3549 l_reservation_record.attribute12 := NULL;
3550 l_reservation_record.attribute13 := NULL;
3551 l_reservation_record.attribute14 := NULL;
3552 l_reservation_record.attribute15 := NULL;
3553 l_reservation_record.attribute_category := NULL;
3554 l_reservation_record.lpn_id := p_lpn_id;
3555 l_reservation_record.pick_slip_number := NULL;
3556 l_reservation_record.lot_number_id := NULL;
3557 l_reservation_record.lot_number := l_lot_number;
3558 l_reservation_record.subinventory_id := NULL;
3559 l_reservation_record.subinventory_code := p_subinventory_code;
3560 l_reservation_record.locator_id := p_locator_id;
3561 l_reservation_record.supply_source_type_id := 13;
3562 l_reservation_record.supply_source_line_detail := NULL;
3563 l_reservation_record.supply_source_name := NULL;
3564 l_reservation_record.supply_source_header_id := p_dem_src_hdr_id;
3565 l_reservation_record.supply_source_line_id := p_dem_src_line_id;
3566 l_reservation_record.external_source_line_id := NULL;
3567 l_reservation_record.external_source_code := NULL;
3568 l_reservation_record.autodetail_group_id := NULL;
3569 l_reservation_record.demand_source_delivery := NULL;
3570 l_reservation_record.demand_source_name := NULL;
3571 l_reservation_record.requirement_date := p_requirement_date;
3572
3573 IF (l_debug = 1) THEN
3574 print_debug('CREATE_RETURN_RESV:**********Calling create_reservations with foll. parameters********');
3575 print_debug('CREATE_RETURN_RESV:org id: ' || p_org_id);
3576 print_debug('CREATE_RETURN_RESV:item id: ' || p_item_id);
3577 print_debug('CREATE_RETURN_RESV:rev: ' || p_revision);
3578 print_debug('CREATE_RETURN_RESV:UOM: ' || l_uom_code);
3579 print_debug('CREATE_RETURN_RESV:res qty: ' || l_primary_res_qty);
3580 print_debug('CREATE_RETURN_RESV:lot: ' || l_lot_number);
3581 print_debug('CREATE_RETURN_RESV:sub: ' || p_subinventory_code);
3582 print_debug('CREATE_RETURN_RESV:loc: ' || p_locator_id);
3583 print_debug('CREATE_RETURN_RESV:lpn_id: ' || p_lpn_id);
3584 print_debug('CREATE_RETURN_RESV:dem_src_type: ' || p_dem_src_type_id);
3585 print_debug('CREATE_RETURN_RESV:dem_src_hdr_id: ' || p_dem_src_hdr_id);
3586 print_debug('CREATE_RETURN_RESV:dem_src_line_id: ' || p_dem_src_line_id);
3587 END IF;
3588
3589 --Call the Create Reservations API
3590 INV_RESERVATION_PUB.CREATE_RESERVATION(
3591 x_return_status => x_return_status,
3592 x_msg_count => x_msg_count,
3593 x_msg_data => x_msg_data,
3594 x_serial_number => l_dummy_sn,
3595 x_quantity_reserved => l_quantity_reserved,
3596 x_reservation_id => l_reservation_id,
3597 p_api_version_number => 1.0,
3598 p_init_msg_lst => FND_API.G_FALSE,
3599 p_rsv_rec => l_reservation_record,
3600 p_partial_reservation_flag => FND_API.G_TRUE,
3601 p_force_reservation_flag => FND_API.G_TRUE,
3602 p_serial_number => l_dummy_sn,
3603 p_validation_flag => FND_API.G_TRUE);
3604
3605 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3606 IF (l_debug = 1) THEN
3607 print_debug('CREATE_RETURN_RESV:error creating reservation: status:' || x_return_status || 'mess:' || sqlerrm);
3608 END IF;
3609 RAISE FND_API.G_EXC_ERROR;
3610 END IF;
3611 IF (l_debug = 1) THEN
3612 print_debug('CREATE_RETURN_RESV:Reservation created successfully. Reservation Id: ' || l_reservation_id || ' . Quantity Reserved: ' || l_quantity_reserved);
3613 END IF;
3614 END IF; --IF l_create_res = TRUE THEN
3615
3616 IF (l_lot_control_code = 1) THEN
3617 EXIT;
3618 END IF;
3619 END LOOP;
3620
3621 IF (l_wms_po_j_higher) THEN
3622 CLOSE c_lots;
3623 ELSE
3624 CLOSE c_lots_old;
3625 END IF;
3626
3627 EXCEPTION
3628 WHEN fnd_api.g_exc_error THEN
3629 x_return_status := fnd_api.g_ret_sts_error;
3630 fnd_msg_pub.count_and_get
3631 ( p_count => x_msg_count,
3632 p_data => x_msg_data
3633 );
3634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3635 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3636 IF (l_debug = 1) THEN
3637 print_debug('unxp:' || sqlerrm);
3638 END IF;
3639 fnd_msg_pub.count_and_get
3640 ( p_count => x_msg_count,
3641 p_data => x_msg_data
3642 );
3643 WHEN OTHERS THEN
3644 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3645 fnd_msg_pub.count_and_get
3646 ( p_count => x_msg_count,
3647 p_data => x_msg_data
3648 );
3649 END CREATE_RETURN_RESV;
3650
3651 END;
[Home] [Help]