1 PACKAGE BODY WMS_RETURN_SV AS
2 /* $Header: WMSRETNB.pls 120.13.12020000.5 2013/03/19 15:00:48 ssingams 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 p_secondary_quantity IN NUMBER :=NULL -- 13399743
1006 )
1007 IS
1008
1009 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1010 l_transactable_flag VARCHAR2(1) := NULL; --bug 5068433
1011 l_validation_level NUMBER := fnd_api.g_valid_level_full; --bug 5068433
1012 l_sec_qty NUMBER := null; -- 12621897
1013 l_sec_uom_code VARCHAR2(50) := null; -- 12621897
1014
1015 BEGIN
1016
1017 IF (l_debug = 1) THEN
1018 print_debug('=== Calling WMS_Container_PUB.PackUnpack_Container ===');
1019 print_debug('p_lpn_id =>' || p_lpn_id);
1020 print_debug('p_lot_number =>' || p_lot_number);
1021 print_debug('p_from_serial_number =>' || p_from_serial_number);
1022 print_debug('p_to_serial_number =>' || p_to_serial_number);
1023 print_debug('p_quantity =>' || p_quantity);
1024 print_debug('p_uom =>' || p_uom);
1025 print_debug('p_subinventory =>' || p_subinventory);
1026 print_debug('p_locator_id =>' || p_locator_id);
1027 print_debug('p_operation =>' || p_operation);
1028 print_debug('p_source_header_id =>' || p_source_header_id);
1029 print_debug('p_source_name =>' || p_source_name);
1030 print_debug('P_COST_GROUP_ID =>' || P_COST_GROUP_ID);
1031 print_debug('p_secondary_quantity =>' || p_secondary_quantity); -- 13399743
1032 END IF;
1033
1034 /* bug 13011555, removed the block defined for bug 5068433 and used
1035 inv_cache to improve performance */
1036 IF (inv_cache.set_item_rec(p_organization_id,p_content_item_id)) THEN
1037 l_transactable_flag := INV_CACHE.item_rec.mtl_transactions_enabled_flag;
1038 l_sec_uom_code := INV_CACHE.item_rec.secondary_uom_code;
1039 ELSE
1040 l_transactable_flag := 'Y';
1041 l_sec_uom_code :=NULL;
1042 IF (l_debug = 1) THEN
1043 print_debug('Error getting item transactable flag and sec_uom_code');
1044 END IF;
1045 END IF;
1046
1047 IF (l_debug = 1) THEN
1048 print_debug('l_transactable_flag =>' || l_transactable_flag);
1049 END IF;
1050
1051 IF l_transactable_flag = 'N' THEN --bug 5048633
1052 l_validation_level := fnd_api.g_valid_level_none;
1053 END IF;
1054
1055 IF (l_debug = 1) THEN
1056 print_debug('l_sec_uom_code =>' || l_sec_uom_code);--bug13011555
1057 END IF;
1058
1059 -- as part of the fix for bug13011555,removed the select statement used for bug 12621897, because wlc wont be there AT this point
1060 IF l_sec_uom_code IS NOT NULL THEN
1061
1062 l_sec_qty := p_secondary_quantity ;-- 13399743
1063 END IF;
1064
1065 IF (l_debug = 1) THEN
1066 print_debug('l_sec_qty =>' || l_sec_qty);--bug13011555
1067 END IF;
1068
1069
1070 WMS_Container_PUB.PackUnpack_Container(
1071 p_api_version => 1.0,
1072 p_validation_level => l_validation_level,--bug 5048633
1073 x_return_status => x_return_status,
1074 x_msg_count => x_msg_count,
1075 x_msg_data => x_msg_data,
1076 p_lpn_id => p_lpn_id,
1077 p_content_item_id => p_content_item_id,
1078 p_revision => p_revision,
1079 p_lot_number => p_lot_number,
1080 p_from_serial_number => p_from_serial_number,
1081 p_to_serial_number => p_to_serial_number,
1082 p_quantity => p_quantity,
1083 p_uom => p_uom,
1084 p_organization_id => p_organization_id,
1085 p_subinventory => p_subinventory,
1086 p_locator_id => p_locator_id,
1087 p_operation => p_operation,
1088 p_source_header_id => p_source_header_id,
1089 p_source_name => p_source_name,
1090 p_cost_group_id => p_cost_group_id,
1091 p_sec_uom => l_sec_uom_code, -- 12621897
1092 p_sec_quantity => l_sec_qty -- 12621897
1093 );
1094
1095 IF (l_debug = 1) THEN
1096 print_debug('***************Output Parameters********************');
1097 print_debug('x_return_status =>' || x_return_status);
1098 print_debug('x_msg_count =>' || x_msg_count);
1099 print_debug('x_msg_data =>' || x_msg_data);
1100 END IF;
1101
1102 --IF (x_return_status <> FND_API.G_RET_STS_SUCCESS or x_msg_count > 0) THEN
1103 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1104 x_return_status := 'E';
1105 IF (l_debug = 1) THEN
1106 print_debug('Errored out...');
1107 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
1108 print_debug(x_msg_data);
1109 END IF;
1110 END IF;
1111
1112 END PackUnpack_Container;
1113
1114 /* Called from INVRCVFB.pls
1115 ** This procedure is used to unpack LPN for Return To Vendor and Correction
1116 ** Transactions with parent transaction type = RECEIVING as Inventory Manager
1117 ** is not called for these transactions.
1118 */
1119
1120 PROCEDURE txn_complete(
1121 p_group_id IN NUMBER,
1122 p_txn_status IN VARCHAR2, -- TRUE/FALSE
1123 p_txn_mode IN VARCHAR2, -- ONLINE/IMMEDIATE
1124 x_return_status OUT NOCOPY VARCHAR2,
1125 x_msg_data OUT NOCOPY VARCHAR2,
1126 x_msg_count OUT NOCOPY NUMBER) IS
1127
1128 /* The first part of the cursor is for Plain and Lot controlled items.
1129 ** The second part of the cursor is for Serial and Lot/Serial controlled items.
1130 */
1131
1132 CURSOR c_lpn_cnts IS
1133 SELECT wlpnc.organization_id
1134 , wlpn.subinventory_code subinventory
1135 , wlpn.locator_id
1136 , rt.lpn_id lpn_id
1137 , rt.transfer_lpn_id
1138 , wlpnc.inventory_item_id
1139 , wlpnc.revision
1140 , wlpnc.lot_number
1141 , to_char(null) serial_number
1142 , wlpnc.quantity
1143 , wlpnc.uom_code
1144 , rt.transaction_type
1145 , rt.interface_transaction_id
1146 , wlpnc.COST_GROUP_ID cg_id
1147 , rt.destination_type_code
1148 , rt.quantity rt_quantity
1149 , rt.parent_transaction_id
1150 FROM wms_license_plate_numbers wlpn, wms_lpn_contents wlpnc, rcv_transactions rt
1151 WHERE rt.group_id = p_group_id
1152 AND (((( rt.transaction_type = 'RETURN TO VENDOR'
1153 AND rt.lpn_id IS NOT NULL -- 3603808
1154 )
1155 OR
1156 ( rt.transaction_type = 'RETURN TO CUSTOMER'
1157 -- AND rt.transfer_lpn_id IS NOT NULL fix for 4389811
1158 AND rt.lpn_id IS NOT NULL
1159 ))
1160 -- to eliminate the row being selected for a rtv
1161 -- from inventory as pack unpack for that is already
1162 -- taken care of in inventory tm.
1163 AND NOT exists (SELECT 1
1164 FROM rcv_transactions rt2
1165 WHERE rt2.interface_transaction_id = rt.interface_transaction_id
1166 AND rt2.transaction_type = 'RETURN TO RECEIVING'
1167 AND rt2.group_id = p_group_id))
1168 OR (rt.transaction_type = 'CORRECT')
1169 OR (rt.transaction_type = 'RETURN TO RECEIVING'
1170 AND rt.transfer_lpn_id IS NOT NULL
1171 AND rt.lpn_id IS NOT NULL))
1172 AND rt.user_entered_flag = 'Y'
1173 AND wlpnc.source_name = rt.transaction_type
1174 AND wlpnc.source_header_id = rt.interface_transaction_id
1175 AND nvl(wlpnc.serial_summary_entry,2) <> 1
1176 AND wlpn.lpn_id = wlpnc.parent_lpn_id
1177 UNION ALL
1178 SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
1179 msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
1180 msn.inventory_item_id, msn.revision, msn.lot_number,
1181 msn.serial_number, to_number(null) quantity, wlpnc.uom_code uom_code, rt.transaction_type,
1182 rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1183 rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1184 FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions rt
1185 WHERE msn.last_txn_source_name = rt.transaction_type
1186 AND msn.last_txn_source_id = rt.interface_transaction_id
1187 AND rt.group_id = p_group_id
1188 AND (((( rt.transaction_type = 'RETURN TO VENDOR' -- 3603808
1189 AND rt.lpn_id IS NOT NULL
1190 )
1191 OR
1192 ( rt.transaction_type = 'RETURN TO CUSTOMER'
1193 -- AND rt.transfer_lpn_id IS NOT NULL fix for 4389811
1194 AND rt.lpn_id IS NOT NULL
1195 ))
1196 AND NOT exists (SELECT 1
1197 FROM rcv_transactions rt2
1198 WHERE rt2.interface_transaction_id = rt.interface_transaction_id
1199 AND rt2.transaction_type = 'RETURN TO RECEIVING'
1200 AND rt2.group_id = p_group_id))
1201 OR (rt.transaction_type = 'CORRECT') OR (rt.transaction_type = 'RETURN TO RECEIVING'
1202 AND rt.transfer_lpn_id IS NOT NULL
1203 AND rt.lpn_id IS NOT NULL))
1204 AND rt.user_entered_flag = 'Y'
1205 AND wlpnc.parent_lpn_id = msn.lpn_id
1206 AND wlpnc.inventory_item_id = msn.inventory_item_id;
1207
1208 -- Bug# 3281512 - Performance Fixes
1209 -- Also select for the item_id column in RTI so we can use
1210 -- this in looking up the values in MSN.
1211 CURSOR c_failure IS
1212 SELECT transaction_type, interface_transaction_id, item_id
1213 FROM rcv_transactions_interface rti
1214 WHERE rti.group_id = p_group_id
1215 AND rti.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER','RETURN TO RECEIVING','CORRECT');
1216
1217 CURSOR c_newly_packed IS
1218 SELECT wlpnc.organization_id
1219 , rti.lpn_id lpn_id
1220 , rti.transfer_lpn_id
1221 , wlpnc.inventory_item_id
1222 , wlpnc.revision
1223 , wlpnc.lot_number
1224 , to_char(null) serial_number
1225 , wlpnc.quantity
1226 , wlpnc.uom_code
1227 , rti.transaction_type
1228 , rti.interface_transaction_id
1229 , rti.destination_type_code
1230 , rti.quantity rti_quantity
1231 , rti.parent_transaction_id
1232 FROM wms_lpn_contents wlpnc, rcv_transactions_interface rti, rcv_transactions rt
1233 WHERE rti.group_id = p_group_id
1234 AND rti.transaction_type = 'CORRECT'
1235 AND rt.transaction_id = rti.parent_transaction_id
1236 AND ((rt.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') AND rti.quantity < 0) OR
1237 (rt.transaction_type = 'RECEIVE' AND rti.quantity > 0))
1238 AND wlpnc.source_name = rti.transaction_type
1239 AND wlpnc.source_header_id = rti.interface_transaction_id
1240 AND nvl(wlpnc.serial_summary_entry,2) <> 1
1241 UNION ALL
1242 SELECT msn.current_organization_id organization_id
1243 , rti.lpn_id
1244 , rti.transfer_lpn_id
1245 , msn.inventory_item_id
1246 , msn.revision
1247 , msn.lot_number
1248 , msn.serial_number
1249 , to_number(null) quantity
1250 , wlpnc.uom_code
1251 , rti.transaction_type
1252 , rti.interface_transaction_id
1253 , rti.destination_type_code
1254 , rti.quantity rti_quantity
1255 , rti.parent_transaction_id
1256 FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions_interface rti, rcv_transactions rt
1257 WHERE msn.last_txn_source_name = rti.transaction_type
1258 AND msn.last_txn_source_id = rti.interface_transaction_id
1259 AND rti.group_id = p_group_id
1260 AND rti.transaction_type = 'CORRECT'
1261 AND rt.transaction_id = rti.parent_transaction_id
1262 AND ((rt.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') AND rti.quantity < 0) OR
1263 (rt.transaction_type = 'RECEIVE' AND rti.quantity > 0))
1264 AND wlpnc.parent_lpn_id = msn.lpn_id
1265 AND wlpnc.inventory_item_id = msn.inventory_item_id;
1266
1267 CURSOR c_neg_deliver_ser IS
1268 SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
1269 msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
1270 msn.inventory_item_id, msn.revision, msn.lot_number,
1271 msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1272 rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1273 rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1274 FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1275 WHERE rt.group_id = p_group_id
1276 AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1277 OR (rt.transaction_type = 'RETURN TO RECEIVING'
1278 AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
1279 AND msn.current_subinventory_code = rt.from_subinventory
1280 AND Nvl(msn.current_locator_id,-1) = Nvl(rt.from_locator_id,-1)
1281 AND msn.current_organization_id = rt.organization_id
1282 AND rsl.shipment_header_id = rt.shipment_header_id
1283 AND rsl.shipment_line_id = rt.shipment_line_id
1284 AND msn.inventory_item_id = rsl.item_id
1285 AND rt.user_entered_flag = 'Y'
1286 AND msn.current_status = 4
1287 AND exists (SELECT '1' FROM rcv_transactions rt2
1288 WHERE rt2.transaction_id = rt.parent_transaction_id
1289 AND rt2.transaction_type = 'DELIVER');
1290
1291 CURSOR c_neg_deliver_ser_lpng IS
1292 SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
1293 msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
1294 msn.inventory_item_id, msn.revision, msn.lot_number,
1295 msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1296 rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1297 rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1298 FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1299 WHERE rt.transaction_date >= (Sysdate - 1)
1300 AND rt.lpn_group_id = p_group_id
1301 AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1302 OR (rt.transaction_type = 'RETURN TO RECEIVING'
1303 AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
1304 AND msn.current_subinventory_code = rt.from_subinventory
1305 AND Nvl(msn.current_locator_id,-1) = Nvl(rt.from_locator_id,-1)
1306 AND msn.current_organization_id = rt.organization_id
1307 AND rsl.shipment_header_id = rt.shipment_header_id
1308 AND rsl.shipment_line_id = rt.shipment_line_id
1309 AND msn.inventory_item_id = rsl.item_id
1310 AND rt.user_entered_flag = 'Y'
1311 AND msn.current_status = 4
1312 AND exists (SELECT '1' FROM rcv_transactions rt2
1313 WHERE rt2.transaction_id = rt.parent_transaction_id
1314 AND rt2.transaction_type = 'DELIVER');
1315
1316 -- Added new cursor to address the Bug 4489361
1317 CURSOR c_neg_deliver_ser_1159 IS
1318 SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
1319 msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
1320 msn.inventory_item_id, msn.revision, msn.lot_number,
1321 msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1322 rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1323 rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1324 FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1325 WHERE rt.group_id = p_group_id
1326 AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1327 OR (rt.transaction_type = 'RETURN TO RECEIVING'
1328 AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
1329 AND msn.current_subinventory_code = rt.subinventory
1330 AND Nvl(msn.current_locator_id,-1) = Nvl(rt.locator_id,-1)
1331 AND msn.current_organization_id = rt.organization_id
1332 AND rsl.shipment_header_id = rt.shipment_header_id
1333 AND rsl.shipment_line_id = rt.shipment_line_id
1334 AND msn.inventory_item_id = rsl.item_id
1335 AND rt.user_entered_flag = 'Y'
1336 AND msn.current_status = 4
1337 AND exists (SELECT '1' FROM rcv_transactions rt2
1338 WHERE rt2.transaction_id = rt.parent_transaction_id
1339 AND rt2.transaction_type = 'DELIVER');
1340
1341 ret boolean;
1342 l_lpn_id number;
1343 l_pack_lpn NUMBER;
1344 l_unpack_lpn NUMBER;
1345 l_parent_transaction_type VARCHAR2(25);
1346 l_routing_header_id NUMBER;
1347 l_insp_status NUMBER;
1348 l_status NUMBER;
1349
1350 /* New vars needed for deleting reservations*/
1351 l_qry_res_rec INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
1352 l_res_rec_to_delete INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
1353 l_src_doc_code VARCHAR2(10);
1354 l_dem_src_type_id NUMBER;
1355 l_mtl_reservation_tbl INV_RESERVATION_GLOBAL.MTL_RESERVATION_TBL_TYPE;
1356 l_mtl_reservation_tbl_count NUMBER;
1357 l_dummy_sn INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
1358 l_error_code NUMBER;
1359 l_res_lpn_id NUMBER;
1360 l_unreserve_qty NUMBER;
1361 l_express_return VARCHAR2(1);
1362
1363 -- Bug# 3631611: Performance Fixes
1364 -- Break the c_reservation_csr into two cursors, one where
1365 -- p_txn_mode = 'LPN_GROUP' and one for all other cases.
1366 CURSOR c_reservation_csr IS
1367 SELECT rt.source_document_code,
1368 rt.organization_id,
1369 rsl.item_id,
1370 rt.subinventory,
1371 rt.locator_id,
1372 rt.from_subinventory,
1373 rt.from_locator_id,
1374 rt.lpn_id,
1375 rt.quantity
1376 FROM rcv_transactions rt, rcv_shipment_lines rsl
1377 WHERE rt.group_id = p_group_id
1378 AND p_txn_mode <> 'LPN_GROUP'
1379 AND rt.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
1380 AND rt.shipment_line_id = rsl.shipment_line_id;
1381
1382 CURSOR c_reservation_lpn_grp_csr IS
1383 SELECT rt.source_document_code,
1384 rt.organization_id,
1385 rsl.item_id,
1386 rt.subinventory,
1387 rt.locator_id,
1388 rt.from_subinventory,
1389 rt.from_locator_id,
1390 rt.lpn_id,
1391 rt.quantity
1392 FROM rcv_transactions rt, rcv_shipment_lines rsl
1393 WHERE rt.transaction_date >= (SYSDATE-1)
1394 AND rt.lpn_group_id = p_group_id
1395 AND p_txn_mode = 'LPN_GROUP'
1396 AND rt.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
1397 AND rt.shipment_line_id = rsl.shipment_line_id;
1398
1399 -- Bug# 3631611: Performance Fixes
1400 -- Also define a cursor record type to fetch the results into.
1401 -- Since both cursors have the same return values, we can use this
1402 -- record type for both.
1403 l_res_csr c_reservation_csr%ROWTYPE;
1404
1405 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1406 l_wms_po_j_higher BOOLEAN := FALSE;
1407
1408 -- Bug# 3281512 - Performance Fixes
1409 -- Cursor to get rid of the hash join problem
1410 CURSOR c_interface_txn_id IS
1411 SELECT interface_transaction_id
1412 FROM rcv_transactions
1413 WHERE group_id = p_group_id;
1414 l_interface_txn_id NUMBER;
1415 BEGIN
1416
1417 x_return_status := FND_API.G_RET_STS_SUCCESS;
1418 IF (l_debug = 1) THEN
1419 print_debug('======== Start txn_complete =========');
1420 print_debug(' p_group_id => ' || p_group_id);
1421 print_debug(' p_txn_status => ' || p_txn_status);
1422 print_debug(' p_txn_mode => ' || p_txn_mode);
1423 END IF;
1424
1425 /* FP-J Lot/Serial Support Enhancement
1426 * Read the currentand PO patch levels and set the flag (that would be used to
1427 * match the Lot Number and the LPN) accordingly
1428 */
1429 IF ((WMS_UI_TASKS_APIS.g_wms_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j) AND
1430 (WMS_UI_TASKS_APIS.g_po_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j_po)) THEN
1431 l_wms_po_j_higher := TRUE;
1432 IF (l_debug = 1) THEN
1433 print_debug('WMS and PO patch levels are J or higher', 4);
1434 END IF;
1435 ELSE
1436 l_wms_po_j_higher := FALSE;
1437 IF (l_debug = 1) THEN
1438 print_debug('Either WMS or/and PO patch level(s) are lower than J', 4);
1439 END IF;
1440 END IF;
1441
1442 IF (p_txn_status = 'TRUE') THEN
1443 print_debug('Within p_txn_status=TRUE', 4);
1444 IF (l_wms_po_j_higher = FALSE) THEN
1445
1446 print_debug('Within l_wms_po_j_higher = FALSE', 4);
1447 FOR i IN c_lpn_cnts LOOP
1448
1449 IF (l_debug = 1) THEN
1450 print_debug('Txn Type=' || i.transaction_type || ', Destination=' || i.destination_type_code);
1451 print_debug('Txn qty=' || i.rt_quantity);
1452 print_debug('From LPN=' || i.lpn_id);
1453 print_debug('To LPN=' || i.transfer_lpn_id);
1454 END IF;
1455
1456 IF i.transaction_type <> 'CORRECT'
1457 AND i.transaction_type <> 'RETURN TO RECEIVING' THEN
1458 -- dont want to call pup for return to receiving as it is done
1459 -- in inventory tm.
1460 IF (l_debug = 1) THEN
1461 print_debug('transaction_type <> CORRECT, RETURN TO RECEIVING');
1462 END IF;
1463 -- Need to call it only for a rtv from receiving as other
1464 -- cases are taken care of in inventory tm.
1465 -- Even a rtv/rtc from inventory creates 2 txns. - return
1466 -- to receiving and rtv/rtc for same transaction_interface_id
1467 -- and the same group_id. Unpacking for it has already happened
1468 -- in the inventory TM (INVTRXWB.pls) so here should not call
1469 -- it again for rtv/rtc from inventory.
1470 PackUnpack_Container(
1471 x_return_status => x_return_status,
1472 x_msg_count => x_msg_count,
1473 x_msg_data => x_msg_data,
1474 p_lpn_id => i.lpn_id,
1475 p_content_item_id => i.inventory_item_id,
1476 p_revision => i.revision,
1477 p_lot_number => i.lot_number,
1478 p_from_serial_number => i.serial_number,
1479 p_to_serial_number => i.serial_number,
1480 p_quantity => i.quantity,
1481 p_uom => i.uom_code,
1482 p_organization_id => i.organization_id,
1483 p_subinventory => NULL,
1484 p_locator_id => NULL,
1485 p_operation => 2, -- unpack
1486 p_source_header_id => i.interface_transaction_id,
1487 p_source_name => i.transaction_type,
1488 p_cost_group_id => i.cg_id
1489 );
1490 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1491 IF (l_debug = 1) THEN
1492 print_debug('Error while unpacking for rtv/rtc from receiving');
1493 END IF;
1494 RAISE fnd_api.g_exc_error; --return;
1495 END IF;
1496
1497 ELSIF i.transaction_type = 'CORRECT' THEN -- <> CORRECT
1498 IF (l_debug = 1) THEN
1499 print_debug('transaction_type = CORRECT');
1500 END IF;
1501
1502 BEGIN
1503 SELECT transaction_type, routing_header_id
1504 INTO l_parent_transaction_type, l_routing_header_id
1505 FROM rcv_transactions
1506 WHERE transaction_id = i.parent_transaction_id;
1507 IF (l_debug = 1) THEN
1508 print_debug('l_parent_transaction_type=' || l_parent_transaction_type);
1509 END IF;
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 l_parent_transaction_type := NULL;
1513 IF (l_debug = 1) THEN
1514 print_debug('Error l_parent_transaction_type=' || l_parent_transaction_type);
1515 END IF;
1516 END;
1517
1518 IF i.lpn_id IS NOT NULL AND i.transfer_lpn_id IS NOT NULL AND
1519 l_parent_transaction_type in ('ACCEPT','REJECT') THEN
1520
1521 /* Accept or Reject */
1522 IF (l_debug = 1) THEN
1523 print_debug('Correct Txn = Accept or Reject');
1524 END IF;
1525 IF i.rt_quantity < 0 THEN
1526 l_unpack_lpn := i.transfer_lpn_id;
1527 l_pack_lpn := i.lpn_id;
1528 ELSE
1529 l_unpack_lpn := i.lpn_id;
1530 l_pack_lpn := i.transfer_lpn_id;
1531 END IF;
1532
1533 PackUnpack_Container(
1534 x_return_status => x_return_status,
1535 x_msg_count => x_msg_count,
1536 x_msg_data => x_msg_data,
1537 p_lpn_id => l_unpack_lpn,
1538 p_content_item_id => i.inventory_item_id,
1539 p_revision => i.revision,
1540 p_lot_number => i.lot_number,
1541 p_from_serial_number => i.serial_number,
1542 p_to_serial_number => i.serial_number,
1543 p_quantity => Abs(i.quantity),
1544 p_uom => i.uom_code,
1545 p_organization_id => i.organization_id,
1546 p_subinventory => NULL,
1547 p_locator_id => NULL,
1548 p_operation => 2, -- unpack
1549 p_source_header_id => i.interface_transaction_id,
1550 p_source_name => i.transaction_type,
1551 p_cost_group_id => i.cg_id
1552 );
1553
1554 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1555 IF (l_debug = 1) THEN
1556 print_debug('Error while unpacking in txn_complete for correction OF ACCEPT/REJECT');
1557 END IF;
1558 RAISE fnd_api.g_exc_error; --return;
1559 END IF;
1560
1561 PackUnpack_Container(
1562 x_return_status => x_return_status,
1563 x_msg_count => x_msg_count,
1564 x_msg_data => x_msg_data,
1565 p_lpn_id => l_pack_lpn,
1566 p_content_item_id => i.inventory_item_id,
1567 p_revision => i.revision,
1568 p_lot_number => i.lot_number,
1569 p_from_serial_number => i.serial_number,
1570 p_to_serial_number => i.serial_number,
1571 p_quantity => Abs(i.quantity),
1572 p_uom => i.uom_code,
1573 p_organization_id => i.organization_id,
1574 p_subinventory => NULL,
1575 p_locator_id => NULL,
1576 p_operation => 1, -- pack
1577 p_source_header_id => NULL,
1578 p_source_name => NULL,
1579 p_cost_group_id => NULL
1580 );
1581
1582 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1583 IF (l_debug = 1) THEN
1584 print_debug('Error while packing in txn_complete for correction OF ACCEPT/REJECT');
1585 END IF;
1586 RAISE fnd_api.g_exc_error; --return;
1587 END IF;
1588
1589 -- Update the context for the pack lpn to 'resides in
1590 -- receiving' as packunpack api may have changed it to
1591 -- 'Defined but not used'
1592
1593 UPDATE wms_license_plate_numbers
1594 SET lpn_context = wms_container_pub.lpn_context_rcv
1595 WHERE lpn_id = l_pack_lpn;
1596
1597 ELSIF ((i.lpn_id IS NOT NULL OR i.transfer_lpn_id IS NOT NULL) AND
1598 l_parent_transaction_type in ('RECEIVE','RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
1599
1600 --IF i.lpn_id IS NOT NULL AND i.transfer_lpn_id IS NULL THEN
1601 -- Making it more explicit.
1602 IF (l_parent_transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
1603 AND i.rt_quantity > 0)
1604 OR (l_parent_transaction_type = 'RECEIVE' AND i.rt_quantity < 0) THEN
1605
1606 /* +ve Correct on RTV/RTC or -ve correction on Receive Txns */
1607 IF l_parent_transaction_type = 'RECEIVE' THEN
1608 l_lpn_id := i.transfer_lpn_id;
1609 ELSE
1610 l_lpn_id := i.lpn_id;
1611 END IF;
1612
1613 IF (l_debug = 1) THEN
1614 print_debug('Correct Txn = +ve Correct RTV/RTC or -ve on Receive Txns');
1615 END IF;
1616
1617 PackUnpack_Container(
1618 x_return_status => x_return_status,
1619 x_msg_count => x_msg_count,
1620 x_msg_data => x_msg_data,
1621 p_lpn_id => l_lpn_id,
1622 p_content_item_id => i.inventory_item_id,
1623 p_revision => i.revision,
1624 p_lot_number => i.lot_number,
1625 p_from_serial_number => i.serial_number,
1626 p_to_serial_number => i.serial_number,
1627 p_quantity => abs(i.quantity),
1628 p_uom => i.uom_code,
1629 p_organization_id => i.organization_id,
1630 p_subinventory => NULL,
1631 p_locator_id => NULL,
1632 p_operation => 2, -- unpack
1633 p_source_header_id => i.interface_transaction_id,
1634 p_source_name => i.transaction_type,
1635 p_cost_group_id => i.cg_id
1636 );
1637
1638 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1639 IF (l_debug = 1) THEN
1640 print_debug('Error in unpacking for Correct Txn = +ve Correct RTV/RTC or -ve on Receive Txns');
1641 END IF;
1642 RAISE fnd_api.g_exc_error; --return;
1643 END IF;
1644
1645 --ELSIF i.lpn_id IS NULL AND i.transfer_lpn_id IS NOT NULL THEN
1646 ELSIF (l_parent_transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
1647 AND i.rt_quantity < 0)
1648 OR (l_parent_transaction_type = 'RECEIVE' AND i.rt_quantity > 0) THEN
1649
1650 /* -ve Correct on RTV/RTC or +ve on Receive Txns */
1651 -- For a negative correction on RTV/RTC, the correction
1652 -- FORM has lpn_id that gets populated. Also for the
1653 -- negative correction of RTV we pack material into the
1654 -- lpn_id entered on the form.
1655 IF i.rt_quantity < 0 THEN l_lpn_id := i.lpn_id;
1656 ELSE l_lpn_id := i.transfer_lpn_id;
1657 END IF;
1658
1659 IF (l_debug = 1) THEN
1660 print_debug('Correct Txn = -ve Correct on RTV/RTC or +ve on Receive txns FOR lpn:'||l_lpn_id);
1661 END IF;
1662
1663 /* Need to unmark the contents that are already packed into receiving */
1664
1665 PackUnpack_Container(
1666 x_return_status => x_return_status,
1667 x_msg_count => x_msg_count,
1668 x_msg_data => x_msg_data,
1669 p_lpn_id => l_lpn_id,
1670 p_content_item_id => i.inventory_item_id,
1671 p_revision => i.revision,
1672 p_lot_number => i.lot_number,
1673 p_from_serial_number => i.serial_number,
1674 p_to_serial_number => i.serial_number,
1675 p_quantity => abs(i.quantity),
1676 p_uom => i.uom_code,
1677 p_organization_id => i.organization_id,
1678 p_subinventory => NULL,
1679 p_locator_id => NULL,
1680 p_operation => 2, -- unpack
1681 p_source_header_id => i.interface_transaction_id,
1682 p_source_name => i.transaction_type,
1683 p_cost_group_id => i.cg_id
1684 );
1685
1686 PackUnpack_Container(
1687 x_return_status => x_return_status,
1688 x_msg_count => x_msg_count,
1689 x_msg_data => x_msg_data,
1690 p_lpn_id => l_lpn_id,
1691 p_content_item_id => i.inventory_item_id,
1692 p_revision => i.revision,
1693 p_lot_number => i.lot_number,
1694 p_from_serial_number => i.serial_number,
1695 p_to_serial_number => i.serial_number,
1696 p_quantity => abs(i.quantity),
1697 p_uom => i.uom_code,
1698 p_organization_id => i.organization_id,
1699 p_subinventory => NULL,
1700 p_locator_id => NULL,
1701 p_operation => 1, -- pack
1702 p_source_header_id => NULL,
1703 p_source_name => NULL,
1704 p_cost_group_id => NULL
1705 );
1706
1707 -- If the parent transaction is a receive txn,
1708 -- update the lpn_context as while unpacking, the
1709 -- packunpack api might have changed the context
1710 -- to 'Defined But not used'
1711 IF (l_parent_transaction_type = 'RECEIVE') THEN
1712 UPDATE wms_license_plate_numbers
1713 SET lpn_context = wms_container_pub.lpn_context_rcv
1714 WHERE lpn_id = l_lpn_id;
1715 END IF;
1716
1717 END IF;
1718
1719 END IF;
1720 END IF; -- <> CORRECT
1721
1722 IF (i.serial_number IS NOT NULL
1723 AND i.transaction_type = 'CORRECT'
1724 AND l_parent_transaction_type IN ('RECEIVE','RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
1725 -- The status of the serial number should be changes to issued
1726 -- out of stores (4) if the serial number is going out of receiving
1727 -- to the vendor which will be +ve correction of RTV and
1728 -- -ve correction of a receive transaction.
1729 -- For -ve corrections of rtvs and +ve correction of receive
1730 -- transaction the serial number is received into receiving
1731 -- so should have a current_status which is same as it has
1732 -- when received through mobile which is 5.
1733 IF (l_parent_transaction_type = 'RECEIVE' AND i.rt_quantity>0)
1734 OR (l_parent_transaction_type IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
1735 AND i.rt_quantity < 0) THEN
1736
1737 /* FP-J Lot/Serial Support Enhancement
1738 * If WMS and PO J are installed, then the current status for serials in
1739 * receiving should be "Resides in Receiving"
1740 * If WMS or PO patch levels are less than J, then retain the current
1741 * status of "Resides in Intransit"
1742 */
1743 IF (l_wms_po_j_higher) THEN
1744 l_status := 7;
1745 ELSE
1746 l_status := 5;
1747 END IF;
1748 /* FP-J Lot/Serial Support Enhancement */
1749 ELSE
1750 l_status := 4;
1751 END IF;
1752
1753 IF (l_parent_transaction_type = 'RECEIVE' AND i.rt_quantity>0)
1754 THEN
1755 IF (l_routing_header_id = 2) THEN
1756 l_insp_status := 1;
1757 ELSE
1758 l_insp_status := NULL;
1759 END IF;
1760 END IF;
1761
1762 -- updating status of the sn. to 5 for -ve correction of rtv
1763 -- since this means that the sn are to be packed in receiving.
1764 UPDATE mtl_serial_numbers
1765 SET current_status = l_status
1766 , inspection_status = l_insp_status
1767 , last_txn_source_name = NULL
1768 , last_txn_source_id = NULL
1769 , group_mark_id = NULL
1770 , line_mark_id = NULL
1771 , cost_group_id = NULL
1772 WHERE serial_number = i.serial_number
1773 AND inventory_item_id = i.inventory_item_id;
1774 IF (l_debug = 1) THEN
1775 print_debug('Updated sn for correction of rtv and receive to status:'||l_status);
1776 END IF;
1777 ELSIF (i.serial_number IS NOT NULL
1778 AND i.transaction_type = 'CORRECT'
1779 AND l_parent_transaction_type IN ('ACCEPT','REJECT')) THEN
1780 IF i.rt_quantity < 0 THEN
1781 l_status := 1;
1782 ELSE
1783 IF l_parent_transaction_type = 'ACCEPT' THEN
1784 l_status := 2;
1785 ELSE
1786 l_status := 3;
1787 END IF;
1788 END IF;
1789 -- Leave the current status of the serial number same
1790 -- as the serial number continues to stay in receiving
1791 -- need to change just the inspection status appropriately.
1792 UPDATE mtl_serial_numbers
1793 SET inspection_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 IF (l_debug = 1) THEN
1802 print_debug('Updated inspection status of sn for correction of accept/reject');
1803 END IF;
1804 ELSIF (i.serial_number IS NOT NULL
1805 AND i.lpn_id IS NOT NULL
1806 AND i.transfer_lpn_id IS NULL
1807 AND i.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
1808
1809 /* Not for Accept or Reject */
1810 l_status := 4;
1811 UPDATE mtl_serial_numbers
1812 SET current_status = l_status
1813 , last_txn_source_name = NULL
1814 , last_txn_source_id = NULL
1815 , group_mark_id = NULL
1816 , line_mark_id = NULL
1817 , cost_group_id = NULL
1818 WHERE serial_number = i.serial_number
1819 AND inventory_item_id = i.inventory_item_id;
1820
1821 ELSIF (i.serial_number IS NOT NULL
1822 AND i.transaction_type in ('RETURN TO RECEIVING')) THEN
1823 --l_status := 5;
1824 /* FP-J Lot/Serial Support Enhancement
1825 * If WMS and PO J are installed, then the current status for serials in
1826 * receiving should be "Resides in Receiving"
1827 * If WMS or PO patch levels are less than J, then retain the current
1828 * status of "Resides in Intransit"
1829 */
1830 IF (l_wms_po_j_higher) THEN
1831 l_status := 7;
1832 ELSE
1833 l_status := 5;
1834 END IF;
1835
1836 UPDATE mtl_serial_numbers
1837 SET current_status = l_status
1838 , last_txn_source_name = NULL
1839 , last_txn_source_id = NULL
1840 , group_mark_id = NULL
1841 , line_mark_id = NULL
1842 , cost_group_id = NULL
1843 WHERE serial_number = i.serial_number
1844 AND inventory_item_id = i.inventory_item_id;
1845
1846 END IF;
1847
1848 END LOOP;
1849 END IF; --IF (l_wms_po_j_higher = FALSE) THEN
1850 --Check the express return profile value
1851 --FND_PROFILE.GET('WMS_EXPRESS_RETURN', l_express_return);
1852 --IF NVL(l_express_return,'Y') = 'N' THEN
1853 BEGIN
1854 -- Bug# 3631611: Performance Fixes
1855 -- Open the appropriate cursor based on p_txn_mode
1856 IF (p_txn_mode = 'LPN_GROUP') THEN
1857 OPEN c_reservation_lpn_grp_csr;
1858 ELSE
1859 OPEN c_reservation_csr;
1860 END IF;
1861 LOOP
1862 IF (p_txn_mode = 'LPN_GROUP') THEN
1863 FETCH c_reservation_lpn_grp_csr INTO l_res_csr;
1864 EXIT WHEN c_reservation_lpn_grp_csr%NOTFOUND;
1865 ELSE
1866 FETCH c_reservation_csr INTO l_res_csr;
1867 EXIT WHEN c_reservation_csr%NOTFOUND;
1868 END IF;
1869 --FOR l_res_csr IN c_reservation_csr LOOP
1870 l_src_doc_code := l_res_csr.source_document_code;
1871 -- Relieve the reservations created against this record
1872 IF l_src_doc_code = 'PO' THEN
1873 l_dem_src_type_id := 1;
1874 ELSIF l_src_doc_code = 'INTREQ' THEN
1875 l_dem_src_type_id := 7;
1876 ELSIF l_src_doc_code = 'RMA' THEN
1877 l_dem_src_type_id := 12;
1878 ELSE
1879 l_dem_src_type_id := 10;
1880 END IF;
1881
1882 --Form the query criteria for checking a reservation record
1883 l_qry_res_rec.demand_source_type_id := l_dem_src_type_id;
1884 l_qry_res_rec.organization_id := l_res_csr.organization_id;
1885 l_qry_res_rec.inventory_item_id := l_res_csr.item_id;
1886 IF (l_wms_po_j_higher) THEN
1887 l_qry_res_rec.subinventory_code := l_res_csr.from_subinventory;
1888 l_qry_res_rec.locator_id := l_res_csr.from_locator_id;
1889 ELSE
1890 l_qry_res_rec.subinventory_code := l_res_csr.subinventory;
1891 l_qry_res_rec.locator_id := l_res_csr.locator_id;
1892 END IF;
1893 l_qry_res_rec.lpn_id := l_res_csr.lpn_id;
1894
1895 IF (l_debug = 1) THEN
1896 print_debug('TXN_COMPLETE: Querying reservation using following parameters');
1897 print_debug('TXN_COMPLETE: Demand source type id:'||l_qry_res_rec.demand_source_type_id);
1898 print_debug('TXN_COMPLETE: Organization id:'||l_qry_res_rec.organization_id);
1899 print_debug('TXN_COMPLETE: Item id:'||l_qry_res_rec.inventory_item_id);
1900 print_debug('TXN_COMPLETE: Subinventory Code:'||l_qry_res_rec.subinventory_code);
1901 print_debug('TXN_COMPLETE: Locator id:'||l_qry_res_rec.locator_id);
1902 print_debug('TXN_COMPLETE: LPN id:'||l_qry_res_rec.lpn_id);
1903 END IF;
1904
1905 --Query all the reservation records for the above combinations
1906 INV_RESERVATION_PUB.QUERY_RESERVATION(
1907 p_api_version_number => 1.0,
1908 x_return_status => x_return_status,
1909 x_msg_count => x_msg_count,
1910 x_msg_data => x_msg_data,
1911 p_query_input => l_qry_res_rec,
1912 x_mtl_reservation_tbl => l_mtl_reservation_tbl,
1913 x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count,
1914 x_error_code => l_error_code
1915 );
1916
1917 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1918 IF (l_debug = 1) THEN
1919 print_debug('TXN_COMPLETE: Error while calling query_reservations');
1920 END IF;
1921 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count, p_data => x_msg_data);
1922 RAISE fnd_api.g_exc_error; --return;
1923 END IF;
1924
1925 IF (l_debug = 1) THEN
1926 print_debug('TXN_COMPLETE: No. of reservation recs found:'||l_mtl_reservation_tbl_count);
1927 END IF;
1928
1929 --Check all the records for the given demand source, header and line.
1930 FOR l_counter IN 1 .. l_mtl_reservation_tbl_count LOOP
1931 l_res_lpn_id := l_mtl_reservation_tbl(l_counter).lpn_id;
1932 l_unreserve_qty := l_res_csr.quantity;
1933
1934 --If a record whose LPN matches the LPN being returned
1935 IF (l_res_lpn_id = l_res_csr.lpn_id) THEN
1936 --Check the reservation quantity. If it is lesser than the quantity to be returned
1937 --update the reservation. If the entire quantity is to be returned, then
1938 --clear the reservation
1939 l_res_rec_to_delete := l_mtl_reservation_tbl(l_counter);
1940 IF (l_debug = 1) THEN
1941 print_debug('TXN_COMPLETE: Deleting the reservation...');
1942 END IF;
1943 INV_RESERVATION_PUB.DELETE_RESERVATION(
1944 p_api_version_number => 1.0,
1945 p_init_msg_lst => FND_API.G_FALSE,
1946 x_return_status => x_return_status,
1947 x_msg_count => x_msg_count,
1948 x_msg_data => x_msg_data,
1949 p_rsv_rec => l_res_rec_to_delete,
1950 p_serial_number => l_dummy_sn
1951 );
1952 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1953 IF (l_debug = 1) THEN
1954 print_debug('TXN_COMPLETE: Error while deleting reservations');
1955 END IF;
1956 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count, p_data => x_msg_data);
1957 RAISE fnd_api.g_exc_error; --return;
1958 END IF;
1959 IF (l_debug = 1) THEN
1960 print_debug('TXN_COMPLETE: Deleted the reservation record successfully');
1961 END IF;
1962 END IF; --End If the lpn_id of the reservation record matches
1963 END LOOP; --END delete all the reservation records
1964 END LOOP; --End for all the records for the group_id
1965 -- Bug# 3631611: Performance Fixes
1966 -- Close the appropriate reservation cursor that was opened
1967 IF (p_txn_mode = 'LPN_GROUP') THEN
1968 CLOSE c_reservation_lpn_grp_csr;
1969 ELSE
1970 CLOSE c_reservation_csr;
1971 END IF;
1972 EXCEPTION
1973 WHEN NO_DATA_FOUND THEN
1974 NULL;
1975 WHEN OTHERS THEN
1976 -- Bug# 3631611: Performance Fixes
1977 -- In case of exceptions, make sure these cursors are closed if open
1978 IF (c_reservation_lpn_grp_csr%ISOPEN) THEN
1979 CLOSE c_reservation_lpn_grp_csr;
1980 END IF;
1981 IF (c_reservation_csr%ISOPEN) THEN
1982 CLOSE c_reservation_csr;
1983 END IF;
1984 END;
1985 --END IF; --End If the "Express Returns" profile is "N"
1986
1987 -- If a -ve correction or return to receiving was performed on the DELIVER transaction then we
1988 -- need to update the current_status, sub and locator for the serial
1989 -- numbers - Bug 2490630
1990 IF (p_txn_mode <> 'LPN_GROUP') THEN
1991 -- Start of fix for the bug 4489361
1992 IF NOT l_wms_po_j_higher THEN
1993
1994 FOR irec IN c_neg_deliver_ser_1159 LOOP
1995 IF ((irec.transaction_type = 'CORRECT' AND irec.rt_quantity < 0)
1996 OR (irec.transaction_type = 'RETURN TO RECEIVING')) THEN
1997
1998 UPDATE mtl_serial_numbers
1999 SET
2000 current_status = 5
2001 , current_subinventory_code = NULL
2002 , current_locator_id = NULL
2003 , last_txn_source_name = NULL
2004 , last_txn_source_id = NULL
2005 , group_mark_id = NULL
2006 , line_mark_id = NULL
2007 , cost_group_id = NULL
2008 WHERE serial_number = irec.serial_number
2009 AND inventory_item_id = irec.inventory_item_id ;
2010
2011 IF (l_debug = 1) THEN
2012 print_debug('TXN_COMPLETE: Deliver Transaction. 1159.. updated serial... '||irec.serial_number);
2013 END IF;
2014 END IF; -- Txn type Check
2015 END LOOP;
2016 ELSE -- Check for l_wms_po_j_higher --End of fix for the bug 4489361
2017
2018 FOR irec IN c_neg_deliver_ser LOOP
2019 IF ((irec.transaction_type = 'CORRECT' AND irec.rt_quantity < 0)
2020 OR (irec.transaction_type = 'RETURN TO RECEIVING')) THEN
2021 /* FP-J Lot/Serial Support Enhancement
2022 * If WMS and PO J are installed, then the current status for serials in
2023 * receiving should be "Resides in Receiving"
2024 * If WMS or PO patch levels are less than J, then retain the current
2025 * status of "Resides in Intransit"
2026 */
2027 IF (l_wms_po_j_higher) THEN
2028 l_status := 7;
2029 ELSE
2030 l_status := 5;
2031 END IF;
2032 UPDATE mtl_serial_numbers
2033 SET
2034 --current_status = 5
2035 current_status = l_status
2036 , current_subinventory_code = NULL
2037 , current_locator_id = NULL
2038 , last_txn_source_name = NULL
2039 , last_txn_source_id = NULL
2040 , group_mark_id = NULL
2041 , line_mark_id = NULL
2042 , cost_group_id = NULL
2043 WHERE serial_number = irec.serial_number
2044 AND inventory_item_id = irec.inventory_item_id
2045 AND exists (SELECT '1' FROM rcv_serials_supply rss
2046 WHERE rss.serial_num = serial_number
2047 AND rss.supply_type_code = 'RECEIVING');
2048 IF (l_debug = 1) THEN
2049 print_debug('TXN_COMPLETE: Deliver Transaction... updated serial... '||irec.serial_number);
2050 END IF;
2051 END IF;
2052 END LOOP;
2053 END IF ; -- Check for l_wms_po_j_higher
2054 ELSE --IF (p_txn_mode <> 'LPN_GROUP') THEN
2055 FOR irec IN c_neg_deliver_ser_lpng LOOP
2056 IF ((irec.transaction_type = 'CORRECT' AND irec.rt_quantity < 0)
2057 OR (irec.transaction_type = 'RETURN TO RECEIVING')) THEN
2058 /* FP-J Lot/Serial Support Enhancement
2059 * If WMS and PO J are installed, then the current status for serials in
2060 * receiving should be "Resides in Receiving"
2061 * If WMS or PO patch levels are less than J, then retain the current
2062 * status of "Resides in Intransit"
2063 */
2064 IF (l_wms_po_j_higher) THEN
2065 l_status := 7;
2066 ELSE
2067 l_status := 5;
2068 END IF;
2069 UPDATE mtl_serial_numbers
2070 SET
2071 --current_status = 5
2072 current_status = l_status
2073 , current_subinventory_code = NULL
2074 , current_locator_id = NULL
2075 , last_txn_source_name = NULL
2076 , last_txn_source_id = NULL
2077 , group_mark_id = NULL
2078 , line_mark_id = NULL
2079 , cost_group_id = NULL
2080 WHERE serial_number = irec.serial_number
2081 AND inventory_item_id = irec.inventory_item_id
2082 AND exists (SELECT '1' FROM rcv_serials_supply rss
2083 WHERE rss.serial_num = serial_number
2084 AND rss.supply_type_code = 'RECEIVING');
2085 IF (l_debug = 1) THEN
2086 print_debug('TXN_COMPLETE: Deliver Transaction... updated serial... '||irec.serial_number);
2087 END IF;
2088 END IF;
2089 END LOOP;
2090 END IF; --IF (p_txn_mode <> 'LPN_GROUP') THEN
2091
2092
2093 -- End Changes for Bug 2490630
2094 IF (l_wms_po_j_higher = FALSE) THEN
2095 maintain_move_orders(
2096 p_group_id => p_group_id,
2097 x_return_status => x_return_status,
2098 x_msg_data => x_msg_data,
2099 x_msg_count => x_msg_count);
2100
2101 -- Need to clean up the Lot/Serial Temp tables.
2102 -- For Inventory destination txns, this would have already been
2103 -- done by Inventory Mgr., but we are checking that here
2104 -- as there is no harm in doing it again
2105
2106 -- Bug# 3281512 - Performance Fixes
2107 -- Open up a cursor to retrieve all of the interface_transaction_id
2108 -- values to avoid the hash join.
2109 OPEN c_interface_txn_id;
2110 LOOP
2111 FETCH c_interface_txn_id INTO l_interface_txn_id;
2112 EXIT WHEN c_interface_txn_id%NOTFOUND;
2113
2114 BEGIN
2115 DELETE FROM MTL_SERIAL_NUMBERS_TEMP
2116 WHERE TRANSACTION_TEMP_ID = l_interface_txn_id;
2117
2118 DELETE FROM MTL_TRANSACTION_LOTS_TEMP
2119 WHERE TRANSACTION_TEMP_ID = l_interface_txn_id;
2120
2121 EXCEPTION
2122 WHEN OTHERS THEN NULL;
2123 END;
2124 END LOOP;
2125 CLOSE c_interface_txn_id;
2126
2127 END IF; --IF (l_wms_po_j_higher = FALSE) THEN
2128
2129 ELSIF (p_txn_status = 'FALSE') THEN -- p_txn_status = 'FALSE'
2130
2131 IF (l_debug = 1) THEN
2132 print_debug('txn_complete- failure: Transaction failed and hence doing the following...');
2133 print_debug('txn_complete- failure: Unpack Contents/serials that were marked');
2134 print_debug('txn_complete- failure: Update Contents/Serials that were marked, erasing Source_Name');
2135 END IF;
2136
2137 /* For +ve correction on 'RECEIVE' and -ve correction on RTV/RTC, material gets into Receiving.
2138 ** So, contents/new Serials would have been created before calling transaction processor.
2139 ** Now unpacking them which in effect removes them
2140 */
2141 FOR i IN c_newly_packed LOOP
2142 PackUnpack_Container(
2143 x_return_status => x_return_status,
2144 x_msg_count => x_msg_count,
2145 x_msg_data => x_msg_data,
2146 p_lpn_id => i.lpn_id,
2147 p_content_item_id => i.inventory_item_id,
2148 p_revision => i.revision,
2149 p_lot_number => i.lot_number,
2150 p_from_serial_number => i.serial_number,
2151 p_to_serial_number => i.serial_number,
2152 p_quantity => i.quantity,
2153 p_uom => i.uom_code,
2154 p_organization_id => i.organization_id,
2155 p_subinventory => NULL,
2156 p_locator_id => NULL,
2157 p_operation => 2, -- unpack
2158 p_source_header_id => NULL,
2159 p_source_name => NULL,
2160 P_COST_GROUP_ID => NULL);
2161
2162 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2163 IF (l_debug = 1) THEN
2164 print_debug('Error in txn_complete while unpacking for txn failure');
2165 END IF;
2166 RAISE fnd_api.g_exc_error; --return; -- error while unpacking
2167 end if;
2168
2169 END LOOP;
2170
2171 /* In all other cases(other than the cases above), contents/new serials would be
2172 ** marked and hence remove the marks (source_name).
2173 ** Also, bring back the previous_status of the serial as the current status
2174 ** and set the lpn_txn_error_flag of the serial
2175 */
2176
2177 FOR i IN c_failure LOOP
2178
2179 UPDATE wms_lpn_contents
2180 SET source_name = NULL
2181 WHERE source_name = i.transaction_type
2182 AND source_header_id = i.interface_transaction_id;
2183
2184 -- Bug# 3281512 - Performance Fixes
2185 -- Also go against the inventory_item_id in the MSN table
2186 -- otherwise an index is not used and a full table scan will occur.
2187 -- Only update MSN if an item ID exists on the RTI record.
2188 IF (i.item_id IS NOT NULL) THEN
2189 UPDATE mtl_serial_numbers
2190 SET last_txn_source_name = NULL,
2191 current_status = nvl(previous_status,current_status),
2192 lpn_txn_error_flag = 'Y'
2193 WHERE last_txn_source_name = i.transaction_type
2194 AND last_txn_source_id = i.interface_transaction_id
2195 AND inventory_item_id = i.item_id;
2196 END IF;
2197
2198 END LOOP;
2199
2200
2201 IF (l_debug = 1) THEN
2202 print_debug('Exiting txn_complete- failure');
2203 END IF;
2204 END IF;
2205 IF (l_debug = 1) THEN
2206 print_debug('Exiting txn_complete');
2207 END IF;
2208
2209 EXCEPTION
2210 WHEN FND_API.g_exc_error THEN
2211 IF (l_debug = 1) THEN
2212 print_debug('txn_complete : execution error');
2213 END IF;
2214
2215 x_return_status := FND_API.G_RET_STS_ERROR;
2216 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2217 WHEN OTHERS THEN
2218 IF (l_debug = 1) THEN
2219 print_debug('In exception when others: ' || sqlerrm || ':' || sqlcode);
2220 END IF;
2221 x_return_status := FND_API.G_RET_STS_ERROR;
2222 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2223 x_msg_data := x_msg_data||sqlerrm;
2224 END txn_complete;
2225
2226 /* This function is called from LOV Cursor procedure 'GET_RETURN_LPN' of
2227 ** WMSLPNLB.pls to determine if the LPN is fully marked or partially marked.
2228 */
2229
2230 FUNCTION GET_LPN_MARKED_STATUS (p_lpn_id IN NUMBER, p_org_id IN NUMBER) RETURN VARCHAR2 IS
2231 /* Returns NONE or PARTIAL or FULL */
2232
2233 v_dummy VARCHAR2(1);
2234 v_is_marked boolean := FALSE;
2235 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2236 BEGIN
2237
2238 BEGIN
2239
2240 SELECT '1' INTO v_dummy FROM
2241 rcv_transactions_interface rti,wms_lpn_contents wlc
2242 WHERE rti.interface_transaction_id = wlc.source_header_id
2243 AND rti.processing_status_code = 'WSH_INTERFACED'
2244 AND rti.to_organization_id = wlc.organization_id
2245 AND wlc.parent_lpn_id = p_lpn_id
2246 AND ROWNUM < 2 ;
2247
2248
2249 IF (l_debug = 1) THEN
2250 print_debug('Lpn:' || p_lpn_id || 'Is Marked for Return Through Shipping RTV Project');
2251 END IF;
2252
2253 RETURN 'NONE';
2254
2255 EXCEPTION
2256 WHEN NO_DATA_FOUND THEN
2257
2258 IF (l_debug = 1) THEN
2259 print_debug('Lpn:' || p_lpn_id || 'Is Marked for Normal Return');
2260 END IF;
2261
2262 NULL;
2263
2264 END;
2265
2266
2267
2268
2269 BEGIN
2270
2271 SELECT '1' into v_dummy
2272 FROM mtl_serial_numbers
2273 WHERE lpn_id = p_lpn_id
2274 AND current_organization_id = p_org_id
2275 AND nvl(last_txn_source_name,'@@@') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2276 AND rownum <= 1;
2277
2278 v_is_marked := TRUE;
2279
2280 EXCEPTION
2281 WHEN NO_DATA_FOUND THEN
2282 NULL;
2283 END;
2284
2285 if not v_is_marked then
2286
2287 BEGIN
2288
2289 SELECT '1' INTO v_dummy
2290 FROM wms_lpn_contents
2291 WHERE nvl(serial_summary_entry,2) <> 1
2292 AND parent_lpn_id = p_lpn_id
2293 AND organization_id = p_org_id
2294 AND nvl(source_name,'@@@') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2295 AND rownum <= 1;
2296
2297 v_is_marked := TRUE;
2298
2299 EXCEPTION
2300 WHEN NO_DATA_FOUND THEN
2301 null;
2302 END;
2303
2304 end if;
2305
2306 if not v_is_marked then
2307 RETURN 'NONE';
2308 end if;
2309
2310 BEGIN
2311
2312 SELECT '1' into v_dummy
2313 FROM mtl_serial_numbers
2314 WHERE lpn_id = p_lpn_id
2315 AND current_organization_id = p_org_id
2316 AND nvl(last_txn_source_name,'@@@') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2317 AND rownum <= 1;
2318
2319 IF (l_debug = 1) THEN
2320 print_debug('Lpn:' || p_lpn_id || ' having serial items is marked partially');
2321 END IF;
2322 return 'PARTIAL';
2323
2324 EXCEPTION
2325 WHEN NO_DATA_FOUND THEN
2326 NULL;
2327 END;
2328
2329 BEGIN
2330
2331 SELECT '1' INTO v_dummy
2332 FROM wms_lpn_contents
2333 WHERE nvl(serial_summary_entry,2) <> 1
2334 AND parent_lpn_id = p_lpn_id
2335 AND ORGANIZATION_ID = p_org_id
2336 AND nvl(source_name,'@@@') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2337 AND rownum <= 1;
2338
2339 IF (l_debug = 1) THEN
2340 print_debug('Lpn:' || p_lpn_id || 'having non serial items is marked partially');
2341 END IF;
2342 return 'PARTIAL';
2343
2344 EXCEPTION
2345 WHEN NO_DATA_FOUND THEN
2346 null;
2347 END;
2348 IF (l_debug = 1) THEN
2349 print_debug('Lpn:' || p_lpn_id || ', entire lpn is marked for return');
2350 END IF;
2351 return 'FULL';
2352 EXCEPTION
2353 WHEN OTHERS THEN
2354 return 'PARTIAL';
2355 END;
2356
2357 /*
2358 ** This Procedure is called from the Returns Form to Mark the LPN Contents
2359 ** that are selected for return.
2360 */
2361
2362
2363 PROCEDURE MARK_RETURNS (
2364 x_return_status OUT NOCOPY VARCHAR2,
2365 x_msg_count OUT NOCOPY NUMBER,
2366 x_msg_data OUT NOCOPY VARCHAR2,
2367 p_rcv_trx_interface_id IN NUMBER,
2368 p_ret_transaction_type IN VARCHAR2,
2369 p_lpn_id IN NUMBER,
2370 p_item_id IN NUMBER,
2371 p_item_revision IN VARCHAR2,
2372 p_quantity IN NUMBER,
2373 p_uom IN VARCHAR2,
2374 p_serial_controlled IN NUMBER,
2375 p_lot_controlled IN NUMBER,
2376 p_org_id IN NUMBER,
2377 p_subinventory IN VARCHAR2,
2378 p_locator_id IN NUMBER
2379 ) IS
2380
2381 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2382 l_lot_number VARCHAR2(80);
2383 l_from_serial_number VARCHAR2(50);
2384 l_to_serial_number VARCHAR2(50);
2385 l_quantity number;
2386 l_lpn_context NUMBER := 0;
2387 l_cost_group_id number;
2388 TYPE c_ref_type IS REF CURSOR;
2389 c_ref c_ref_type;
2390 l_position VARCHAR2(4) := '0000';
2391 l_primary_uom VARCHAR2(10);
2392 l_uom VARCHAR2(3);--BUG 4939647: For non-serial controlled item, always pass the txn uom
2393
2394 -- bug 4411792
2395 l_lpn_update WMS_CONTAINER_PUB.LPN;
2396 l_return_status VARCHAR2(1);
2397 l_msg_count NUMBER;
2398 l_msg_data VARCHAR2(2000);
2399
2400 l_sec_qty NUMBER; -- bug 13399743
2401
2402 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2403 BEGIN
2404
2405 l_position := '0010';
2406 x_return_status := FND_API.G_RET_STS_SUCCESS;
2407 IF (l_debug = 1) THEN
2408 print_debug('Enter MARK_RETURNS');
2409 print_debug('p_serial_controlled => ' || p_serial_controlled);
2410 print_debug('p_lot_controlled => ' || p_lot_controlled);
2411 END IF;
2412
2413 BEGIN
2414 SELECT lpn_context
2415 INTO l_lpn_context
2416 FROM wms_license_plate_numbers
2417 WHERE organization_id = p_org_id
2418 AND lpn_id = p_lpn_id;
2419 EXCEPTION
2420 WHEN no_data_found THEN
2421 NULL;
2422 END;
2423
2424
2425 --Get primary UOM - for bug fix 3609203
2426 BEGIN
2427 SELECT primary_uom_code
2428 INTO l_primary_uom
2429 FROM mtl_system_items
2430 WHERE inventory_item_id = p_item_id
2431 AND organization_id = p_org_id ;
2432
2433 END; --get primary uom
2434
2435
2436 IF p_lot_controlled = 2 AND p_serial_controlled in (2,5) THEN
2437
2438 /* Lot/Serial Controlled */
2439 l_position := '0020';
2440
2441 open c_ref for SELECT msn.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2442 MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID, null sec_qty--13399743
2443 FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT
2444 WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2445 AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
2446 AND MSN.INVENTORY_ITEM_ID = p_item_id
2447 AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2448 AND MSN.LOT_NUMBER = MTLT.LOT_NUMBER
2449 AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
2450 AND MSN.SERIAL_NUMBER <= MSNT.TO_SERIAL_NUMBER
2451
2452 UNION --RTV Change 16197273
2453
2454 SELECT msn.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2455 MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID, null sec_qty--13399743
2456 FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_INTERFACE
2457 MSNI, mtl_transaction_lots_interface MTLI
2458 WHERE MTLI.PRODUCT_TRANSACTION_ID = p_rcv_trx_interface_id
2459 AND MSNI.PRODUCT_TRANSACTION_ID = p_rcv_trx_interface_id
2460 AND MSN.INVENTORY_ITEM_ID = p_item_id
2461 AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2462 AND MSN.LOT_NUMBER = MTLI.LOT_NUMBER
2463 AND MSN.SERIAL_NUMBER >= MSNI.FM_SERIAL_NUMBER
2464 AND MSN.SERIAL_NUMBER <= MSNI.TO_SERIAL_NUMBER;
2465
2466 -- AND length(MSN.SERIAL_NUMBER) = length(MSNT.FM_SERIAL_NUMBER);
2467 -- It is not possible to use length function here because table MTL_TRANSACTION_LOTS_TEMP
2468 -- has a field 'length' and it would result in a compilation error if function length
2469 -- is used.
2470
2471 l_uom := l_primary_uom;
2472 ELSIF p_lot_controlled = 2 AND p_serial_controlled not in (2,5) THEN
2473
2474 /* Lot Controlled */
2475 l_position := '0030';
2476
2477 /* For a given LPN, Lot and Item combination there could be more
2478 ** than one record in wms_lpn_contents, but we need
2479 ** only one record per LPN, Lot and Item combination as an output of this cursor
2480 */
2481
2482 open c_ref for SELECT DISTINCT MTLT.LOT_NUMBER, NULL FM_SERIAL_NUMBER,
2483 NULL TO_SERIAL_NUMBER, MTLT.transaction_quantity quantity,wlpnc.cost_group_id, MTLT.secondary_quantity sec_qty--13399743
2484 FROM WMS_LPN_CONTENTS WLPNC, MTL_TRANSACTION_LOTS_TEMP MTLT
2485 WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2486 AND WLPNC.LOT_NUMBER = MTLT.LOT_NUMBER
2487 AND WLPNC.PARENT_LPN_ID = p_lpn_id
2488 AND WLPNC.INVENTORY_ITEM_ID = P_ITEM_ID
2489 AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER')
2490
2491 UNION -- RTV Change 16197273
2492
2493 SELECT DISTINCT MTLI.LOT_NUMBER, NULL FM_SERIAL_NUMBER,
2494 NULL TO_SERIAL_NUMBER, MTLI.transaction_quantity quantity,wlpnc.cost_group_id, MTLI.SECONDARY_TRANSACTION_QUANTITY sec_qty--13399743
2495 FROM WMS_LPN_CONTENTS WLPNC, mtl_transaction_lots_interface MTLI
2496 WHERE MTLI.PRODUCT_TRANSACTION_ID = p_rcv_trx_interface_id
2497 AND WLPNC.LOT_NUMBER = MTLI.LOT_NUMBER
2498 AND WLPNC.PARENT_LPN_ID = p_lpn_id
2499 AND WLPNC.INVENTORY_ITEM_ID = P_ITEM_ID
2500 AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER') ;
2501
2502 l_uom := p_uom;
2503 ELSIF p_lot_controlled <> 2 AND p_serial_controlled in (2,5) THEN
2504
2505 /* Serial Controlled */
2506 l_position := '0040';
2507 open c_ref for SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2508 MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID, null sec_qty --13399743
2509 FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP msnt,
2510 wms_lpn_contents wlpnc
2511 WHERE MSNT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2512 AND MSN.INVENTORY_ITEM_ID = p_item_id
2513 AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2514 AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
2515 AND MSN.SERIAL_NUMBER <= MSNT.to_serial_number
2516 AND msn.lpn_id = wlpnc.parent_lpn_id
2517 AND wlpnc.parent_lpn_id = p_lpn_id
2518 AND wlpnc.inventory_item_id = msn.inventory_item_id
2519 AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER')
2520 AND length(MSN.SERIAL_NUMBER) = length(MSNT.FM_SERIAL_NUMBER)
2521
2522 UNION --RTV Change 16197273
2523
2524 SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2525 MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID, null sec_qty --13399743
2526 FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_INTERFACE MSNI,
2527 wms_lpn_contents wlpnc
2528 WHERE MSNI.PRODUCT_TRANSACTION_ID = p_rcv_trx_interface_id
2529 AND MSN.INVENTORY_ITEM_ID = p_item_id
2530 AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2531 AND MSN.SERIAL_NUMBER >= MSNI.FM_SERIAL_NUMBER
2532 AND MSN.SERIAL_NUMBER <= MSNI.to_serial_number
2533 AND msn.lpn_id = wlpnc.parent_lpn_id
2534 AND wlpnc.parent_lpn_id = p_lpn_id
2535 AND wlpnc.inventory_item_id = msn.inventory_item_id
2536 AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER')
2537 AND length(MSN.SERIAL_NUMBER) = length(MSNI.FM_SERIAL_NUMBER);
2538
2539
2540 l_uom := l_primary_uom;
2541 ELSE
2542
2543 l_position := '0050';
2544 open c_ref for SELECT NULL , NULL , NULL , rti.quantity, wlpnc.cost_group_id, RTI.secondary_quantity sec_qty--13399743
2545 FROM WMS_LPN_CONTENTS WLPNC, RCV_TRANSACTIONS_INTERFACE RTI
2546 WHERE RTI.INTERFACE_TRANSACTION_ID = p_rcv_trx_interface_id
2547 AND WLPNC.PARENT_LPN_ID = p_lpn_id
2548 AND WLPNC.INVENTORY_ITEM_ID = RTI.ITEM_ID
2549 AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER')
2550 AND rownum <= 1;
2551
2552 /* For a given LPN and Item combination there could be more
2553 ** than one record in wms_lpn_contents, but we need
2554 ** only one record as an output of this cursor
2555 */
2556 l_uom := p_uom;
2557 END IF;
2558 l_position := '0060';
2559
2560 LOOP
2561 l_position := '0070';
2562
2563 FETCH c_ref into l_lot_number, l_from_serial_number, l_to_serial_number, l_quantity, l_cost_group_id, l_sec_qty; --13399743
2564 l_position := '0080';
2565
2566
2567 IF c_ref%NOTFOUND THEN
2568 IF (l_debug = 1) THEN
2569 print_debug('Contents not found');
2570 END IF;
2571 EXIT;
2572 END IF;
2573 l_position := '0090';
2574
2575 PackUnpack_Container(
2576 x_return_status => x_return_status,
2577 x_msg_count => x_msg_count,
2578 x_msg_data => x_msg_data,
2579 p_lpn_id => p_lpn_id,
2580 p_content_item_id => p_item_id,
2581 p_revision => p_item_revision,
2582 p_lot_number => l_lot_number,
2583 p_from_serial_number => l_from_serial_number,
2584 p_to_serial_number => l_to_serial_number,
2585 p_quantity => abs(l_quantity),
2586 p_uom => l_uom,--BUG 4939647: For non-serial controlled item,always pass the txn uom
2587 p_organization_id => p_org_id,
2588 p_subinventory => p_subinventory,
2589 p_locator_id => p_locator_id,
2590 p_operation => 2, -- unpack
2591 p_source_header_id => NULL,
2592 p_source_name => NULL,
2593 P_COST_GROUP_ID => l_cost_group_id,
2594 p_secondary_quantity => abs(l_sec_qty) --13399743
2595 );
2596 l_position := '0100';
2597
2598 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2599 IF (l_debug = 1) THEN
2600 print_debug('Error in mark_returns while unpacking');
2601 END IF;
2602 RAISE fnd_api.g_exc_error; --return; -- error while unpacking
2603 end if;
2604
2605 PackUnpack_Container(
2606 x_return_status => x_return_status,
2607 x_msg_count => x_msg_count,
2608 x_msg_data => x_msg_data,
2609 p_lpn_id => p_lpn_id,
2610 p_content_item_id => p_item_id,
2611 p_revision => p_item_revision ,
2612 p_lot_number => l_lot_number,
2613 p_from_serial_number => l_from_serial_number,
2614 p_to_serial_number => l_to_serial_number,
2615 p_quantity => abs(l_quantity),
2616 p_uom => l_uom,--R12: For non-serial controlled item,always pass the txn uom
2617 p_organization_id => p_org_id,
2618 p_subinventory => p_subinventory,
2619 p_locator_id => p_locator_id,
2620 p_operation => 1, -- pack
2621 p_source_header_id => p_rcv_trx_interface_id,
2622 p_source_name => p_ret_transaction_type,
2623 P_COST_GROUP_ID => l_cost_group_id,
2624 p_secondary_quantity => abs(l_sec_qty) --13399743
2625 );
2626
2627 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2628 IF (l_debug = 1) THEN
2629 print_debug('Error in mark_returns while packing');
2630 END IF;
2631 RAISE fnd_api.g_exc_error; --return; -- error while packing
2632 end if;
2633
2634 END LOOP;
2635
2636 IF (l_lpn_context <> 0) THEN
2637
2638 -- 4411792 Diecrt update to wlpn is replaced by the below call
2639
2640 l_lpn_update.lpn_id := p_lpn_id ;
2641 l_lpn_update.organization_id := p_org_id ;
2642 l_lpn_update.lpn_context := l_lpn_context ;
2643
2644 wms_container_pvt.Modify_LPN
2645 (
2646 p_api_version => 1.0
2647 , p_validation_level => fnd_api.g_valid_level_none
2648 , x_return_status => l_return_status
2649 , x_msg_count => l_msg_count
2650 , x_msg_data => l_msg_data
2651 , p_lpn => l_lpn_update
2652 ) ;
2653
2654 l_lpn_update := NULL;
2655
2656 -- Bug 4411792
2657 --UPDATE wms_license_plate_numbers
2658 --SET lpn_context = l_lpn_context
2659 --WHERE organization_id = p_org_id
2660 --AND lpn_id = p_lpn_id;
2661 END IF;
2662
2663 UPDATE mtl_txn_request_lines
2664 SET wms_process_flag = 2
2665 , txn_source_line_detail_id = p_rcv_trx_interface_id
2666 WHERE lpn_id = p_lpn_id;
2667
2668 l_position := '0200';
2669 IF (l_debug = 1) THEN
2670 print_debug('Exit MARK_RETURNS');
2671 END IF;
2672
2673 EXCEPTION
2674 WHEN FND_API.g_exc_error THEN
2675 IF (l_debug = 1) THEN
2676 print_debug('mark_returns : execution error');
2677 END IF;
2678
2679 x_return_status := FND_API.G_RET_STS_ERROR;
2680 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2681 when others then
2682 IF (l_debug = 1) THEN
2683 print_debug('Error(' || l_position || '):' || sqlerrm);
2684 END IF;
2685 x_return_status := FND_API.G_RET_STS_ERROR;
2686 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2687 x_msg_data := x_msg_data||sqlerrm;
2688 END MARK_RETURNS;
2689
2690 /*
2691 --16197273
2692 --Description:API to unmark the wms_lpn_contents table at the time of processing new rti/mti.
2693 --This api will be called from RTV specific package :RCVWSHIB.pls
2694 */
2695
2696 PROCEDURE unmark_returns (
2697 x_return_status OUT NOCOPY VARCHAR2,
2698 x_msg_count OUT NOCOPY NUMBER,
2699 x_msg_data OUT NOCOPY VARCHAR2,
2700 p_rcv_trx_interface_id IN NUMBER,
2701 p_ret_transaction_type IN VARCHAR2,
2702 p_lpn_id IN NUMBER,
2703 p_item_id IN NUMBER,
2704 p_item_revision IN VARCHAR2,
2705 p_org_id IN NUMBER,
2706 p_lot_number IN VARCHAR2 )
2707
2708
2709 IS
2710
2711 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2712 l_position VARCHAR2(4) := '0000';
2713
2714 CURSOR c_unmark_wlc
2715 IS
2716 SELECT LPN_CONTENT_ID
2717 FROM wms_lpn_contents
2718 WHERE parent_lpn_id = p_lpn_id
2719 AND inventory_item_id = p_item_id
2720 AND organization_id = p_org_id
2721 AND NVL(lot_number, '@@@') = NVL(p_lot_number,'@@@')
2722 AND NVL(revision, '@@@') = NVL(p_item_revision,'@@@')
2723 AND source_header_id = p_rcv_trx_interface_id
2724 AND source_name = p_ret_transaction_type;
2725
2726 TYPE m_lpn_content_id IS TABLE OF c_unmark_wlc%ROWTYPE;
2727 l_lpn_content_id m_lpn_content_id;
2728
2729
2730 BEGIN
2731
2732 l_position := '0010';
2733 x_return_status := FND_API.G_RET_STS_SUCCESS;
2734
2735 IF (l_debug = 1) THEN
2736 print_debug('RTV ER:Inside Unmark API ' || p_rcv_trx_interface_id );
2737 print_debug('RTV ER:Inside Unmark API ' || p_lpn_id );
2738
2739 END if ;
2740
2741
2742 OPEN c_unmark_wlc ;
2743
2744 FETCH c_unmark_wlc BULK COLLECT INTO l_lpn_content_id ;
2745
2746 CLOSE c_unmark_wlc;
2747
2748
2749 FOR i IN 1..l_lpn_content_id.COUNT LOOP
2750
2751
2752 UPDATE wms_lpn_contents
2753 SET source_header_id = NULL,
2754 source_name = NULL
2755 WHERE LPN_CONTENT_ID = l_lpn_content_id(i).LPN_CONTENT_ID ;
2756
2757 l_position := '0020' ;
2758
2759 END LOOP;
2760
2761 IF (l_debug = 1) THEN
2762
2763 print_debug('RTV ER:After Unmark API for ' || p_lpn_id );
2764
2765 END if ;
2766
2767
2768
2769 l_position := '0030';
2770 IF (l_debug = 1) THEN
2771 print_debug('Exit UNMARK_RETURNS');
2772 END IF;
2773
2774 EXCEPTION
2775
2776 when others then
2777 IF (l_debug = 1) THEN
2778 l_position := '0040';
2779 print_debug('Error(' || l_position || '):' || sqlerrm);
2780 END IF;
2781 x_return_status := FND_API.G_RET_STS_ERROR;
2782 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
2783 x_msg_data := x_msg_data||sqlerrm;
2784
2785 END unmark_returns;
2786
2787 /*
2788 --16197273
2789 --Description:API to create container WDD and WDA for Return order.
2790 --This api will be called from RTV specific package :RCVWSHIB.pls
2791
2792 */
2793
2794 PROCEDURE Create_Update_Containers_RTV (
2795 x_return_status OUT NOCOPY VARCHAR2
2796 ,x_msg_count OUT NOCOPY NUMBER
2797 , x_msg_data OUT NOCOPY VARCHAR2
2798 , p_interface_txn_id IN NUMBER
2799 , p_wdd_table WSH_GLBL_VAR_STRCT_GRP.delivery_details_Attr_tbl_Type
2800
2801 )IS
2802
2803
2804 CURSOR c_lpn_exist (v_lpn_id NUMBER) IS
2805 SELECT 1
2806 FROM wsh_delivery_details
2807 WHERE lpn_id = v_lpn_id
2808 AND released_status = 'X'
2809 and rownum <2;
2810
2811
2812 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2813 l_position VARCHAR2(4) := '0001';
2814 wsh_create_tbl WSH_Glbl_Var_Strct_GRP.delivery_details_Attr_tbl_Type;
2815 l_IN_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
2816 l_OUT_rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
2817 l_lpn_id NUMBER ;
2818 l_wdd_exists NUMBER ;
2819 l_return_status VARCHAR2(1);
2820 l_shipping_attr wsh_interface.changedattributetabtype;
2821 l_wsh_dd_rec WSH_Glbl_Var_Strct_GRP.Delivery_Details_Rec_Type;
2822 l_lpn_attr WMS_Data_Type_Definitions_PUB.LPNRecordType;
2823
2824
2825
2826
2827 BEGIN
2828
2829 l_position := '0010';
2830 x_return_status := FND_API.G_RET_STS_SUCCESS;
2831
2832 SELECT transfer_lpn_id INTO l_lpn_id FROM rcv_transactions_interface
2833 WHERE interface_transaction_id = p_interface_txn_id;
2834
2835 OPEN c_lpn_exist(l_lpn_id);
2836 FETCH c_lpn_exist INTO l_wdd_exists;
2837 CLOSE c_lpn_exist;
2838
2839 IF l_wdd_exists = 1 THEN
2840
2841 print_debug('Container WDD already exist for this lpn_id');
2842
2843
2844 ELSE
2845
2846 BEGIN
2847 SELECT lpn_id
2848 , license_plate_number
2849 , parent_lpn_id
2850 , outermost_lpn_id
2851 , lpn_context
2852 , organization_id
2853 , subinventory_code
2854 , locator_id
2855 , inventory_item_id
2856 , revision
2857 , lot_number
2858 , serial_number
2859 , cost_group_id
2860 , tare_weight_uom_code
2861 , tare_weight
2862 , gross_weight_uom_code
2863 , gross_weight
2864 , container_volume_uom
2865 , container_volume
2866 , content_volume_uom_code
2867 , content_volume
2868 , source_type_id
2869 , source_header_id
2870 , source_line_id
2871 , source_line_detail_id
2872 , source_name
2873 , attribute_category
2874 , attribute1
2875 , attribute2
2876 , attribute3
2877 , attribute4
2878 , attribute5
2879 , attribute6
2880 , attribute7
2881 , attribute8
2882 , attribute9
2883 , attribute10
2884 , attribute11
2885 , attribute12
2886 , attribute13
2887 , attribute14
2888 , attribute15
2889 INTO l_lpn_attr.lpn_id
2890 , l_lpn_attr.license_plate_number
2891 , l_lpn_attr.parent_lpn_id
2892 , l_lpn_attr.outermost_lpn_id
2893 , l_lpn_attr.lpn_context
2894 , l_lpn_attr.organization_id
2895 , l_lpn_attr.subinventory_code
2896 , l_lpn_attr.locator_id
2897 , l_lpn_attr.inventory_item_id
2898 , l_lpn_attr.revision
2899 , l_lpn_attr.lot_number
2900 , l_lpn_attr.serial_number
2901 , l_lpn_attr.cost_group_id
2902 , l_lpn_attr.tare_weight_uom_code
2903 , l_lpn_attr.tare_weight
2904 , l_lpn_attr.gross_weight_uom_code
2905 , l_lpn_attr.gross_weight
2906 , l_lpn_attr.container_volume_uom
2907 , l_lpn_attr.container_volume
2908 , l_lpn_attr.content_volume_uom_code
2909 , l_lpn_attr.content_volume
2910 , l_lpn_attr.source_type_id
2911 , l_lpn_attr.source_header_id
2912 , l_lpn_attr.source_line_id
2913 , l_lpn_attr.source_line_detail_id
2914 , l_lpn_attr.source_name
2915 , l_lpn_attr.attribute_category
2916 , l_lpn_attr.attribute1
2917 , l_lpn_attr.attribute2
2918 , l_lpn_attr.attribute3
2919 , l_lpn_attr.attribute4
2920 , l_lpn_attr.attribute5
2921 , l_lpn_attr.attribute6
2922 , l_lpn_attr.attribute7
2923 , l_lpn_attr.attribute8
2924 , l_lpn_attr.attribute9
2925 , l_lpn_attr.attribute10
2926 , l_lpn_attr.attribute11
2927 , l_lpn_attr.attribute12
2928 , l_lpn_attr.attribute13
2929 , l_lpn_attr.attribute14
2930 , l_lpn_attr.attribute15
2931 FROM wms_license_plate_numbers
2932 WHERE lpn_id = l_lpn_id;
2933 EXCEPTION
2934 WHEN NO_DATA_FOUND THEN
2935 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
2936 fnd_msg_pub.ADD;
2937 RAISE fnd_api.g_exc_error;
2938 END;
2939
2940
2941
2942 wsh_create_tbl(1) := wms_container_pvt.To_DeliveryDetailsRecType(l_lpn_attr);
2943
2944
2945 IF ( wsh_create_tbl.last > 0 ) THEN
2946 IF (l_debug = 1) THEN
2947 print_debug('Calling WSH API to creat Container WDD');
2948 END IF;
2949
2950 l_IN_rec.caller := 'WMS';
2951 l_IN_rec.action_code := 'CREATE';
2952
2953 WSH_WMS_LPN_GRP.Create_Update_Containers (
2954 p_api_version => 1.0
2955 , p_init_msg_list => fnd_api.g_false
2956 , p_commit => fnd_api.g_false
2957 , x_return_status => x_return_status
2958 , x_msg_count => x_msg_count
2959 , x_msg_data => x_msg_data
2960 , p_detail_info_tab => wsh_create_tbl
2961 , p_IN_rec => l_IN_rec
2962 , x_OUT_rec => l_OUT_rec );
2963
2964
2965 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
2966 IF (l_debug = 1) THEN
2967 print_debug('Create_Update_Containers Failed');
2968 END IF ;
2969
2970 END IF;
2971 -- Once Create is done need to clear table
2972 wsh_create_tbl.delete;
2973 END IF;
2974
2975 END IF ;
2976
2977 --Calling update shipping attributes
2978
2979 IF (l_debug = 1) THEN
2980 print_debug('Calling update shipping attributes for all content WDD');
2981 END IF ;
2982
2983
2984
2985 FOR i IN 1 .. p_wdd_table.Count LOOP
2986
2987
2988 IF (l_debug = 1) THEN
2989 print_debug('after update shipping attributes for WDD:delivery_detail_id' || p_wdd_table(i).delivery_detail_id);
2990 print_debug('after update shipping attributes for WDD:source_header_id' || p_wdd_table(i).source_header_id);
2991 print_debug('after update shipping attributes for WDD:source_line_id' || p_wdd_table(i).source_line_id);
2992 print_debug('after update shipping attributes for WDD:organization_id' || p_wdd_table(i).organization_id);
2993 print_debug('after update shipping attributes for WDD:subinventory' || p_wdd_table(i).subinventory);
2994 print_debug('after update shipping attributes for WDD:revision' || p_wdd_table(i).revision);
2995 print_debug('after update shipping attributes for WDD:locator_id' || p_wdd_table(i).locator_id);
2996 print_debug('after update shipping attributes for WDD:lot_number' || p_wdd_table(i).lot_number);
2997
2998 END IF;
2999
3000 l_shipping_attr(i).source_header_id := p_wdd_table(i).source_header_id;
3001 l_shipping_attr(i).source_line_id := p_wdd_table(i).source_line_id;
3002 l_shipping_attr(i).ship_from_org_id := p_wdd_table(i).organization_id;
3003 l_shipping_attr(i).subinventory := p_wdd_table(i).subinventory;
3004 l_shipping_attr(i).revision := p_wdd_table(i).revision;
3005 l_shipping_attr(i).locator_id := p_wdd_table(i).locator_id;
3006 l_shipping_attr(i).released_status := 'X';
3007 l_shipping_attr(i).delivery_detail_id := p_wdd_table(i).delivery_detail_id;
3008 --l_shipping_attr(1).serial_number := l_serial_number;
3009 l_shipping_attr(i).lot_number := p_wdd_table(i).lot_number;
3010 l_shipping_attr(i).transfer_lpn_id := l_lpn_id ;
3011
3012 wsh_interface.update_shipping_attributes(
3013 p_source_code => 'INV',
3014 p_changed_attributes => l_shipping_attr,
3015 x_return_status => l_return_status);
3016
3017
3018
3019 END LOOP ;
3020
3021
3022 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3023 IF (l_debug = 1) THEN
3024 print_debug('return error from update shipping attributes');
3025 END IF;
3026 RAISE fnd_api.g_exc_error;
3027 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3028 IF (l_debug = 1) THEN
3029 print_debug('return error from update shipping attributes');
3030 END IF;
3031 RAISE fnd_api.g_exc_unexpected_error;
3032 END IF;
3033
3034
3035 EXCEPTION
3036 WHEN OTHERS THEN
3037 IF (l_debug = 1) THEN
3038 l_position := '0040';
3039 print_debug('Error(' || l_position || '):' || sqlerrm);
3040 END IF;
3041 x_return_status := FND_API.G_RET_STS_ERROR;
3042 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3043 x_msg_data := x_msg_data||sqlerrm;
3044
3045 END Create_Update_Containers_RTV;
3046
3047 /*
3048 --16197273
3049 --Description;API created to do post TM updates from WMS side .
3050
3051 */
3052
3053 PROCEDURE perform_post_TM_wms_updates (
3054 x_return_status OUT NOCOPY VARCHAR2
3055 ,x_msg_count OUT NOCOPY NUMBER
3056 , x_msg_data OUT NOCOPY VARCHAR2
3057 ,p_rcv_trx_interface_id IN NUMBER
3058 ,p_ship_flag IN varchar2)
3059
3060 IS
3061
3062 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3063 l_position VARCHAR2(4) := '0000';
3064
3065 l_lpn_id NUMBER ;
3066 v_dummy NUMBER ;
3067
3068
3069 BEGIN
3070
3071
3072 l_position := '0011';
3073 x_return_status := FND_API.G_RET_STS_SUCCESS;
3074
3075 IF (l_debug = 1) THEN
3076 print_debug('RTV ER: perform_post_TM_wms_updates ::' || p_rcv_trx_interface_id );
3077 END IF;
3078
3079
3080 IF (p_ship_flag = 'PARTIAL') THEN
3081
3082 BEGIN
3083
3084 SELECT 1 INTO v_dummy
3085 FROM rcv_transactions_interface rti,wms_license_plate_numbers wlpn
3086 WHERE rti.interface_transaction_id = p_rcv_trx_interface_id
3087 AND rti.processing_status_code = 'WSH_INTERFACED'
3088 AND rti.transfer_lpn_id = wlpn.lpn_id
3089 AND wlpn.lpn_context = 5
3090 AND rti.to_organization_id = wlpn.organization_id ;
3091
3092
3093 IF (l_debug = 1) THEN
3094 print_debug('Master LPN is shipped.' );
3095 END IF;
3096
3097
3098 SELECT DISTINCT parent_lpn_id INTO l_lpn_id FROM wms_lpn_contents WHERE
3099 source_header_id = p_rcv_trx_interface_id
3100 AND source_name = 'RETURN TO VENDOR'
3101 AND ROWNUM < 2 ;
3102
3103 IF (l_debug = 1) THEN
3104 print_debug('Before updating master RTI for the remaining qty.' );
3105 END IF;
3106
3107 l_position := '0012';
3108
3109 UPDATE rcv_transactions_interface SET transfer_lpn_id = l_lpn_id
3110 WHERE
3111 interface_transaction_id = p_rcv_trx_interface_id
3112 AND processing_status_code = 'WSH_INTERFACED' ;
3113
3114 EXCEPTION
3115 WHEN NO_DATA_FOUND THEN
3116 IF (l_debug = 1) THEN
3117 print_debug('Master LPN not shipped.' );
3118 END IF;
3119
3120 NULL;
3121
3122 END;
3123
3124 END IF ;
3125
3126 EXCEPTION
3127 WHEN OTHERS THEN
3128
3129 IF (l_debug = 1) THEN
3130 l_position := '0013';
3131 print_debug('Error(' || l_position || '):' || sqlerrm);
3132 END IF;
3133 x_return_status := FND_API.G_RET_STS_ERROR;
3134 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3135 x_msg_data := x_msg_data||sqlerrm;
3136
3137
3138
3139 END perform_post_TM_wms_updates ;
3140
3141
3142
3143 /*
3144 --16514543
3145 --Description:Chnages done to Unpack qty from LPN ,in case of Partial Cancelation of Return Order.
3146 --This api will be called from Procedure:cancel_return_order_RTV
3147 */
3148
3149 PROCEDURE callUnpack(
3150 x_return_status OUT NOCOPY VARCHAR2 ,
3151 x_msg_count OUT NOCOPY NUMBER ,
3152 x_msg_data OUT NOCOPY VARCHAR2 ,
3153 p_rtv_order IN NUMBER ,
3154 p_lpn_id IN NUMBER ,
3155 p_trx_header_id NUMBER )
3156 IS
3157 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3158 l_position VARCHAR2(4) := '0000';
3159 l_message VARCHAR2(2000);
3160 l_trx_tmp_id NUMBER;
3161 l_lpn_id NUMBER;
3162 l_transfer_lpn_id NUMBER;
3163 l_org_id NUMBER;
3164 l_subinventory VARCHAR2(30);
3165 l_xfr_org_id NUMBER;
3166 l_locator_id NUMBER;
3167 l_trx_qty NUMBER;
3168 l_pri_qty NUMBER;
3169 l_sectrxqty NUMBER;
3170 l_item_id NUMBER;
3171 l_revision VARCHAR2(25);
3172 l_trx_uom_code VARCHAR2(30);
3173 l_trx_src_type_id NUMBER;
3174 l_user_id NUMBER;
3175 l_secondary_unit_of_measure VARCHAR2(25);
3176 l_msg_data VARCHAR2(2000);
3177 l_lot_number VARCHAR2(80);
3178 l_lot_expiration_date DATE;
3179 l_ser_trx_id NUMBER;
3180 l_lot_control_code NUMBER;
3181 l_serial_control_code NUMBER;
3182 l_serial_number_from VARCHAR2(30) := NULL;
3183 l_serial_number_to VARCHAR2(30) := NULL;
3184 l_interface_txn_id NUMBER;
3185 l_trx_header_id NUMBER ;
3186 l_trx_action_id NUMBER ;
3187 l_trx_type_id NUMBER ;
3188 l_return_status NUMBER ;
3189 CURSOR c_unpack_lpn
3190 IS
3191 SELECT wlc.LPN_CONTENT_ID lpn_content_id ,
3192 wlpn.lpn_id lpn_id ,
3193 wlpn. SUBINVENTORY_CODE subinventory_code ,
3194 wlpn.LOCATOR_ID locator_id ,
3195 wlpn.ORGANIZATION_ID org_id ,
3196 wlc.INVENTORY_ITEM_ID inventory_item_id ,
3197 wlc.REVISION revision ,
3198 wlc.LOT_NUMBER lot_number ,
3199 wlc.QUANTITY quantity ,
3200 wlc.UOM_CODE uom_code ,
3201 wlc.PRIMARY_QUANTITY primary_qty ,
3202 wlc.SECONDARY_QUANTITY sec_qty ,
3203 wlc.SECONDARY_UOM_CODE sec_uom ,
3204 wlc.SERIAL_SUMMARY_ENTRY serial_control
3205 FROM wms_lpn_contents wlc,
3206 wms_license_plate_numbers wlpn
3207 WHERE wlc.parent_lpn_id = wlpn.lpn_id
3208 AND wlpn.lpn_id = p_lpn_id
3209 AND wlc.source_header_id IS NULL ;
3210 l_unpack_rec c_unpack_lpn%ROWTYPE ;
3211 CURSOR c_mtlt(p_lot_number VARCHAR2,p_organization_id NUMBER )
3212 IS
3213 SELECT expiration_date,
3214 origination_date
3215 FROM mtl_lot_numbers
3216 WHERE LOT_NUMBER = p_lot_number
3217 AND ORGANIZATION_ID = p_organization_id ;
3218 CURSOR c_msnt(p_lpn_id NUMBER,p_organization_id NUMBER,p_item_id NUMBER )
3219 IS
3220 SELECT serial_number
3221 FROM mtl_serial_numbers
3222 WHERE lpn_id = p_lpn_id
3223 AND inventory_item_id = p_item_id
3224 AND CURRENT_ORGANIZATION_ID = p_organization_id ;
3225 l_mtlt_rec c_mtlt%ROWTYPE;
3226 l_msnt_rec c_msnt%ROWTYPE;
3227
3228 BEGIN
3229 IF (l_debug = 1) THEN
3230 print_debug('Inside CallUnpack for Partial Cancelation ' || p_lpn_id);
3231 END IF;
3232
3233 SELECT fnd_global.user_id INTO l_user_id FROM DUAL;
3234 OPEN c_unpack_lpn ;
3235 LOOP
3236 FETCH c_unpack_lpn INTO l_unpack_rec ;
3237 EXIT
3238 WHEN c_unpack_lpn%NOTFOUND;
3239 l_lpn_id := l_unpack_rec.lpn_id;
3240 l_transfer_lpn_id := NULL;
3241 l_org_id := l_unpack_rec.org_id ;
3242 l_subinventory := l_unpack_rec.subinventory_code;
3243 l_locator_id := l_unpack_rec.locator_id;
3244 l_trx_qty := l_unpack_rec.quantity;
3245 l_pri_qty := l_unpack_rec.primary_qty;
3246 l_sectrxqty := l_unpack_rec.sec_qty ;
3247 l_item_id := l_unpack_rec.inventory_item_id;
3248 l_revision := l_unpack_rec.revision;
3249 l_trx_uom_code := l_unpack_rec.uom_code;
3250 l_secondary_unit_of_measure := l_unpack_rec.sec_uom;
3251 l_trx_src_type_id := 13;
3252 l_interface_txn_id := p_rtv_order; --This is to stamp,RTV reference on MMT for unpack transaction
3253 l_trx_action_id := 51;
3254 l_trx_type_id := 88;
3255 --I will modify the below query ,to get control from Cache..
3256 /* SELECT LOT_CONTROL_CODE,SERIAL_NUMBER_CONTROL_CODE
3257 INTO l_lot_control_code,l_serial_control_code
3258 FROM mtl_system_items
3259 WHERE INVENTORY_ITEM_ID = l_item_id
3260 AND ORGANIZATION_ID = l_org_id
3261 AND ROWNUM <2 ; */
3262 /* SELECT mtl_material_transactions_s.NEXTVAL
3263 INTO l_trx_header_id
3264 FROM dual; */
3265
3266 IF (l_debug = 1) THEN
3267 print_debug('Inside CallUnpack for for lpn_content_id ' || l_unpack_rec.lpn_content_id);
3268 print_debug('Before call to inv_trx_util_pub.insert_line_trx ' || p_lpn_id);
3269 END IF;
3270
3271 l_return_status := inv_trx_util_pub.insert_line_trx( p_trx_hdr_id => p_trx_header_id
3272 , p_item_id => l_item_id
3273 , p_revision => l_revision
3274 , p_org_id => l_org_id
3275 , p_trx_action_id => l_trx_action_id
3276 , p_subinv_code => l_subinventory
3277 , p_locator_id => l_locator_id
3278 , p_trx_type_id => l_trx_type_id
3279 , p_trx_src_type_id => l_trx_src_type_id
3280 , p_trx_qty => l_trx_qty
3281 , p_pri_qty => l_pri_qty
3282 , p_uom => l_trx_uom_code
3283 , p_user_id => l_user_id
3284 , p_from_lpn_id => l_lpn_id
3285 , p_xfr_lpn_id => l_transfer_lpn_id
3286 , x_trx_tmp_id => l_trx_tmp_id
3287 , x_proc_msg => l_msg_data
3288 , p_secondary_trx_qty => l_sectrxqty
3289 , p_secondary_uom => l_secondary_unit_of_measure );
3290
3291 IF (l_return_status = -1) THEN
3292 l_position := '0040';
3293 x_return_status := FND_API.G_RET_STS_ERROR;
3294 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3295 END IF;
3296
3297 IF (l_debug = 1) THEN
3298 print_debug('MMTT insertion done' || p_lpn_id);
3299 END IF;
3300
3301 FOR l_mtlt_rec IN c_mtlt(l_unpack_rec.lot_number,l_org_id)
3302 LOOP
3303 IF (l_debug = 1) THEN
3304 print_debug('Inside loop for Lot ' || l_unpack_rec.lot_number);
3305 END IF;
3306 l_return_status := inv_trx_util_pub.insert_lot_trx( p_trx_tmp_id => l_trx_tmp_id,
3307 p_user_id => l_user_id,
3308 p_lot_number => l_unpack_rec.lot_number,
3309 p_exp_date => l_mtlt_rec.expiration_date,
3310 p_origination_date => l_mtlt_rec.origination_date,
3311 p_trx_qty => l_trx_qty,
3312 p_pri_qty => l_pri_qty,
3313 x_ser_trx_id => l_ser_trx_id,
3314 x_proc_msg => l_msg_data );
3315
3316 IF (l_debug = 1) THEN
3317 print_debug('After inserting MTLT for LOT ' || l_unpack_rec.lot_number);
3318 END IF;
3319 END LOOP; --c_mtlt
3320 IF (l_return_status = -1) THEN
3321 IF (l_debug = 1) THEN
3322 print_debug('Error While inserting lot transaction ' || l_unpack_rec.lot_number);
3323 END IF;
3324 l_position := '0040';
3325 x_return_status := FND_API.G_RET_STS_ERROR;
3326 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3327 END IF;
3328
3329 --Review Comments
3330
3331 IF(l_return_status = 0 AND l_unpack_rec.serial_control = 1) THEN
3332
3333 --Review Comments
3334
3335 --fnd_message.debug('Create MSNT');
3336 IF (l_ser_trx_id IS NOT NULL) THEN
3337 l_trx_tmp_id := l_ser_trx_id;
3338 END IF;
3339 FOR l_msnt_rec IN c_msnt(l_lpn_id,l_org_id,l_item_id)
3340 LOOP
3341 IF (l_debug = 1) THEN
3342 print_debug('Before calling insert Serial transaction ' || l_unpack_rec.lpn_id);
3343 END IF;
3344
3345 l_return_status := inv_trx_util_pub.insert_ser_trx( p_trx_tmp_id => l_trx_tmp_id,
3346 p_user_id => l_user_id,
3347 p_fm_ser_num => l_msnt_rec.serial_number,
3348 p_to_ser_num => l_msnt_rec.serial_number,
3349 x_proc_msg => l_msg_data );
3350
3351 IF (l_return_status = -1) THEN
3352 IF (l_debug = 1) THEN
3353 print_debug('Error occured while inserting Serial transaction ' || l_unpack_rec.lpn_id);
3354 END IF;
3355 l_position := '0040';
3356 x_return_status := FND_API.G_RET_STS_ERROR;
3357 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3358 END IF;
3359 --fnd_message.debug('Created MSNT Successfully.');
3360 IF (l_debug = 1) THEN
3361 print_debug('After inserting MSNT for Serial ' || l_msnt_rec.serial_number);
3362 END IF;
3363 END LOOP ;--c_msnt
3364 END IF;
3365 END LOOP ; --c_unpack_lpn
3366 EXCEPTION
3367 WHEN OTHERS THEN
3368 IF (l_debug = 1) THEN
3369 l_position := '0040';
3370 print_debug('Error(' || l_position || '):' || sqlerrm);
3371 END IF;
3372 x_return_status := FND_API.G_RET_STS_ERROR;
3373 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3374 x_msg_data := x_msg_data||sqlerrm;
3375 END callUnpack;
3376
3377
3378 /*
3379 --16514543
3380 --Description:Chnages done to Unpack qty from LPN ,in case of Partial Cancelation of Return Order.
3381 --This api will be called from RTV specific package :RCVWSHIB.pls
3382 */
3383
3384 PROCEDURE cancel_return_order_RTV(
3385 x_return_status OUT NOCOPY VARCHAR2,
3386 x_msg_count OUT NOCOPY NUMBER,
3387 x_msg_data OUT NOCOPY VARCHAR2,
3388 p_rtv_order IN NUMBER )
3389 IS
3390 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3391 l_position VARCHAR2(4) := '0000';
3392 l_message VARCHAR2(2000);
3393 l_rti_qty NUMBER ;
3394 l_return_status VARCHAR2(1);
3395 l_msg_count NUMBER;
3396 l_msg_data VARCHAR2(2000);
3397 l_trx_header_id NUMBER ;
3398 l_ret NUMBER;
3399 CURSOR c_get_lpnrti
3400 IS
3401 SELECT transfer_lpn_id,
3402 SUM(quantity) rti_qty
3403 FROM rcv_transactions_interface
3404 WHERE group_id = p_rtv_order
3405 AND PROCESSING_STATUS_CODE = 'WSH_INTERFACED'
3406 AND transfer_lpn_id IS NOT NULL
3407 GROUP BY transfer_lpn_id;
3408 CURSOR get_unpack_lpn(p_lpn_id NUMBER, p_rti_qty NUMBER)
3409 IS
3410 SELECT parent_lpn_id
3411 FROM wms_lpn_contents
3412 WHERE parent_lpn_id = p_lpn_id
3413 --AND source_header_is is null
3414 GROUP BY parent_lpn_id
3415 HAVING SUM(quantity) <> p_rti_qty;
3416 l_lpnrti_rec c_get_lpnrti%ROWTYPE ;
3417 l_lpn_id NUMBER;
3418 BEGIN
3419 l_position := '0010';
3420 x_return_status := FND_API.G_RET_STS_SUCCESS;
3421 IF (l_debug = 1) THEN
3422 print_debug('Enter cancel_return_order_RTV ' || p_rtv_order);
3423 END IF;
3424 OPEN c_get_lpnrti;
3425 LOOP
3426 FETCH c_get_lpnrti INTO l_lpnrti_rec ;
3427 EXIT
3428 WHEN c_get_lpnrti%NOTFOUND;
3429 IF (l_debug = 1) THEN
3430 print_debug('Enter cancel_return_order_RTV :Transfer lpn_id: ' || l_lpnrti_rec.transfer_lpn_id);
3431 print_debug('Enter cancel_return_order_RTV :Sum of RTi qty w.r.t LPN id: ' || l_lpnrti_rec.rti_qty);
3432 END IF;
3433
3434 --Review Comments
3435 SELECT mtl_material_transactions_s.NEXTVAL INTO l_trx_header_id FROM dual;
3436 --Review Comments
3437
3438 OPEN get_unpack_lpn(l_lpnrti_rec.transfer_lpn_id,l_lpnrti_rec.rti_qty);
3439 LOOP
3440 FETCH get_unpack_lpn INTO l_lpn_id ; --Get lpn_id w.r.t RTi.
3441 EXIT
3442 WHEN get_unpack_lpn%notfound;
3443 IF (l_debug = 1) THEN
3444 print_debug('Calling Unpack for the LPN.due to Partial Cancelation' || l_lpn_id);
3445 END IF;
3446
3447 callUnpack( x_return_status => l_return_status,
3448 x_msg_count => l_msg_count,
3449 x_msg_data => l_msg_data,
3450 p_lpn_id => l_lpn_id,
3451 p_rtv_order => p_rtv_order,
3452 p_trx_header_id => l_trx_header_id );
3453
3454 --call unpack(l_lpn_id)
3455 IF (l_debug = 1) THEN
3456 print_debug('After Calling Unpack for the LPN.due to Partial Cancelation' || l_lpn_id);
3457 END IF;
3458 END LOOP ;--get_unpack_lpn
3459
3460 --Review Comments
3461 IF (l_debug = 1) THEN
3462 print_debug('Before calling Process LPN ' || l_lpn_id);
3463 END IF;
3464
3465 l_ret := inv_lpn_trx_pub.process_lpn_trx( p_trx_hdr_id => l_trx_header_id,
3466 p_commit => 'F',
3467 x_proc_msg => x_msg_data,
3468 p_proc_mode=> NULL,
3469 p_process_trx => 'T',
3470 p_atomic => 'T');
3471
3472 -- Check return value and in case of failure raise exception
3473 IF (l_ret <> 0) THEN
3474 IF (l_debug = 1) THEN
3475 print_debug('Error occured while processing LPN ' || l_lpn_id);
3476 END IF;
3477 l_position := '0040';
3478 x_return_status := FND_API.G_RET_STS_ERROR;
3479 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3480 END IF;
3481 IF (l_debug = 1) THEN
3482 print_debug('LPN Processed for LPN' || l_lpn_id);
3483 END IF;
3484 --Review Comments
3485 END LOOP ;---c_get_lpnrti
3486 l_position := '0030';
3487 IF (l_debug = 1) THEN
3488 print_debug('Exit cancel_return_order_RTV');
3489 END IF;
3490 EXCEPTION
3491 WHEN OTHERS THEN
3492 IF (l_debug = 1) THEN
3493 l_position := '0040';
3494 print_debug('Error(' || l_position || '):' || sqlerrm);
3495 END IF;
3496 x_return_status := FND_API.G_RET_STS_ERROR;
3497 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3498 x_msg_data := x_msg_data||sqlerrm;
3499
3500 END cancel_return_order_RTV;
3501
3502
3503
3504 /*
3505 ** This Procedure is called from the Corrections Form to Pack the LPN Contents
3506 ** that are selected for +ve correction into Receiving.
3507 */
3508
3509 PROCEDURE PACK_INTO_RECEIVING (
3510 x_return_status OUT NOCOPY VARCHAR2,
3511 x_msg_count OUT NOCOPY NUMBER,
3512 x_msg_data OUT NOCOPY VARCHAR2,
3513 p_rcv_trx_interface_id IN NUMBER,
3514 p_ret_transaction_type IN VARCHAR2,
3515 p_lpn_id IN NUMBER,
3516 p_item_id IN NUMBER,
3517 p_item_revision IN VARCHAR2,
3518 p_quantity IN NUMBER,
3519 p_uom IN VARCHAR2,
3520 p_serial_controlled IN NUMBER,
3521 p_lot_controlled IN NUMBER,
3522 p_org_id IN NUMBER
3523 ) IS
3524
3525 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
3526 l_lot_number VARCHAR2(80);
3527 l_from_serial_number VARCHAR2(50);
3528 l_to_serial_number VARCHAR2(50);
3529 l_quantity number;
3530 l_cost_group_id number;
3531 TYPE c_ref_type IS REF CURSOR;
3532 c_ref c_ref_type;
3533 l_position VARCHAR2(4) := '0000';
3534
3535 l_wms_po_j_higher BOOLEAN;
3536
3537 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3538 BEGIN
3539
3540 l_position := '0010';
3541 x_return_status := FND_API.G_RET_STS_SUCCESS;
3542
3543 /* FP-J Lot/Serial Support Enhancement
3544 * Read the currentand PO patch levels and set the flag (that would be used to
3545 * match the Lot Number and the LPN) accordingly
3546 */
3547 IF ((WMS_UI_TASKS_APIS.g_wms_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j) AND
3548 (WMS_UI_TASKS_APIS.g_po_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j_po)) THEN
3549 l_wms_po_j_higher := TRUE;
3550 IF (l_debug = 1) THEN
3551 print_debug('PACK_INTO_RECEIVING:WMS and PO patch levels are J or higher', 4);
3552 END IF;
3553 ELSE
3554 l_wms_po_j_higher := FALSE;
3555 IF (l_debug = 1) THEN
3556 print_debug('PACK_INTO_RECEIVING:Either WMS or/and PO patch level(s) are lower than J', 4);
3557 END IF;
3558 END IF;
3559
3560 IF (l_wms_po_j_higher = FALSE) THEN
3561
3562 IF (l_debug = 1) THEN
3563 print_debug('Enter PACK_INTO_RECEIVING');
3564 print_debug('p_serial_controlled => ' || p_serial_controlled);
3565 print_debug('p_lot_controlled => ' || p_lot_controlled);
3566 END IF;
3567
3568 IF p_lot_controlled = 2 AND p_serial_controlled in (2,5) THEN
3569 l_position := '0020';
3570 open c_ref for SELECT mtlt.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
3571 MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity
3572 FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT
3573 WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
3574 AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
3575 AND MSN.INVENTORY_ITEM_ID = p_item_id
3576 AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
3577 --AND MSN.LOT_NUMBER = MTLT.LOT_NUMBER
3578 AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
3579 AND MSN.SERIAL_NUMBER <= MSNT.TO_SERIAL_NUMBER;
3580 -- AND length(MSN.SERIAL_NUMBER) = length(MSNT.FM_SERIAL_NUMBER);
3581 -- It is not possible to use length function here because table MTL_TRANSACTION_LOTS_TEMP
3582 -- has a field 'length' and it would result in a compilation error if function length
3583 -- is used.
3584
3585 ELSIF p_lot_controlled = 2 AND p_serial_controlled not in (2,5) THEN
3586 l_position := '0030';
3587 open c_ref for SELECT MTLT.LOT_NUMBER, NULL FM_SERIAL_NUMBER,
3588 NULL TO_SERIAL_NUMBER, MTLT.TRANSACTION_QUANTITY quantity
3589 FROM MTL_TRANSACTION_LOTS_TEMP MTLT
3590 WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id;
3591
3592 ELSIF p_lot_controlled <> 2 AND p_serial_controlled in (2,5) THEN
3593 l_position := '0040';
3594 open c_ref for SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
3595 MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity
3596 FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT
3597 WHERE MSNT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
3598 AND MSN.INVENTORY_ITEM_ID = p_item_id
3599 AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
3600 AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
3601 AND MSN.SERIAL_NUMBER <= MSNT.TO_SERIAL_NUMBER
3602 AND length(MSN.SERIAL_NUMBER) = length(MSNT.FM_SERIAL_NUMBER);
3603
3604 ELSE
3605 l_position := '0050';
3606 open c_ref for SELECT NULL , NULL , NULL , rti.quantity
3607 FROM RCV_TRANSACTIONS_INTERFACE RTI
3608 WHERE RTI.INTERFACE_TRANSACTION_ID = p_rcv_trx_interface_id;
3609
3610 END IF;
3611 l_position := '0060';
3612
3613 LOOP
3614 l_position := '0070';
3615
3616 FETCH c_ref into l_lot_number, l_from_serial_number, l_to_serial_number, l_quantity;
3617 l_position := '0080';
3618
3619 IF c_ref%NOTFOUND THEN EXIT; END IF;
3620 l_position := '0090';
3621
3622 /* Update the previous_status of serial to current_status before doing anything.
3623 ** This is needed so that current_status can be put back to previous_status
3624 ** if the txn fails. Bringing back the status is done in txn_complete
3625 ** if txn fails.
3626 */
3627 IF l_from_serial_number IS NOT NULL THEN
3628 UPDATE mtl_serial_numbers
3629 SET previous_status = current_status
3630 WHERE serial_number = l_from_serial_number
3631 AND inventory_item_id = p_item_id;
3632 END IF;
3633
3634 PackUnpack_Container(
3635 x_return_status => x_return_status,
3636 x_msg_count => x_msg_count,
3637 x_msg_data => x_msg_data,
3638 p_lpn_id => p_lpn_id,
3639 p_content_item_id => p_item_id,
3640 p_revision => p_item_revision ,
3641 p_lot_number => l_lot_number,
3642 p_from_serial_number => l_from_serial_number,
3643 p_to_serial_number => l_to_serial_number,
3644 p_quantity => abs(l_quantity),
3645 p_uom => p_uom,
3646 p_organization_id => p_org_id,
3647 p_subinventory => NULL,
3648 p_locator_id => NULL,
3649 p_operation => 1, -- pack
3650 p_source_header_id => p_rcv_trx_interface_id,
3651 p_source_name => p_ret_transaction_type,
3652 P_COST_GROUP_ID => NULL
3653 );
3654
3655 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
3656 IF (l_debug = 1) THEN
3657 print_debug('Error in PACK_INTO_RECEIVING while packing');
3658 END IF;
3659 RAISE fnd_api.g_exc_error; --return; -- error while packing
3660 end if;
3661
3662 END LOOP;
3663
3664 UPDATE wms_license_plate_numbers
3665 SET lpn_context = wms_container_pub.lpn_context_rcv
3666 WHERE lpn_id = p_lpn_id;
3667
3668 UPDATE mtl_txn_request_lines
3669 SET wms_process_flag = 2
3670 , txn_source_line_detail_id = p_rcv_trx_interface_id
3671 WHERE lpn_id = p_lpn_id;
3672
3673 l_position := '0200';
3674 IF (l_debug = 1) THEN
3675 print_debug('Exit PACK_INTO_RECEIVING');
3676 END IF;
3677 END IF;--IF (l_wms_po_j_higher = FALSE) THEN
3678
3679 EXCEPTION
3680 WHEN FND_API.g_exc_error THEN
3681 IF (l_debug = 1) THEN
3682 print_debug('maintain_move_orders : execution error');
3683 END IF;
3684
3685 x_return_status := FND_API.G_RET_STS_ERROR;
3686 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3687 when others then
3688 x_return_status := FND_API.G_RET_STS_ERROR;
3689 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
3690 x_msg_data := x_msg_data||sqlerrm;
3691 IF (l_debug = 1) THEN
3692 print_debug('Error(' || l_position || '):' || sqlerrm);
3693 END IF;
3694 END PACK_INTO_RECEIVING;
3695
3696 /*
3697 ** This procedure is called from Mobile Returns when the input LPN
3698 ** is totally marked for Return.
3699 */
3700
3701 PROCEDURE PROCESS_WHOLE_LPN_RETURN (
3702 x_return_status OUT NOCOPY VARCHAR2
3703 , x_msg_count OUT NOCOPY NUMBER
3704 , x_msg_data OUT NOCOPY VARCHAR2
3705 , p_org_id IN NUMBER
3706 , p_lpn_id IN NUMBER
3707 , p_txn_proc_mode IN VARCHAR2
3708 , p_group_id IN NUMBER
3709 ) IS
3710 l_rtiid NUMBER;
3711
3712 TYPE c_ref_type IS REF CURSOR;
3713 c_ref c_ref_type;
3714 c_ref_ser c_ref_type;
3715
3716 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3717 BEGIN
3718
3719 IF (l_debug = 1) THEN
3720 print_debug('Enter PROCESS_WHOLE_LPN_RETURN');
3721 print_debug('Called PROCESS_WHOLE_LPN_RETURN with the parameters');
3722 END IF;
3723
3724 IF (l_debug = 1) THEN
3725 print_debug('p_org_id =>' || p_org_id);
3726 print_debug('p_lpn_id =>' || p_lpn_id);
3727 print_debug('p_txn_proc_mode =>' || p_txn_proc_mode);
3728 print_debug('p_group_id =>' || p_group_id);
3729 END IF;
3730
3731 x_return_status := fnd_api.g_ret_sts_success;
3732
3733 open c_ref for SELECT SOURCE_HEADER_ID
3734 FROM WMS_LPN_CONTENTS WLPNC
3735 WHERE WLPNC.ORGANIZATION_ID = p_org_id
3736 AND WLPNC.PARENT_LPN_ID = p_lpn_id
3737 AND NVL(SERIAL_SUMMARY_ENTRY,2) <> 1 -- Non Serial Contents Records(value=2)
3738 AND WLPNC.SOURCE_NAME IN ('RETURN TO VENDOR',
3739 'RETURN TO CUSTOMER',
3740 'RETURN TO RECEIVING');
3741
3742 LOOP
3743 FETCH c_ref into l_rtiid;
3744 IF c_ref%NOTFOUND THEN
3745 EXIT;
3746 END IF;
3747 IF (l_debug = 1) THEN
3748 print_debug('l_rtiid =>' || l_rtiid);
3749 END IF;
3750
3751 UPDATE RCV_TRANSACTIONS_INTERFACE
3752 SET GROUP_ID = p_group_id,
3753 PROCESSING_MODE_CODE = p_txn_proc_mode,
3754 MOBILE_TXN = 'Y'
3755 WHERE INTERFACE_TRANSACTION_ID = l_rtiid;
3756 END LOOP;
3757 IF (l_debug = 1) THEN
3758 print_debug('END OF LOOP PROCESS WHOLE LPN RETURN - Non Serial Contents Records');
3759 END IF;
3760
3761 open c_ref_ser for SELECT LAST_TXN_SOURCE_ID
3762 FROM MTL_SERIAL_NUMBERS MSN
3763 WHERE MSN.LPN_ID = p_lpn_id
3764 AND MSN.LAST_TXN_SOURCE_NAME IN ('RETURN TO VENDOR',
3765 'RETURN TO CUSTOMER',
3766 'RETURN TO RECEIVING');
3767
3768 LOOP
3769 FETCH c_ref_ser into l_rtiid;
3770 IF c_ref_ser%NOTFOUND THEN
3771 EXIT;
3772 END IF;
3773 IF (l_debug = 1) THEN
3774 print_debug('l_rtiid =>' || l_rtiid);
3775 END IF;
3776
3777 UPDATE RCV_TRANSACTIONS_INTERFACE
3778 SET GROUP_ID = p_group_id,
3779 PROCESSING_MODE_CODE = p_txn_proc_mode,
3780 MOBILE_TXN = 'Y'
3781 WHERE INTERFACE_TRANSACTION_ID = l_rtiid;
3782 END LOOP;
3783 IF (l_debug = 1) THEN
3784 print_debug('END OF LOOP PROCESS WHOLE LPN RETURN - Serial Records');
3785 END IF;
3786
3787 EXCEPTION
3788 WHEN fnd_api.g_exc_error THEN
3789
3790 x_return_status := fnd_api.g_ret_sts_error;
3791 IF (l_debug = 1) THEN
3792 print_debug('x_return_status =>' || x_return_status);
3793 END IF;
3794
3795 -- Get message count and data
3796 fnd_msg_pub.count_and_get
3797 ( p_count => x_msg_count
3798 , p_data => x_msg_data
3799 );
3800
3801 IF (c_ref%isopen) THEN
3802 CLOSE c_ref;
3803 END IF;
3804 IF (c_ref_ser%isopen) THEN
3805 CLOSE c_ref_ser;
3806 END IF;
3807
3808
3809 WHEN fnd_api.g_exc_unexpected_error THEN
3810
3811 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3812 IF (l_debug = 1) THEN
3813 print_debug('x_return_status =>' || x_return_status);
3814 END IF;
3815
3816 -- Get message count and data
3817 fnd_msg_pub.count_and_get
3818 ( p_count => x_msg_count
3819 , p_data => x_msg_data
3820 );
3821
3822 IF (c_ref%isopen) THEN
3823 CLOSE c_ref;
3824 END IF;
3825 IF (c_ref_ser%isopen) THEN
3826 CLOSE c_ref_ser;
3827 END IF;
3828
3829
3830 WHEN others THEN
3831
3832 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3833 IF (l_debug = 1) THEN
3834 print_debug('x_return_status =>' || x_return_status);
3835 END IF;
3836 --
3837 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3838 THEN
3839 fnd_msg_pub.add_exc_msg
3840 ( g_pkg_name
3841 , 'main_process'
3842 );
3843 END IF;
3844
3845 -- Get message count and data
3846 fnd_msg_pub.count_and_get
3847 ( p_count => x_msg_count
3848 , p_data => x_msg_data
3849 );
3850
3851 IF (c_ref%isopen) THEN
3852 CLOSE c_ref;
3853 END IF;
3854 IF (c_ref_ser%isopen) THEN
3855 CLOSE c_ref_ser;
3856 END IF;
3857
3858 END PROCESS_WHOLE_LPN_RETURN;
3859
3860
3861 /*
3862 ** This procedure is called from Mobile Returns when the input LPN
3863 ** is partially marked for Return.
3864 */
3865
3866 PROCEDURE PROCESS_RETURNS (
3867 x_return_status OUT NOCOPY VARCHAR2
3868 , x_msg_count OUT NOCOPY NUMBER
3869 , x_msg_data OUT NOCOPY VARCHAR2
3870 , p_org_id IN NUMBER
3871 , p_lpn_id IN NUMBER
3872 , p_item_id IN NUMBER
3873 , p_item_revision IN VARCHAR2
3874 , p_uom IN VARCHAR2
3875 , p_lot_code IN VARCHAR2
3876 , p_serial_code IN VARCHAR2
3877 , p_quantity IN NUMBER
3878 , p_serial_controlled IN NUMBER
3879 , p_lot_controlled IN NUMBER
3880 , p_txn_proc_mode IN VARCHAR2
3881 , p_group_id IN NUMBER
3882 , p_to_lpn_id IN NUMBER
3883 ) IS
3884 l_rtiid NUMBER;
3885
3886 TYPE c_ref_type IS REF CURSOR;
3887 c_ref c_ref_type;
3888
3889 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3890 BEGIN
3891
3892 x_return_status := fnd_api.g_ret_sts_success;
3893
3894 IF (l_debug = 1) THEN
3895 print_debug('Enter PROCESS_RETURNS');
3896 print_debug('Called PROCESS_RETURNS with the parameters');
3897 END IF;
3898
3899 IF (l_debug = 1) THEN
3900 print_debug('p_org_id =>' || p_org_id);
3901 print_debug('p_lpn_id =>' || p_lpn_id);
3902 print_debug('p_item_id =>' || p_item_id);
3903 print_debug('p_item_revision =>' || p_item_revision);
3904 print_debug('p_uom =>' || p_uom);
3905 print_debug('p_lot_code =>' || p_lot_code);
3906 print_debug('p_serial_code =>' || p_serial_code);
3907 print_debug('p_quantity =>' || p_quantity);
3908 print_debug('p_serial_controlled =>' || p_serial_controlled);
3909 print_debug('p_lot_controlled =>' || p_lot_controlled);
3910 print_debug('p_txn_proc_mode =>' || p_txn_proc_mode);
3911 print_debug('p_group_id =>' || p_group_id);
3912 print_debug('p_to_lpn_id =>' || p_to_lpn_id);
3913 END IF;
3914
3915 IF p_lot_controlled = 2 AND p_serial_controlled = 2 THEN -- NonSerial and NonLot
3916 open c_ref for SELECT SOURCE_HEADER_ID
3917 FROM WMS_LPN_CONTENTS WLPNC
3918 WHERE WLPNC.ORGANIZATION_ID = p_org_id
3919 AND WLPNC.PARENT_LPN_ID = p_lpn_id
3920 AND WLPNC.INVENTORY_ITEM_ID = p_item_id
3921 AND ((WLPNC.revision = p_item_revision AND p_item_revision IS NOT NULL) OR
3922 (WLPNC.REVISION IS NULL AND p_item_revision IS NULL))
3923 AND WLPNC.SOURCE_NAME IN ('RETURN TO VENDOR',
3924 'RETURN TO CUSTOMER',
3925 'RETURN TO RECEIVING');
3926
3927 ELSIF p_serial_controlled = 1 THEN -- Serial, Lot control doesn't matter
3928 open c_ref for SELECT LAST_TXN_SOURCE_ID
3929 FROM MTL_SERIAL_NUMBERS MSN
3930 WHERE MSN.LPN_ID = p_lpn_id
3931 AND MSN.INVENTORY_ITEM_ID = p_item_id
3932 AND ((MSN.REVISION = p_item_revision AND p_item_revision IS NOT NULL) OR
3933 (MSN.REVISION IS NULL AND p_item_revision IS NULL))
3934 AND MSN.LAST_TXN_SOURCE_NAME IN ('RETURN TO VENDOR',
3935 'RETURN TO CUSTOMER',
3936 'RETURN TO RECEIVING')
3937 AND MSN.SERIAL_NUMBER = p_serial_code;
3938
3939 ELSIF p_lot_controlled = 1 AND p_serial_controlled = 2 THEN -- Lot Only
3940 open c_ref for SELECT SOURCE_HEADER_ID
3941 FROM WMS_LPN_CONTENTS WLPNC
3942 WHERE WLPNC.ORGANIZATION_ID = p_org_id
3943 AND WLPNC.PARENT_LPN_ID = p_lpn_id
3944 AND WLPNC.INVENTORY_ITEM_ID = p_item_id
3945 AND ((WLPNC.REVISION = p_item_revision AND p_item_revision IS NOT NULL) OR
3946 (WLPNC.REVISION IS NULL AND p_item_revision IS NULL))
3947 AND WLPNC.SOURCE_NAME IN ('RETURN TO VENDOR',
3948 'RETURN TO CUSTOMER',
3949 'RETURN TO RECEIVING')
3950 AND WLPNC.LOT_NUMBER = p_lot_code;
3951
3952 END IF;
3953
3954 LOOP
3955 FETCH c_ref into l_rtiid;
3956 IF c_ref%NOTFOUND THEN
3957 EXIT;
3958 END IF;
3959 IF (l_debug = 1) THEN
3960 print_debug('l_rtiid =>' || l_rtiid);
3961 END IF;
3962
3963 UPDATE RCV_TRANSACTIONS_INTERFACE
3964 SET GROUP_ID = p_group_id,
3965 PROCESSING_MODE_CODE = p_txn_proc_mode,
3966 MOBILE_TXN = 'Y'
3967 WHERE INTERFACE_TRANSACTION_ID = l_rtiid;
3968
3969 IF (l_debug = 1) THEN
3970 print_debug('yes, p_to_lpn_id not zero ' || to_char(p_to_lpn_id));
3971 END IF;
3972 IF p_to_lpn_id <> 0 THEN
3973 IF (l_debug = 1) THEN
3974 print_debug('yes, p_to_lpn_id not zero ' || to_char(p_to_lpn_id));
3975 END IF;
3976 UPDATE RCV_TRANSACTIONS_INTERFACE
3977 SET TRANSFER_LPN_ID = p_to_lpn_id
3978 WHERE INTERFACE_TRANSACTION_ID = l_rtiid
3979 AND NVL(TRANSACTION_TYPE, '@@@') = 'RETURN TO RECEIVING';
3980 END IF;
3981
3982 END LOOP;
3983 COMMIT;
3984 IF (l_debug = 1) THEN
3985 print_debug('END OF LOOP PROCESS RETURNS');
3986 END IF;
3987
3988 EXCEPTION
3989 WHEN fnd_api.g_exc_error THEN
3990
3991 x_return_status := fnd_api.g_ret_sts_error;
3992 IF (l_debug = 1) THEN
3993 print_debug('x_return_status =>' || x_return_status);
3994 END IF;
3995
3996 -- Get message count and data
3997 fnd_msg_pub.count_and_get
3998 ( p_count => x_msg_count
3999 , p_data => x_msg_data
4000 );
4001
4002 IF (c_ref%isopen) THEN
4003 CLOSE c_ref;
4004 END IF;
4005
4006
4007 WHEN fnd_api.g_exc_unexpected_error THEN
4008
4009 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4010 IF (l_debug = 1) THEN
4011 print_debug('x_return_status =>' || x_return_status);
4012 END IF;
4013
4014 -- Get message count and data
4015 fnd_msg_pub.count_and_get
4016 ( p_count => x_msg_count
4017 , p_data => x_msg_data
4018 );
4019
4020 IF (c_ref%isopen) THEN
4021 CLOSE c_ref;
4022 END IF;
4023
4024
4025 WHEN others THEN
4026
4027 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4028 IF (l_debug = 1) THEN
4029 print_debug('x_return_status =>' || x_return_status);
4030 END IF;
4031 --
4032 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4033 THEN
4034 fnd_msg_pub.add_exc_msg
4035 ( g_pkg_name
4036 , 'main_process'
4037 );
4038 END IF;
4039
4040 -- Get message count and data
4041 fnd_msg_pub.count_and_get
4042 ( p_count => x_msg_count
4043 , p_data => x_msg_data
4044 );
4045
4046 IF (c_ref%isopen) THEN
4047 CLOSE c_ref;
4048 END IF;
4049
4050
4051 END PROCESS_RETURNS;
4052
4053 /*
4054 ** This Function is called from procedure 'GET_TRX_VALUES' to get Receiving
4055 ** Processing Mode.
4056 */
4057
4058 FUNCTION GET_TRX_PROC_MODE RETURN VARCHAR2 IS
4059 /*
4060 ** Function will return Receiving Transaction Processor Mode (RCV_TP_MODE)
4061 ** If Transaction Processor Mode is NULL then
4062 ** Default the Mode to 'ONLINE'
4063 ** Function will be referencing a 'FND_PROFILE.GET' procedure defined
4064 ** by AOL grp . It will return the value of the PROFILE being asked for,
4065 ** or will return 'ONLINE' if profile 'RCV_TP_MODE' is NULL.
4066 */
4067 transaction_processor_value VARCHAR2(10);
4068
4069 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4070 BEGIN
4071 fnd_profile.get('RCV_TP_MODE',transaction_processor_value);
4072
4073 if transaction_processor_value is null then
4074 transaction_processor_value := 'ONLINE';
4075 end if;
4076
4077 return(transaction_processor_value);
4078
4079 EXCEPTION
4080 WHEN OTHERS THEN
4081 IF (l_debug = 1) THEN
4082 print_debug('Failure getting transaction processing mode');
4083 END IF;
4084 RAISE;
4085
4086 END GET_TRX_PROC_MODE;
4087
4088 /*
4089 ** This procedure is called from Mobile Returns to determine the
4090 ** Receiving Processing Mode and Group ID from sequence that are used
4091 ** to stamp on RTI. This single wrapper procedure is created so that Mobile
4092 ** Returns visits Database only once to get both Receiving Processing Mode
4093 ** and Group ID.
4094 */
4095
4096 PROCEDURE GET_TRX_VALUES(
4097 transaction_processor_value OUT NOCOPY VARCHAR2
4098 , group_id OUT NOCOPY NUMBER) IS
4099 l_groupid NUMBER;
4100 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4101 BEGIN
4102 transaction_processor_value := GET_TRX_PROC_MODE;
4103 SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL INTO l_groupid FROM DUAL;
4104
4105 group_id := l_groupid;
4106 EXCEPTION
4107 WHEN OTHERS THEN
4108 IF (l_debug = 1) THEN
4109 print_debug('Failure getting TXN PROC MODE and GROUPID');
4110 END IF;
4111 RAISE;
4112
4113 END GET_TRX_VALUES;
4114
4115 /*
4116 ** This procedure is called from Mobile Returns to launch the Receiving
4117 ** Processor after setting the input group ID and receiving processing mode.
4118 */
4119
4120 PROCEDURE RCV_PROCESS_WRAPPER(
4121 x_return_status OUT NOCOPY VARCHAR2
4122 , x_msg_data OUT NOCOPY VARCHAR2
4123 , p_trx_proc_mode IN VARCHAR2
4124 , p_group_id IN NUMBER) IS
4125
4126 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4127 BEGIN
4128 inv_rcv_common_apis.g_po_startup_value.transaction_mode := p_trx_proc_mode;
4129 inv_rcv_common_apis.g_rcv_global_var.interface_group_id := p_group_id;
4130 INV_RCV_MOBILE_PROCESS_TXN.rcv_process_receive_txn(x_return_status, x_msg_data);
4131 END RCV_PROCESS_WRAPPER;
4132
4133 /*
4134 ** This procedure is called from Mobile Returns to get the suggested 'To LPN'
4135 ** if any, for the input From LPN and Item.
4136 */
4137
4138 PROCEDURE GET_SUGGESTED_TO_LPN(
4139 x_lpn_lov OUT NOCOPY t_genref
4140 , p_org_id IN NUMBER
4141 , p_lpn_id IN NUMBER
4142 , p_item_id IN NUMBER
4143 , p_revision IN VARCHAR2) IS
4144 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4145 BEGIN
4146 OPEN x_lpn_lov FOR
4147 select distinct wlpnc.license_plate_number
4148 from wms_license_plate_numbers wlpnc, rcv_transactions_interface rti
4149 where rti.lpn_id = p_lpn_id
4150 and rti.item_id = p_item_id
4151 and nvl(rti.item_revision, '@@@') = nvl(p_revision, '@@@')
4152 and nvl(rti.transaction_type, '@@@') = 'RETURN TO RECEIVING'
4153 and rti.transfer_lpn_id is not null
4154 and wlpnc.lpn_id = rti.transfer_lpn_id
4155 and wlpnc.organization_id = p_org_id;
4156 END GET_SUGGESTED_TO_LPN;
4157
4158 /* This procedure is used to create a reservation during a Return. Called
4159 ** from RCVTXERE.pld after creating an rcv_transaction_interface_record
4160 */
4161 PROCEDURE CREATE_RETURN_RESV(
4162 x_return_status OUT NOCOPY VARCHAR2,
4163 x_msg_count OUT NOCOPY VARCHAR2,
4164 x_msg_data OUT NOCOPY VARCHAR2,
4165 p_org_id IN NUMBER,
4166 p_item_id IN NUMBER,
4167 p_revision IN VARCHAR2,
4168 p_subinventory_code IN VARCHAR2,
4169 p_locator_id IN NUMBER,
4170 p_lpn_id IN NUMBER,
4171 p_reservation_qty IN NUMBER,
4172 p_unit_of_measure IN VARCHAR2,
4173 p_requirement_date IN DATE,
4174 p_dem_src_type_id IN NUMBER,
4175 p_dem_src_hdr_id IN NUMBER,
4176 p_dem_src_line_id IN NUMBER,
4177 p_intf_txn_id IN NUMBER
4178 ) IS
4179
4180 /* Reservation Data Structures */
4181 l_mtl_reservation_tbl_count NUMBER;
4182 l_reservation_record INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
4183 l_qry_reservation_record INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
4184 l_upd_reservation_record INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
4185 l_upd_reservation_tbl INV_RESERVATION_GLOBAL.MTL_RESERVATION_TBL_TYPE;
4186 l_upd_reservation_tbl_cnt NUMBER := 0;
4187 l_dummy_sn INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
4188 l_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE; --Lot Number
4189 l_uom_code VARCHAR2(3); --UOM Code
4190 l_quantity_reserved NUMBER; --Quantity that was reserved
4191 l_reservation_id NUMBER; --Reservation Id
4192 l_error_code NUMBER;
4193 l_item_primary_uom VARCHAR2(3);
4194 l_primary_res_qty NUMBER;
4195 l_res_lpn_id NUMBER;
4196 l_create_res BOOLEAN := TRUE;
4197 l_lot_control_code NUMBER := 1;
4198
4199 CURSOR c_lots IS
4200 SELECT lot_number, primary_quantity
4201 FROM mtl_transaction_lots_temp
4202 WHERE product_code = 'RCV'
4203 AND product_transaction_id = p_intf_txn_id;
4204
4205 CURSOR c_lots_old IS
4206 SELECT lot_number, primary_quantity
4207 FROM mtl_transaction_lots_temp
4208 WHERE transaction_temp_id = p_intf_txn_id;
4209
4210 l_wms_po_j_higher BOOLEAN;
4211
4212 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4213 BEGIN
4214 x_return_status := fnd_api.g_ret_sts_success;
4215
4216 IF (l_debug = 1) THEN
4217 print_debug('CREATE_RETURN_RESV:Interface Transaction ID:'||p_intf_txn_id,1);
4218 END IF;
4219
4220 --Get the UOM code based on the Unit of Measure passed
4221 --For expense items, UOM would be null
4222 SELECT uom_code
4223 INTO l_uom_code
4224 FROM mtl_item_uoms_view
4225 WHERE inventory_item_id = p_item_id
4226 AND organization_id = p_org_id
4227 AND unit_of_measure = p_unit_of_measure;
4228
4229 SELECT primary_uom_code, lot_control_code
4230 INTO l_item_primary_uom, l_lot_control_code
4231 FROM mtl_system_items
4232 WHERE inventory_item_id = p_item_id
4233 AND organization_id = p_org_id;
4234
4235 -- BEGIN
4236 -- SELECT wlc.lot_number
4237 -- INTO l_lot_number
4238 --FROM wms_lpn_contents wlc,
4239 -- mtl_system_items msi
4240 --WHERE wlc.parent_lpn_id = p_lpn_id
4241 --AND wlc.organization_id = p_org_id
4242 --AND wlc.inventory_item_id = p_item_id
4243 --AND msi.inventory_item_id = p_item_id
4244 --AND msi.organization_id = p_org_id
4245 --AND msi.lot_control_code = 2
4246 --AND ROWNUM < 2;
4247
4248 -- EXCEPTION
4249 -- WHEN NO_DATA_FOUND THEN
4250 -- NULL;
4251 -- END;
4252
4253 IF ((WMS_UI_TASKS_APIS.g_wms_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j) AND
4254 (WMS_UI_TASKS_APIS.g_po_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j_po)) THEN
4255 l_wms_po_j_higher := TRUE;
4256 IF (l_debug = 1) THEN
4257 print_debug('CREATE_RETURN_RESV:WMS and PO patch levels are J or higher', 4);
4258 END IF;
4259 ELSE
4260 l_wms_po_j_higher := FALSE;
4261 IF (l_debug = 1) THEN
4262 print_debug('CREATE_RETURN_RESV:Either WMS or/and PO patch level(s) are lower than J', 4);
4263 END IF;
4264 END IF;
4265
4266 IF (l_wms_po_j_higher) THEN
4267 OPEN c_lots;
4268 ELSE
4269 OPEN c_lots_old;
4270 END IF;
4271
4272 LOOP
4273 --if the item is lot controlled then fetch from the cursor otherwise
4274 --just use the existing data and exit at the end.
4275 IF (l_lot_control_code = 2) THEN
4276 IF (l_wms_po_j_higher) THEN
4277 FETCH c_lots INTO l_lot_number, l_primary_res_qty;
4278
4279 EXIT WHEN c_lots%NOTFOUND;
4280 ELSE
4281 FETCH c_lots_old INTO l_lot_number, l_primary_res_qty;
4282
4283 EXIT WHEN c_lots_old%NOTFOUND;
4284 END IF;
4285 END IF;
4286
4287 --Check if there exists a reservation record for the current combination
4288 IF (l_debug = 1) THEN
4289 print_debug('CREATE_RETURN_RESV:Lot Number:'||l_lot_number);
4290 print_debug('CREATE_RETURN_RESV:Prim Qty:'||l_primary_res_qty);
4291 print_debug('CREATE_RETURN_RESV:Check if the reservation already exists');
4292 END IF;
4293 l_qry_reservation_record.organization_id := p_org_id;
4294 l_qry_reservation_record.inventory_item_id := p_item_id;
4295 l_qry_reservation_record.demand_source_header_id := p_dem_src_hdr_id;
4296 l_qry_reservation_record.demand_source_line_id := p_dem_src_line_id;
4297 l_qry_reservation_record.demand_source_type_id := p_dem_src_type_id;
4298 l_qry_reservation_record.lpn_id := p_lpn_id;
4299 l_qry_reservation_record.lot_number := l_lot_number;
4300 l_reservation_record.lpn_id := p_lpn_id;
4301
4302 --Query all the reservation records for the above combinations
4303 INV_RESERVATION_PUB.QUERY_RESERVATION(
4304 p_api_version_number => 1.0,
4305 x_return_status => x_return_status,
4306 x_msg_count => x_msg_count,
4307 x_msg_data => x_msg_data,
4308 p_query_input => l_qry_reservation_record,
4309 x_mtl_reservation_tbl => l_upd_reservation_tbl,
4310 x_mtl_reservation_tbl_count => l_upd_reservation_tbl_cnt,
4311 x_error_code => l_error_code
4312 );
4313
4314 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4315 IF (l_debug = 1) THEN
4316 print_debug('CREATE_RETURN_RESV:Error while calling query_reservations');
4317 END IF;
4318 RAISE FND_API.G_EXC_ERROR;
4319 END IF;
4320
4321 IF (l_debug = 1) THEN
4322 print_debug('CREATE_RETURN_RESV:There are ' || l_upd_reservation_tbl_cnt || ' reservation records');
4323 END IF;
4324 --If there exists a reservation for this combination then update the reservation quantity
4325 IF l_upd_reservation_tbl_cnt > 0 THEN
4326 FOR l_count IN 1 .. l_upd_reservation_tbl_cnt LOOP
4327 l_upd_reservation_record := l_upd_reservation_tbl(l_count);
4328 l_res_lpn_id := l_upd_reservation_record.lpn_id;
4329 IF (l_res_lpn_id <> p_lpn_id) THEN
4330 l_create_res := TRUE;
4331 ELSE
4332 l_create_res := FALSE;
4333
4334 --Get the quantity that was already reserved
4335 l_quantity_reserved := l_upd_reservation_record.reservation_quantity;
4336 IF (l_debug = 1) THEN
4337 print_debug('CREATE_RETURN_RESV:Quantity that was reserved so far: ' || l_quantity_reserved);
4338 END IF;
4339 IF (l_lot_control_code = 1) THEN
4340 IF l_uom_code <> l_item_primary_uom THEN
4341 l_primary_res_qty := INV_CONVERT.INV_UM_CONVERT(
4342 item_id => p_item_id,
4343 precision => null,
4344 from_quantity => p_reservation_qty,
4345 from_unit => l_uom_code,
4346 to_unit => l_item_primary_uom,
4347 from_name => null,
4348 to_name => null);
4349 IF (l_primary_res_qty = -99999) THEN
4350 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
4351 fnd_message.set_token('UOM', l_item_primary_uom);
4352 fnd_message.set_token('ROUTINE', 'Create Reservation');
4353 fnd_msg_pub.ADD;
4354 RAISE fnd_api.g_exc_error;
4355 END IF;
4356 ELSE --IF l_uom_code <> l_item_primary_uom THEN
4357 l_primary_res_qty := p_reservation_qty;
4358 END IF; --IF l_uom_code <> l_item_primary_uom THEN
4359 END IF; --IF (l_lot_control_code = 1) THEN
4360
4361 IF (l_debug = 1) THEN
4362 print_debug('CREATE_RETURN_RESV:Quantity entered: ' || l_primary_res_qty);
4363 END IF;
4364 --Add the new quantity to the quantity that was already reserved
4365 l_upd_reservation_record.reservation_quantity :=
4366 l_upd_reservation_record.reservation_quantity + l_primary_res_qty;
4367
4368 l_upd_reservation_record.primary_reservation_quantity :=
4369 l_upd_reservation_record.primary_reservation_quantity + l_primary_res_qty;
4370
4371 --Update the reservation record with the new quantity
4372 INV_RESERVATION_PUB.UPDATE_RESERVATION(
4373 p_api_version_number => 1.0,
4374 p_init_msg_lst => FND_API.G_FALSE,
4375 x_return_status => x_return_status,
4376 x_msg_count => x_msg_count,
4377 x_msg_data => x_msg_data,
4378 p_original_rsv_rec => l_upd_reservation_tbl(l_count),
4379 p_to_rsv_rec => l_upd_reservation_record,
4380 p_original_serial_number => l_dummy_sn,
4381 p_to_serial_number => l_dummy_sn,
4382 p_validation_flag => FND_API.G_TRUE);
4383
4384 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4385 IF (l_debug = 1) THEN
4386 print_debug('CREATE_RETURN_RESV:error in update_reservation');
4387 END IF;
4388 FND_MESSAGE.SET_NAME('INB', 'INV_UPD_RSV_FAILED');
4389 FND_MSG_PUB.ADD;
4390 RAISE fnd_api.g_exc_error;
4391 END IF;
4392 IF (l_debug = 1) THEN
4393 print_debug('CREATE_RETURN_RESV:successfully updated a reservation record: ' ||sql%rowcount);
4394 END IF;
4395 EXIT;
4396 END IF; -- End if the lpn_id is the same as the one being returned
4397 END LOOP;
4398 ELSE
4399 l_create_res := TRUE;
4400 END IF; -- End if there exists a record in mtl_reservations
4401
4402 --There exist no reservations for this combinations. Create one
4403 IF l_create_res = TRUE THEN
4404 IF (l_debug = 1) THEN
4405 print_debug('CREATE_RETURN_RESV:No reservation exists for the LPN. Have to create one...');
4406 END IF;
4407 l_reservation_record.organization_id := p_org_id;
4408 l_reservation_record.inventory_item_id := p_item_id;
4409 l_reservation_record.revision := p_revision;
4410
4411 IF (l_lot_control_code = 1) THEN
4412 --Convert the quantity into the primary UOM code of the item
4413 IF l_uom_code <> l_item_primary_uom THEN
4414 l_primary_res_qty := INV_CONVERT.INV_UM_CONVERT(
4415 item_id => p_item_id,
4416 precision => null,
4417 from_quantity => p_reservation_qty,
4418 from_unit => l_uom_code,
4419 to_unit => l_item_primary_uom,
4420 from_name => null,
4421 to_name => null);
4422 IF (l_primary_res_qty = -99999) THEN
4423 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
4424 fnd_message.set_token('UOM', l_item_primary_uom);
4425 fnd_message.set_token('ROUTINE', 'Create Reservation');
4426 fnd_msg_pub.add;
4427 RAISE fnd_api.g_exc_error;
4428 END IF;
4429 ELSE --IF l_uom_code <> l_item_primary_uom THEN
4430 l_primary_res_qty := p_reservation_qty;
4431 END IF; --IF l_uom_code <> l_item_primary_uom THEN
4432 END IF; --IF (l_lot_control_code = 1) THEN
4433
4434 --The reservation UOM code and the primary reservation UOM are set to
4435 --the primary UOM code of the item since it is a dummy reservation
4436 l_reservation_record.reservation_uom_id := NULL;
4437 l_reservation_record.reservation_uom_code := l_item_primary_uom;
4438 l_reservation_record.primary_uom_id := NULL;
4439 l_reservation_record.primary_uom_code := l_item_primary_uom;
4440
4441 --Reservation quantity is set to the quantity after conversion to
4442 --the primary UOM code of the item
4443 l_reservation_record.primary_reservation_quantity := l_primary_res_qty;
4444 l_reservation_record.reservation_quantity := l_primary_res_qty;
4445 l_reservation_record.demand_source_header_id := p_dem_src_hdr_id;
4446 l_reservation_record.demand_source_line_id := p_dem_src_line_id;
4447 l_reservation_record.demand_source_type_id := p_dem_src_type_id;
4448
4449 l_reservation_record.ship_ready_flag := 2;
4450 l_reservation_record.attribute1 := NULL;
4451 l_reservation_record.attribute2 := NULL;
4452 l_reservation_record.attribute3 := NULL;
4453 l_reservation_record.attribute4 := NULL;
4454 l_reservation_record.attribute5 := NULL;
4455 l_reservation_record.attribute6 := NULL;
4456 l_reservation_record.attribute7 := NULL;
4457 l_reservation_record.attribute8 := NULL;
4458 l_reservation_record.attribute9 := NULL;
4459 l_reservation_record.attribute10 := NULL;
4460 l_reservation_record.attribute11 := NULL;
4461 l_reservation_record.attribute12 := NULL;
4462 l_reservation_record.attribute13 := NULL;
4463 l_reservation_record.attribute14 := NULL;
4464 l_reservation_record.attribute15 := NULL;
4465 l_reservation_record.attribute_category := NULL;
4466 l_reservation_record.lpn_id := p_lpn_id;
4467 l_reservation_record.pick_slip_number := NULL;
4468 l_reservation_record.lot_number_id := NULL;
4469 l_reservation_record.lot_number := l_lot_number;
4470 l_reservation_record.subinventory_id := NULL;
4471 l_reservation_record.subinventory_code := p_subinventory_code;
4472 l_reservation_record.locator_id := p_locator_id;
4473 l_reservation_record.supply_source_type_id := 13;
4474 l_reservation_record.supply_source_line_detail := NULL;
4475 l_reservation_record.supply_source_name := NULL;
4476 l_reservation_record.supply_source_header_id := p_dem_src_hdr_id;
4477 l_reservation_record.supply_source_line_id := p_dem_src_line_id;
4478 l_reservation_record.external_source_line_id := NULL;
4479 l_reservation_record.external_source_code := NULL;
4480 l_reservation_record.autodetail_group_id := NULL;
4481 l_reservation_record.demand_source_delivery := NULL;
4482 l_reservation_record.demand_source_name := NULL;
4483 l_reservation_record.requirement_date := p_requirement_date;
4484
4485 IF (l_debug = 1) THEN
4486 print_debug('CREATE_RETURN_RESV:**********Calling create_reservations with foll. parameters********');
4487 print_debug('CREATE_RETURN_RESV:org id: ' || p_org_id);
4488 print_debug('CREATE_RETURN_RESV:item id: ' || p_item_id);
4489 print_debug('CREATE_RETURN_RESV:rev: ' || p_revision);
4490 print_debug('CREATE_RETURN_RESV:UOM: ' || l_uom_code);
4491 print_debug('CREATE_RETURN_RESV:res qty: ' || l_primary_res_qty);
4492 print_debug('CREATE_RETURN_RESV:lot: ' || l_lot_number);
4493 print_debug('CREATE_RETURN_RESV:sub: ' || p_subinventory_code);
4494 print_debug('CREATE_RETURN_RESV:loc: ' || p_locator_id);
4495 print_debug('CREATE_RETURN_RESV:lpn_id: ' || p_lpn_id);
4496 print_debug('CREATE_RETURN_RESV:dem_src_type: ' || p_dem_src_type_id);
4497 print_debug('CREATE_RETURN_RESV:dem_src_hdr_id: ' || p_dem_src_hdr_id);
4498 print_debug('CREATE_RETURN_RESV:dem_src_line_id: ' || p_dem_src_line_id);
4499 END IF;
4500
4501 --Call the Create Reservations API
4502 INV_RESERVATION_PUB.CREATE_RESERVATION(
4503 x_return_status => x_return_status,
4504 x_msg_count => x_msg_count,
4505 x_msg_data => x_msg_data,
4506 x_serial_number => l_dummy_sn,
4507 x_quantity_reserved => l_quantity_reserved,
4508 x_reservation_id => l_reservation_id,
4509 p_api_version_number => 1.0,
4510 p_init_msg_lst => FND_API.G_FALSE,
4511 p_rsv_rec => l_reservation_record,
4512 p_partial_reservation_flag => FND_API.G_TRUE,
4513 p_force_reservation_flag => FND_API.G_TRUE,
4514 p_serial_number => l_dummy_sn,
4515 p_validation_flag => FND_API.G_TRUE);
4516
4517 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4518 IF (l_debug = 1) THEN
4519 print_debug('CREATE_RETURN_RESV:error creating reservation: status:' || x_return_status || 'mess:' || sqlerrm);
4520 END IF;
4521 RAISE FND_API.G_EXC_ERROR;
4522 END IF;
4523 IF (l_debug = 1) THEN
4524 print_debug('CREATE_RETURN_RESV:Reservation created successfully. Reservation Id: ' || l_reservation_id || ' . Quantity Reserved: ' || l_quantity_reserved);
4525 END IF;
4526 END IF; --IF l_create_res = TRUE THEN
4527
4528 IF (l_lot_control_code = 1) THEN
4529 EXIT;
4530 END IF;
4531 END LOOP;
4532
4533 IF (l_wms_po_j_higher) THEN
4534 CLOSE c_lots;
4535 ELSE
4536 CLOSE c_lots_old;
4537 END IF;
4538
4539 EXCEPTION
4540 WHEN fnd_api.g_exc_error THEN
4541 x_return_status := fnd_api.g_ret_sts_error;
4542 fnd_msg_pub.count_and_get
4543 ( p_count => x_msg_count,
4544 p_data => x_msg_data
4545 );
4546 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4547 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4548 IF (l_debug = 1) THEN
4549 print_debug('unxp:' || sqlerrm);
4550 END IF;
4551 fnd_msg_pub.count_and_get
4552 ( p_count => x_msg_count,
4553 p_data => x_msg_data
4554 );
4555 WHEN OTHERS THEN
4556 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4557 fnd_msg_pub.count_and_get
4558 ( p_count => x_msg_count,
4559 p_data => x_msg_data
4560 );
4561 END CREATE_RETURN_RESV;
4562
4563 END;
[Home] [Help]