[Home] [Help]
PACKAGE BODY: APPS.INV_LPN_RESERVATIONS_PVT
Source
1 PACKAGE BODY inv_lpn_reservations_pvt AS
2 /* $Header: INVRSVLB.pls 120.4.12010000.3 2008/10/06 17:23:06 pbonthu ship $*/
3
4 g_pkg_name VARCHAR2(30) := 'inv_lpn_reservations_pvt';
5 g_debug NUMBER;
6
7 --Create_LPN_Reservations
8 --
9 -- This API is designed to be called from the Reservations Form.
10 -- This procedure will create a separate reservation for each lot and
11 -- revision in that LPN.
12
13 PROCEDURE debug_print(p_message IN VARCHAR2, p_level IN NUMBER := 9) IS
14 --l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
15 BEGIN
16 inv_log_util.TRACE(p_message, g_pkg_name, p_level);
17 -- dbms_output.put_line(p_message);
18 END debug_print;
19
20 /***************************************************************************
21 * Bug#2402957: *
22 * Function is_lpn_reserved() checks if the lpn passed is reserved *
23 * against any other demand than the one passed. *
24 * 1. If demand is Sales/Internal Order or RMA then check if the LPN is *
25 * reserved against a demand other than the current demand line id *
26 * 2. If the demand is Account/Account Alias check if the LPN is reserved *
27 * against a demand other than the current header id. *
28 * 3. If the demand is Inventory/User defined. then check if the LPN is *
29 * reserved against a demand other than the current demand name. *
30 ***************************************************************************/
31 FUNCTION is_lpn_reserved(
32 p_item_id IN NUMBER
33 ,p_org_id IN NUMBER
34 ,p_demand_source_type_id IN NUMBER
35 ,p_demand_source_header_id IN NUMBER
36 ,p_demand_source_line_id IN NUMBER
37 ,p_demand_source_name IN VARCHAR2
38 ,p_lpn_id IN NUMBER
39 ) RETURN BOOLEAN IS
40 l_result NUMBER := 0;
41 BEGIN
42 IF p_demand_source_type_id IN (2,8,12) THEN
43 SELECT 1
44 INTO l_result
45 FROM dual
46 WHERE EXISTS (
47 SELECT 1
48 FROM mtl_reservations
49 WHERE organization_id = p_org_id
50 AND inventory_item_id = p_item_id
51 AND (demand_source_line_id <> p_demand_source_line_id
52 OR demand_source_line_id IS NULL)
53 AND lpn_id = p_lpn_id
54 );
55 ELSIF p_demand_source_type_id IN (3,6) THEN
56 SELECT 1
57 INTO l_result
58 FROM dual
59 WHERE EXISTS (
60 SELECT 1
61 FROM mtl_reservations
62 WHERE organization_id = p_org_id
63 AND inventory_item_id = p_item_id
64 AND (demand_source_header_id <> p_demand_source_header_id
65 OR demand_source_header_id IS NULL)
66 AND lpn_id = p_lpn_id
67 );
68 ELSE
69 SELECT 1
70 INTO l_result
71 FROM dual
72 WHERE EXISTS (
73 SELECT 1
74 FROM mtl_reservations
75 WHERE organization_id = p_org_id
76 AND inventory_item_id = p_item_id
77 AND (demand_source_name <> p_demand_source_name
78 OR demand_source_name IS NULL)
79 AND lpn_id = p_lpn_id
80 );
81 END IF;
82 IF l_result = 0 THEN
83 RETURN FALSE;
84 ELSE
85 RETURN TRUE;
86 END IF;
87 EXCEPTION
88 WHEN no_data_found THEN
89 RETURN FALSE;
90 END is_lpn_reserved;
91
92 PROCEDURE create_lpn_reservations(
93 x_return_status OUT NOCOPY VARCHAR2
94 , x_msg_count OUT NOCOPY NUMBER
95 , x_msg_data OUT NOCOPY VARCHAR2
96 , p_organization_id IN NUMBER
97 , p_inventory_item_id IN NUMBER
98 , p_demand_source_type_id IN NUMBER
99 , p_demand_source_header_id IN NUMBER
100 , p_demand_source_line_id IN NUMBER
101 , p_demand_source_name IN VARCHAR2
102 , p_need_by_date IN DATE
103 , p_lpn_id IN NUMBER
104 ) IS
105 l_api_name VARCHAR2(30) := 'create_lpn_reservations';
106 l_revision VARCHAR2(3);
107 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
108 l_lot_number VARCHAR2(80);
109 l_subinventory_code VARCHAR2(10);
110 l_locator_id NUMBER;
111 l_quantity NUMBER;
112 l_secondary_quantity NUMBER; -- INVCONV
113 l_increase_quantity NUMBER;
114 l_increase_secondary NUMBER; -- INVCONV
115 l_return_status VARCHAR2(1);
116 l_msg_count NUMBER;
117 l_msg_data VARCHAR2(240);
118 l_query_rec inv_reservation_global.mtl_reservation_rec_type;
119 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
120 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
121 l_rsv_count NUMBER;
122 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
123 l_error_code NUMBER;
124 l_primary_uom_code VARCHAR2(3);
125 l_secondary_uom_code VARCHAR2(3); -- INVCONV
126 l_tracking_quantity_ind VARCHAR2(30); -- INVCONV
127 l_quantity_reserved NUMBER;
128 l_secondary_quantity_reserved NUMBER; -- INVCONV
129 l_reservation_id NUMBER;
130 l_total_rsv_qty NUMBER;
131 l_total_secondary_rsv_qty NUMBER; -- INVCONV
132 l_revision_control_code NUMBER;
133 l_lot_control_code NUMBER;
134 l_revision_control BOOLEAN;
135 l_lot_control BOOLEAN;
136 l_tree_id NUMBER;
137 l_qoh NUMBER;
138 l_rqoh NUMBER;
139 l_qr NUMBER;
140 l_qs NUMBER;
141 l_atr NUMBER;
142 l_att NUMBER;
143 l_sqoh NUMBER; -- INVCONV
144 l_srqoh NUMBER; -- INVCONV
145 l_sqr NUMBER; -- INVCONV
146 l_sqs NUMBER; -- INVCONV
147 l_satr NUMBER; -- INVCONV
148 l_satt NUMBER; -- INVCONV
149 l_reserved_qty NUMBER;
150 l_ordered_qty NUMBER;
151 l_ord_qty NUMBER;
152 l_order_qty_uom VARCHAR2(3);
153 l_lpn_reserved_qty NUMBER;
154 l_lpn_already_resv BOOLEAN;
155
156 CURSOR c_item_controls IS
157 SELECT revision_qty_control_code
158 , lot_control_code
159 , primary_uom_code
160 , secondary_uom_code -- INVCONV
161 , tracking_quantity_ind -- INVCONV
162 FROM mtl_system_items
163 WHERE inventory_item_id = p_inventory_item_id
164 AND organization_id = p_organization_id;
165
166 CURSOR c_lpn_contents IS
167 SELECT revision
168 , lot_number
169 , subinventory_code
170 , locator_id
171 , SUM(primary_transaction_quantity)
172 , SUM(secondary_transaction_quantity) -- INVCONV
173 FROM mtl_onhand_quantities_detail
174 WHERE lpn_id = p_lpn_id
175 GROUP BY revision, lot_number, subinventory_code, locator_id;
176
177 --bug#2402957. added the cursor c_lpn_qty.
178 CURSOR c_lpn_qty IS
179 SELECT SUM(primary_transaction_quantity)
180 FROM mtl_onhand_quantities_detail
181 WHERE lpn_id = p_lpn_id;
182
183 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
184 BEGIN
185 IF (l_debug = 1) THEN
186 debug_print('Enter Create_LPN_Reservations');
187 debug_print('OrgID = ' || p_organization_id || ' : ItemID = ' || p_inventory_item_id || ' : LPN ID = ' || p_lpn_id);
188 debug_print('Demand Source - Header ID = ' || p_demand_source_header_id || ' : Line ID = ' || p_demand_source_line_id || ' : Name = ' || p_demand_source_name || ' : Type = ' || p_demand_source_type_id);
189 END IF;
190 SAVEPOINT entire_lpn;
191 --validate input values
192 IF p_organization_id IS NULL
193 OR p_inventory_item_id IS NULL
194 OR p_demand_source_type_id IS NULL
195 OR p_lpn_id IS NULL THEN
196 IF (l_debug = 1) THEN
197 debug_print('Missing input parameters');
198 END IF;
199
200 RAISE fnd_api.g_exc_unexpected_error;
201 END IF;
202
203 --bug#2402957. call is_lpn_reserved to see if lpn is reserved for some other demand.
204 l_lpn_already_resv := is_lpn_reserved(
205 p_org_id => p_organization_id
206 , p_item_id => p_inventory_item_id
207 , p_demand_source_type_id => p_demand_source_type_id
208 , p_demand_source_header_id => p_demand_source_header_id
209 , p_demand_source_line_id => p_demand_source_line_id
210 , p_demand_source_name => p_demand_source_name
211 , p_lpn_id => p_lpn_id
212 );
213 IF l_lpn_already_resv THEN
214 IF (l_debug = 1) THEN
215 debug_print('Error: LPN is reserved for some other demand line');
216 END IF;
217 fnd_message.set_name('INV', 'INV_CANNOT_RESERVE_LPN');
218 fnd_msg_pub.ADD;
219 RAISE fnd_api.g_exc_error;
220 END IF;
221
222 OPEN c_item_controls;
223 -- INVCONV - incorporate secondary uom and tracking quantity below
224 FETCH c_item_controls INTO l_revision_control_code, l_lot_control_code,
225 l_primary_uom_code, l_secondary_uom_code, l_tracking_quantity_ind;
226 CLOSE c_item_controls;
227
228 IF l_revision_control_code = 2 THEN
229 l_revision_control := TRUE;
230 ELSE
231 l_revision_control := FALSE;
232 END IF;
233
234 IF l_lot_control_code = 2 THEN
235 l_lot_control := TRUE;
236 ELSE
237 l_lot_control := FALSE;
238 END IF;
239
240 --bug#2402957. get orderline qty, current reservation qty for the order line and current qty
241 --reserved for the orderline by the current lpn.
242 BEGIN
243 SELECT nvl(ordered_quantity,0),order_quantity_uom
244 INTO l_ord_qty,l_order_qty_uom
245 FROM oe_order_lines_all
246 WHERE line_id = p_demand_source_line_id;
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 l_ord_qty := 0;
250 END;
251 --convert ordered qty into primary UOM.
252 IF l_ord_qty <> 0 THEN
253 l_ordered_qty := nvl(inv_convert.inv_um_convert(
254 item_id => p_inventory_item_id
255 , PRECISION => NULL
256 , from_quantity => l_ord_qty
257 , from_unit => l_order_qty_uom
258 , to_unit => l_primary_uom_code
259 , from_name => NULL
260 , to_name => NULL
261 ), 0);
262 END IF;
263
264 -- {{
265 -- R12: Create an LPN reservation for a sales order line that
266 -- is not pick released, using an LPN that is fully available
267 --
268 -- Create an LPN reservation for a sales order line that is
269 -- not pick released, using an LPN that is partially crossdock-
270 -- pegged to another order line - should fail
271 --
272 -- Create an LPN reservation for a sales order line that is
273 -- that is partially pegged to an LPN. Should be able to
274 -- peg the remaining order qty to the remaining LPN qty }}
275 --
276 SELECT nvl(sum(primary_reservation_quantity),0)
277 INTO l_reserved_qty
278 FROM mtl_reservations
279 WHERE demand_source_line_id = p_demand_source_line_id
280 AND organization_id = p_organization_id
281 AND inventory_item_id = p_inventory_item_id;
282
283 SELECT nvl(sum(primary_reservation_quantity),0)
284 INTO l_lpn_reserved_qty
285 FROM mtl_reservations
286 WHERE demand_source_line_id = p_demand_source_line_id
287 AND lpn_id = p_lpn_id
288 AND organization_id = p_organization_id
289 AND inventory_item_id = p_inventory_item_id
290 AND demand_source_line_detail IS NULL;
291
292 OPEN c_lpn_qty;
293 FETCH c_lpn_qty INTO l_quantity;
294 CLOSE c_lpn_qty;
295
296 IF (l_debug = 1) THEN
297 debug_print('Order Qty in Order UOM = ' || l_ord_qty);
298 debug_print('Order Qty in Primary UOM = ' || l_ordered_qty);
299 debug_print('LPN Qty in Primary UOM = ' || l_quantity);
300 debug_print('Reserved Qty in Primary UOM = ' || l_reserved_qty);
301 debug_print('LPN Reserved Qty in Primary UOM = ' || l_lpn_reserved_qty);
302 END IF;
303
304 --bug#2402957. if the reservation is for a sales/internal order. order qty will be > 0.
305 IF l_ordered_qty <> 0 then
306 IF l_ordered_qty = l_reserved_qty THEN
307 --show error that the order is completely reserved.
308 IF (l_debug = 1) THEN
309 debug_print('Error: Order Line completely reserved');
310 END IF;
311 fnd_message.set_name('INV', 'INV_CANNOT_CREATE_RESERVATION');
312 fnd_msg_pub.ADD;
313 RAISE fnd_api.g_exc_error;
314 ELSIF (l_ordered_qty - l_reserved_qty + l_lpn_reserved_qty < l_quantity) THEN
315 --error as lpn has more qty than order line.
316 IF (l_debug = 1) THEN
317 debug_print('Error: LPN Qty > Order Line Qty');
318 END IF;
319 fnd_message.set_name('INV', 'INV_LPN_QTY_GREATER');
320 fnd_msg_pub.ADD;
321 RAISE fnd_api.g_exc_error;
322 END IF;
323 END IF;
324
325 -- create the quantity tree
326 inv_quantity_tree_pvt.create_tree(
327 p_api_version_number => 1.0
328 , p_init_msg_lst => fnd_api.g_true
329 , x_return_status => l_return_status
330 , x_msg_count => l_msg_count
331 , x_msg_data => l_msg_data
332 , p_organization_id => p_organization_id
333 , p_inventory_item_id => p_inventory_item_id
334 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
335 , p_is_revision_control => l_revision_control
336 , p_is_lot_control => l_lot_control
337 , p_is_serial_control => FALSE
338 , p_asset_sub_only => FALSE
339 , p_include_suggestion => TRUE
340 , p_demand_source_type_id => p_demand_source_type_id
341 , p_demand_source_header_id => p_demand_source_header_id
342 , p_demand_source_line_id => p_demand_source_line_id
343 , p_demand_source_name => p_demand_source_name
344 , p_demand_source_delivery => NULL
345 , p_lot_expiration_date => SYSDATE -- Bug#2716563
346 , x_tree_id => l_tree_id
347 );
348
349 IF l_return_status = fnd_api.g_ret_sts_error THEN
350 IF (l_debug = 1) THEN
351 debug_print('Error creating quantity tree');
352 END IF;
353
354 RAISE fnd_api.g_exc_error;
355 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
356 IF (l_debug = 1) THEN
357 debug_print('Unexpected error creating quantity tree');
358 END IF;
359
360 RAISE fnd_api.g_exc_unexpected_error;
361 END IF;
362
363 OPEN c_lpn_contents;
364
365 IF (l_debug = 1) THEN
366 debug_print('Looping through each LPN contents');
367 END IF;
368
369 --for each content record in the lpn
370 LOOP
371 -- INVCONV - secondary_quantity below
372 FETCH c_lpn_contents INTO l_revision, l_lot_number, l_subinventory_code, l_locator_id,
373 l_quantity, l_secondary_quantity;
374 EXIT WHEN c_lpn_contents%NOTFOUND;
375
376 IF (l_debug = 1) THEN
377 debug_print('Fetched a LPN Record');
378 debug_print(' --> Revision : '|| l_revision);
379 debug_print(' --> Lot Number : '|| l_lot_number);
380 debug_print(' --> SubInventory : '|| l_subinventory_code);
381 debug_print(' --> Locator ID : '|| l_locator_id);
382 debug_print(' --> Current Qty : '|| l_quantity);
383 debug_print(' --> Secondary Qty : '|| l_secondary_quantity);
384 END IF;
385
386 --Query to see if this reservation already exists
387 l_query_rec.organization_id := p_organization_id;
388 l_query_rec.inventory_item_id := p_inventory_item_id;
389 l_query_rec.demand_source_type_id := p_demand_source_type_id;
390 l_query_rec.demand_source_header_id := p_demand_source_header_id;
391 l_query_rec.demand_source_line_id := p_demand_source_line_id;
392 l_query_rec.demand_source_name := p_demand_source_name;
393 l_query_rec.revision := l_revision;
394 l_query_rec.lot_number := l_lot_number;
395 l_query_rec.subinventory_code := l_subinventory_code;
396 l_query_rec.locator_id := l_locator_id;
397 l_query_rec.lpn_id := p_lpn_id;
398 l_query_rec.demand_source_line_detail := NULL;
399
400 IF (l_debug = 1) THEN
401 debug_print('Querying for existing reservations');
402 END IF;
403
404 inv_reservation_pvt.query_reservation(
405 p_api_version_number => 1.0
406 , p_init_msg_lst => fnd_api.g_false
407 , x_return_status => l_return_status
408 , x_msg_count => l_msg_count
409 , x_msg_data => l_msg_data
410 , p_query_input => l_query_rec
411 , x_mtl_reservation_tbl => l_rsv_tbl
412 , x_mtl_reservation_tbl_count => l_rsv_count
413 , x_error_code => l_error_code
414 );
415
416 IF l_return_status = fnd_api.g_ret_sts_error THEN
417 IF (l_debug = 1) THEN
418 debug_print('Error in query_reservation');
419 END IF;
420
421 RAISE fnd_api.g_exc_error;
422 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
423 IF (l_debug = 1) THEN
424 debug_print('Unexpected error in query_reservation');
425 END IF;
426
427 RAISE fnd_api.g_exc_unexpected_error;
428 END IF;
429
430 -- If reservation exists
431 IF l_rsv_count >= 1 THEN
432 IF (l_debug = 1) THEN
433 debug_print('Reservation exists');
434 END IF;
435
436 l_total_rsv_qty := 0;
437 FOR l_count IN 1 .. l_rsv_count LOOP
438 l_total_rsv_qty := l_total_rsv_qty + l_rsv_tbl(l_count).primary_reservation_quantity;
439 -- INVCONV BEGIN
440 IF l_tracking_quantity_ind = 'PS' THEN
441 l_total_secondary_rsv_qty :=
442 NVL(l_total_secondary_rsv_qty,0) + l_rsv_tbl(l_count).secondary_reservation_quantity;
443 END IF;
444 -- INVCONV END
445 END LOOP;
446 l_increase_quantity := l_quantity - l_total_rsv_qty;
447 -- INVCONV BEGIN
448 IF l_tracking_quantity_ind = 'PS' THEN
449 l_increase_secondary := l_secondary_quantity - l_total_secondary_rsv_qty;
450 END IF;
451 -- INVCONV END
452
453 -- If not all the quantity is reserved, increase reservation quantity
454 IF l_increase_quantity > 0 THEN
455 IF (l_debug = 1) THEN
456 debug_print('Trying to increase the Reservation Qty by '|| l_increase_quantity);
457 debug_print('Trying to increase the Secondary Rsv Qty by '|| l_increase_secondary); -- INVCONV
458 END IF;
459
460 --query quantity tree to make sure quantity is available to increase reservations
461 inv_quantity_tree_pvt.query_tree(
462 p_api_version_number => 1.0
463 , p_init_msg_lst => fnd_api.g_true
464 , x_return_status => l_return_status
465 , x_msg_count => l_msg_count
466 , x_msg_data => l_msg_data
467 , p_tree_id => l_tree_id
468 , p_revision => l_revision
469 , p_lot_number => l_lot_number
470 , p_subinventory_code => l_subinventory_code
471 , p_locator_id => l_locator_id
472 , x_qoh => l_qoh
473 , x_rqoh => l_rqoh
474 , x_qr => l_qr
475 , x_qs => l_qs
476 , x_att => l_att
477 , x_atr => l_atr
478 , x_sqoh => l_sqoh -- INVCONV
479 , x_srqoh => l_srqoh -- INVCONV
480 , x_sqr => l_sqr -- INVCONV
481 , x_sqs => l_sqs -- INVCONV
482 , x_satt => l_satt -- INVCONV
483 , x_satr => l_satr -- INVCONV
484 , p_transfer_subinventory_code => NULL
485 , p_cost_group_id => NULL
486 , p_lpn_id => p_lpn_id
487 , p_transfer_locator_id => NULL
488 );
489
490 IF l_return_status = fnd_api.g_ret_sts_error THEN
491 IF (l_debug = 1) THEN
492 debug_print('Error from query_tree');
493 END IF;
494
495 RAISE fnd_api.g_exc_error;
496 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
497 IF (l_debug = 1) THEN
498 debug_print('Unexpected error from query_tree');
499 END IF;
500
501 RAISE fnd_api.g_exc_unexpected_error;
502 END IF;
503
504 --Bug#2402957:
505 -- If (ATR + AlreadyReserved) <> LPN Qty => LPN is reserved for some other demand, so error.
506 -- This is possible only when there are Higher Level Reservations. Otherwise it will
507 -- be caught at is_lpn_reserved
508 IF (l_quantity <> (l_atr + l_total_rsv_qty)) THEN
509 --show error that lpn is reserved for some other demand.
510 IF (l_debug = 1) THEN
511 debug_print('Error: LPN is reserved for some other demand line');
512 END IF;
513 fnd_message.set_name('INV', 'INV_CANNOT_RESERVE_LPN');
514 fnd_msg_pub.ADD;
515 RAISE fnd_api.g_exc_error;
516 END IF;
517
518 l_increase_quantity := l_atr + l_rsv_tbl(1).primary_reservation_quantity;
519 -- INVCONV BEGIN
520 IF l_tracking_quantity_ind = 'PS' THEN
521 l_increase_secondary := l_satr + l_rsv_tbl(1).secondary_reservation_quantity;
522 END IF;
523 -- INVCONV END
524 IF (l_debug = 1) THEN
525 debug_print('New Reservation Qty = '|| l_increase_quantity);
526 debug_print('New Secondary Reservation Qty = '|| l_increase_secondary);
527 END IF;
528 IF l_increase_quantity > 0 THEN
529 l_rsv_rec := l_rsv_tbl(1);
530 l_rsv_rec.primary_reservation_quantity := l_increase_quantity;
531 l_rsv_rec.reservation_quantity := NULL;
532 l_rsv_rec.secondary_reservation_quantity := l_increase_secondary; -- INVCONV
533
534 -- Call update reservation to increase quantity
535 IF (l_debug = 1) THEN
536 debug_print('Calling Update Reservation');
537 END IF;
538
539 inv_reservation_pvt.update_reservation(
540 p_api_version_number => 1.0
541 , p_init_msg_lst => fnd_api.g_false
542 , x_return_status => l_return_status
543 , x_msg_count => l_msg_count
544 , x_msg_data => l_msg_data
545 , p_original_rsv_rec => l_rsv_tbl(1)
546 , p_to_rsv_rec => l_rsv_rec
547 , p_original_serial_number => l_dummy_sn
548 , p_to_serial_number => l_dummy_sn
549 , p_validation_flag => fnd_api.g_true
550 );
551
552 IF l_return_status = fnd_api.g_ret_sts_error THEN
553 IF (l_debug = 1) THEN
554 debug_print('Error from update_reservation');
555 END IF;
556
557 RAISE fnd_api.g_exc_error;
558 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
559 IF (l_debug = 1) THEN
560 debug_print('Unexpected error from update_reservation');
561 END IF;
562
563 RAISE fnd_api.g_exc_unexpected_error;
564 END IF;
565 END IF; -- if new increase quantity > 0
566 END IF; -- if increase quantity > 0
567 ELSE --Else, create new reservation
568 IF (l_debug = 1) THEN
569 debug_print('Reservation doesnt exists');
570 END IF;
571
572 inv_quantity_tree_pvt.query_tree(
573 p_api_version_number => 1.0
574 , p_init_msg_lst => fnd_api.g_true
575 , x_return_status => l_return_status
576 , x_msg_count => l_msg_count
577 , x_msg_data => l_msg_data
578 , p_tree_id => l_tree_id
579 , p_revision => l_revision
580 , p_lot_number => l_lot_number
581 , p_subinventory_code => l_subinventory_code
582 , p_locator_id => l_locator_id
583 , x_qoh => l_qoh
584 , x_rqoh => l_rqoh
585 , x_qr => l_qr
586 , x_qs => l_qs
587 , x_att => l_att
588 , x_atr => l_atr
589 , p_transfer_subinventory_code => NULL
590 , p_cost_group_id => NULL
591 , p_lpn_id => p_lpn_id
592 , p_transfer_locator_id => NULL
593 );
594
595 IF l_return_status = fnd_api.g_ret_sts_error THEN
596 IF (l_debug = 1) THEN
597 debug_print('Error in query_tree');
598 END IF;
599
600 RAISE fnd_api.g_exc_error;
601 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
602 IF (l_debug = 1) THEN
603 debug_print('Unexpected error in query_tree');
604 END IF;
605
606 RAISE fnd_api.g_exc_unexpected_error;
607 END IF;
608
609 --Bug#2402957:
610 -- If ATR <> LPN Qty => LPN is also reserved for some other demand, so error.
611 -- This is possible only when there are Higher Level Reservations. Otherwise it will
612 -- be caught at is_lpn_reserved
613 IF l_quantity <> l_atr THEN
614 --show error that lpn is reserved for some other demand.
615 IF (l_debug = 1) THEN
616 debug_print('Error: LPN is reserved for some other order line');
617 END IF;
618 fnd_message.set_name('INV', 'INV_CANNOT_RESERVE_LPN');
619 fnd_msg_pub.ADD;
620 RAISE fnd_api.g_exc_error;
621 END IF;
622
623 IF (l_debug = 1) THEN
624 debug_print('New Reservation Qty = '|| l_quantity);
625 debug_print('Secondary Reservation Qty = '|| l_secondary_quantity);
626 END IF;
627 IF l_quantity > 0 THEN
628 --create reservation for available quantity
629 l_rsv_rec.reservation_id := NULL; -- cannot know
630 l_rsv_rec.requirement_date := p_need_by_date;
631 l_rsv_rec.organization_id := p_organization_id;
632 l_rsv_rec.inventory_item_id := p_inventory_item_id;
633 l_rsv_rec.demand_source_type_id := p_demand_source_type_id;
634 l_rsv_rec.demand_source_name := p_demand_source_name;
635 l_rsv_rec.demand_source_header_id := p_demand_source_header_id;
636 l_rsv_rec.demand_source_line_id := p_demand_source_line_id;
637 l_rsv_rec.demand_source_delivery := NULL;
638 l_rsv_rec.primary_uom_code := l_primary_uom_code;
639 l_rsv_rec.primary_uom_id := NULL;
640 l_rsv_rec.secondary_uom_code := l_secondary_uom_code; -- INVCONV
641 l_rsv_rec.secondary_uom_id := NULL; -- INVCONV
642 l_rsv_rec.reservation_uom_code := l_primary_uom_code;
643 l_rsv_rec.reservation_uom_id := NULL;
644 l_rsv_rec.primary_reservation_quantity := l_quantity;
645 l_rsv_rec.secondary_reservation_quantity:= l_secondary_quantity;
646 l_rsv_rec.reservation_quantity := l_quantity;
647 l_rsv_rec.autodetail_group_id := NULL;
648 l_rsv_rec.external_source_code := NULL;
649 l_rsv_rec.external_source_line_id := NULL;
650 l_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
651 l_rsv_rec.supply_source_header_id := NULL;
652 l_rsv_rec.supply_source_line_id := NULL;
653 l_rsv_rec.supply_source_name := NULL;
654 l_rsv_rec.supply_source_line_detail := NULL;
655 l_rsv_rec.revision := l_revision;
656 l_rsv_rec.lot_number := l_lot_number;
657 l_rsv_rec.subinventory_code := l_subinventory_code;
658 l_rsv_rec.subinventory_id := NULL;
659 l_rsv_rec.locator_id := l_locator_id;
660 l_rsv_rec.lot_number_id := NULL;
661 l_rsv_rec.pick_slip_number := NULL;
662 l_rsv_rec.lpn_id := p_lpn_id;
663 l_rsv_rec.attribute_category := NULL;
664 l_rsv_rec.attribute1 := NULL;
665 l_rsv_rec.attribute2 := NULL;
666 l_rsv_rec.attribute3 := NULL;
667 l_rsv_rec.attribute4 := NULL;
668 l_rsv_rec.attribute5 := NULL;
669 l_rsv_rec.attribute6 := NULL;
670 l_rsv_rec.attribute7 := NULL;
671 l_rsv_rec.attribute8 := NULL;
672 l_rsv_rec.attribute9 := NULL;
673 l_rsv_rec.attribute10 := NULL;
674 l_rsv_rec.attribute11 := NULL;
675 l_rsv_rec.attribute12 := NULL;
676 l_rsv_rec.attribute13 := NULL;
677 l_rsv_rec.attribute14 := NULL;
678 l_rsv_rec.attribute15 := NULL;
679 l_rsv_rec.ship_ready_flag := NULL;
680 l_rsv_rec.detailed_quantity := 0;
681 -- INVCONV BEGIN
682 IF l_tracking_quantity_ind = 'PS' THEN
683 l_rsv_rec.secondary_detailed_quantity := 0;
684 END IF;
685 -- INVCONV END
686
687 IF (l_debug = 1) THEN
688 debug_print('Calling create_reservation');
689 END IF;
690
691 -- INVCONV - Upgrade call for Inventory Convergence
692 inv_reservation_pvt.create_reservation(
693 p_api_version_number => 1.0
694 , p_init_msg_lst => fnd_api.g_false
695 , x_return_status => l_return_status
696 , x_msg_count => l_msg_count
697 , x_msg_data => l_msg_data
698 , p_rsv_rec => l_rsv_rec
699 , p_serial_number => l_dummy_sn
700 , x_serial_number => l_dummy_sn
701 , p_partial_reservation_flag => fnd_api.g_true
702 , p_force_reservation_flag => fnd_api.g_false
703 , p_validation_flag => fnd_api.g_true
704 , x_quantity_reserved => l_quantity_reserved
705 , x_secondary_quantity_reserved => l_secondary_quantity_reserved --INVCONV
706 , x_reservation_id => l_reservation_id
707 );
708
709 IF l_return_status = fnd_api.g_ret_sts_error THEN
710 IF (l_debug = 1) THEN
711 debug_print('Error in create_reservation');
712 END IF;
713
714 RAISE fnd_api.g_exc_error;
715 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
716 IF (l_debug = 1) THEN
717 debug_print('Unexpected error in create_reservation');
718 END IF;
719
720 RAISE fnd_api.g_exc_unexpected_error;
721 END IF;
722 END IF; -- if quantity > 0
723 END IF; -- if reservation count > 0
724 END LOOP;
725
726 CLOSE c_lpn_contents;
727 x_return_status := fnd_api.g_ret_sts_success;
728
729 IF (l_debug = 1) THEN
730 debug_print('Exit Create_LPN_Reservations');
731 END IF;
732 EXCEPTION
733 WHEN fnd_api.g_exc_error THEN
734 x_return_status := fnd_api.g_ret_sts_error;
735 ROLLBACK TO entire_lpn; --bug#2402957.
736 IF (l_debug = 1) THEN
737 debug_print('Error in Create_LPN_Reservations');
738 END IF;
739 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded=> 'F');
740 WHEN fnd_api.g_exc_unexpected_error THEN
741 x_return_status := fnd_api.g_ret_sts_unexp_error;
742
743 IF (l_debug = 1) THEN
744 debug_print('Unexpected error in Create_LPN_Reservations');
745 END IF;
746 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded=> 'F');
747 WHEN OTHERS THEN
748 x_return_status := fnd_api.g_ret_sts_unexp_error;
749
750 IF (l_debug = 1) THEN
751 debug_print('Other error in Create_LPN_Reservations');
752 END IF;
753
754 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
755 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
756 END IF;
757 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded=> 'F');
758 END create_lpn_reservations;
759
760 --Transfer_LPN_Reservations
761 --
762 -- This API is designed to be called from the mobile subinventory transfer
763 -- and putaway forms. This procedure will transfer all the reservations
764 -- for a given LPN from the current subinventory and locator to a new
765 -- subinventory and locator. This is useful for moving reserved LPNs around
766 -- the warehouse.
767 PROCEDURE transfer_lpn_reservations(
768 x_return_status OUT NOCOPY VARCHAR2
769 , x_msg_count OUT NOCOPY NUMBER
770 , x_msg_data OUT NOCOPY VARCHAR2
771 , p_organization_id IN NUMBER
772 , p_inventory_item_id IN NUMBER DEFAULT NULL
773 , p_lpn_id IN NUMBER
774 , p_to_subinventory_code IN VARCHAR2
775 , p_to_locator_id IN NUMBER
776 ) IS
777 l_api_name VARCHAR2(30) := 'transfer_lpn_reservations';
778 l_debug NUMBER;
779 l_reservable_type NUMBER; --Bug 6007873
780 l_lpn_controlled_flag NUMBER; --Bug 6007873
781 BEGIN
782 IF (g_debug IS NULL) THEN
783 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
784 END IF;
785
786 l_debug := g_debug;
787
788 IF (l_debug = 1) THEN
789 debug_print('In transfer_lpn_reservations');
790 debug_print('p_organization_id = ' || p_organization_id);
791 debug_print('p_inventory_item_id = ' || p_inventory_item_id);
792 debug_print('p_lpn_id = ' || p_lpn_id);
793 debug_print('p_to_subinventory_code = ' || p_to_subinventory_code);
794 debug_print('p_to_locator_id = ' || p_to_locator_id);
795 END IF;
796
797 --Bug 6007873
798 SELECT reservable_type,lpn_controlled_flag
799 INTO l_reservable_type, l_lpn_controlled_flag
800 FROM mtl_secondary_inventories
801 WHERE secondary_inventory_name LIKE (p_to_subinventory_code)
802 AND organization_id = p_organization_id;
803
804 IF (l_debug = 1) THEN
805 debug_print('transfer_lpn_reservations:: l_reservable_type '|| l_reservable_type);
806 debug_print('transfer_lpn_reservations:: l_lpn_controlled_flag '|| l_lpn_controlled_flag);
807 END IF;
808
809 ----Bug 6007873 Added if
810 IF l_reservable_type = 1 THEN --transfer Sub is reservable, keep the reservation record
811 IF l_lpn_controlled_flag = 1 THEN --transfer Sub is LPN controlled, keep the lpn_id stamping
812 IF p_inventory_item_id IS NOT NULL THEN
813 UPDATE mtl_reservations
814 SET subinventory_code = p_to_subinventory_code,
815 locator_id = p_to_locator_id
816 WHERE organization_id = p_organization_id
817 AND inventory_item_id = p_inventory_item_id
818 AND lpn_id IN (SELECT lpn_id
819 FROM wms_license_plate_numbers
820 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id); --NESTED LPN ER 7307189
821 ELSE
822 UPDATE mtl_reservations
823 SET subinventory_code = p_to_subinventory_code,
824 locator_id = p_to_locator_id
825 WHERE organization_id = p_organization_id
826 AND lpn_id IN (SELECT lpn_id
827 FROM wms_license_plate_numbers
828 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id); --NESTED LPN ER 7307189
829 END IF;
830 ELSE --transfer Sub is Non-LPN controlled, null out the lpn_id stamping as LPN will be unpacked.
831 IF p_inventory_item_id IS NOT NULL THEN
832 UPDATE mtl_reservations
833 SET subinventory_code = p_to_subinventory_code,
834 locator_id = p_to_locator_id,
835 lpn_id = NULL
836 WHERE organization_id = p_organization_id
837 AND inventory_item_id = p_inventory_item_id
838 AND lpn_id IN (SELECT lpn_id
839 FROM wms_license_plate_numbers
840 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id); --NESTED LPN ER 7307189
841 ELSE
842 UPDATE mtl_reservations
843 SET subinventory_code = p_to_subinventory_code,
844 locator_id = p_to_locator_id,
845 lpn_id = NULL
846 WHERE organization_id = p_organization_id
847 AND lpn_id IN (SELECT lpn_id
848 FROM wms_license_plate_numbers
849 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id); --NESTED LPN ER 7307189
850 END IF;
851 END IF;
852 ELSE --Bug 6007873
853 -- sub is non-reservable
854 --need to delete the reservations
855 IF p_inventory_item_id IS NOT NULL THEN
856 DELETE FROM mtl_reservations
857 WHERE organization_id = p_organization_id
858 AND inventory_item_id = p_inventory_item_id
859 AND lpn_id IN (SELECT lpn_id
860 FROM wms_license_plate_numbers
861 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id); --NESTED LPN ER 7307189
862 ELSE
863 DELETE FROM mtl_reservations
864 WHERE organization_id = p_organization_id
865 AND lpn_id IN (SELECT lpn_id
866 FROM wms_license_plate_numbers
867 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id ); --NESTED LPN ER 7307189
868 END IF;
869 END IF;
870
871 /*** {{ R12 Enhanced reservations code changes ***/
872 -- call inv_reservation_pvt.transfer_serial_rsv_in_LPN to
873 -- and pass the outermost_lpn_id to transfer any serial
874 -- reservations with no lpn in the same reservation in that lpn.
875 inv_reservation_pvt.transfer_serial_rsv_in_LPN
876 (
877 x_return_status => x_return_status
878 , x_msg_count => x_msg_count
879 , x_msg_data => x_msg_data
880 , p_organization_id => p_organization_id
881 , p_inventory_item_id => p_inventory_item_id
882 , p_lpn_id => null
883 , p_outermost_lpn_id => p_lpn_id
884 , p_to_subinventory_code => p_to_subinventory_code
885 , p_to_locator_id => p_to_locator_id
886 );
887
888 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
889 IF (l_debug = 1) THEN
890 debug_print('Error return status from transfer_serial_rsv_in_LPN');
891 END IF;
892
893 RAISE fnd_api.g_exc_error;
894 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
895 IF (l_debug = 1) THEN
896 debug_print('Unexpected return status from transfer_serial_rsv_in_LPN');
897 END IF;
898
899 RAISE fnd_api.g_exc_unexpected_error;
900 END IF;
901
902 /*** End R12 }} ***/
903
904 inv_quantity_tree_pvt.clear_quantity_cache;
905
906 x_return_status := fnd_api.g_ret_sts_success;
907 EXCEPTION
908 WHEN fnd_api.g_exc_error THEN
909 x_return_status := fnd_api.g_ret_sts_error;
910 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
911 WHEN fnd_api.g_exc_unexpected_error THEN
912 x_return_status := fnd_api.g_ret_sts_unexp_error;
913 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
914 WHEN OTHERS THEN
915 x_return_status := fnd_api.g_ret_sts_unexp_error;
916
917 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
918 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
919 END IF;
920 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
921 END transfer_lpn_reservations;
922
923
924 -- ER 7307189 changes start
925
926 --transfer_reserved_lpn_contents
927 --
928 -- This API is designed to be called from the mobile Move any LPN (transfer contents scenario) .
929 -- This procedure will transfer all the reservations
930 -- from lpn to transfer lpn ,current subinventory and locator to a new
931 -- subinventory and locator. This is useful for moving reserved LPNs around
932 -- the warehouse.
933 PROCEDURE transfer_reserved_lpn_contents(
934 x_return_status OUT NOCOPY VARCHAR2
935 , x_msg_count OUT NOCOPY NUMBER
936 , x_msg_data OUT NOCOPY VARCHAR2
937 , p_organization_id IN NUMBER
938 , p_inventory_item_id IN NUMBER DEFAULT NULL
939 , p_lpn_id IN NUMBER
940 , p_transfer_lpn_id IN NUMBER
941 , p_to_subinventory_code IN VARCHAR2
942 , p_to_locator_id IN NUMBER
943 ) IS
944 l_api_name VARCHAR2(30) := 'transfer_reserved_lpn_contents';
945 l_debug NUMBER;
946 l_reservable_type NUMBER;
947 l_lpn_controlled_flag NUMBER;
948 BEGIN
949 IF (g_debug IS NULL) THEN
950 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
951 END IF;
952
953 l_debug := g_debug;
954
955 IF (l_debug = 1) THEN
956 debug_print('In transfer_reserved_lpn_contents');
957 debug_print('transfer_reserved_lpn_contents:p_organization_id = ' || p_organization_id);
958 debug_print('transfer_reserved_lpn_contents:p_lpn_id = ' || p_lpn_id);
959 debug_print('transfer_reserved_lpn_contents:p_to_subinventory_code = ' || p_to_subinventory_code);
960 debug_print('transfer_reserved_lpn_contents:p_to_locator_id = ' || p_to_locator_id);
961 debug_print('transfer_reserved_lpn_contents:p_transfer_lpn_id = ' || p_transfer_lpn_id);
962 debug_print('transfer_reserved_lpn_contents:p_inventory_item_id = ' || p_inventory_item_id);
963 END IF;
964
965 SELECT reservable_type,lpn_controlled_flag
966 INTO l_reservable_type, l_lpn_controlled_flag
967 FROM mtl_secondary_inventories
968 WHERE secondary_inventory_name LIKE (p_to_subinventory_code)
969 AND organization_id = p_organization_id;
970
971 IF (l_debug = 1) THEN
972 debug_print('transfer_reserved_lpn_contents:: l_reservable_type '|| l_reservable_type);
973 debug_print('transfer_reserved_lpn_contents:: l_lpn_controlled_flag '|| l_lpn_controlled_flag);
974 END IF;
975
976
977 IF l_reservable_type = 1 THEN --transfer Sub is reservable, keep the reservation record
978 IF l_lpn_controlled_flag = 1 THEN --transfer Sub is LPN controlled, keep the lpn_id stamping
979 IF p_inventory_item_id IS NOT NULL THEN
980 UPDATE mtl_reservations
981 SET subinventory_code = p_to_subinventory_code
982 , locator_id = p_to_locator_id
983 , lpn_id=p_transfer_lpn_id
984 WHERE organization_id = p_organization_id
985 AND inventory_item_id = p_inventory_item_id
986 AND lpn_id IN (SELECT lpn_id
987 FROM wms_license_plate_numbers
988 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id ) --nested lpn
989 AND demand_source_line_detail IS NULL;
990 ELSE
991 UPDATE mtl_reservations
992 SET subinventory_code = p_to_subinventory_code
993 , locator_id = p_to_locator_id
994 , lpn_id=p_transfer_lpn_id
995 WHERE organization_id = p_organization_id
996 AND lpn_id IN (SELECT lpn_id
997 FROM wms_license_plate_numbers
998 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id ) --nested lpn
999 AND demand_source_line_detail IS NULL;
1000 END IF;
1001 ELSE --transfer Sub is Non-LPN controlled, null out the lpn_id stamping as LPN will be unpacked.
1002 IF p_inventory_item_id IS NOT NULL THEN
1003 UPDATE mtl_reservations
1004 SET subinventory_code = p_to_subinventory_code
1005 , locator_id = p_to_locator_id
1006 , lpn_id=NULL
1007 WHERE organization_id = p_organization_id
1008 AND inventory_item_id = p_inventory_item_id
1009 AND lpn_id IN (SELECT lpn_id
1010 FROM wms_license_plate_numbers
1011 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id ) --nested lpn
1012 AND demand_source_line_detail IS NULL;
1013 ELSE
1014 UPDATE mtl_reservations
1015 SET subinventory_code = p_to_subinventory_code
1016 , locator_id = p_to_locator_id
1017 , lpn_id=NULL
1018 WHERE organization_id = p_organization_id
1019 AND lpn_id IN (SELECT lpn_id
1020 FROM wms_license_plate_numbers
1021 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id ) --nested lpn
1022 AND demand_source_line_detail IS NULL;
1023 END IF;
1024 END IF;
1025 ELSE
1026 -- sub is non-reservable
1027 --need to delete the reservations
1028 IF p_inventory_item_id IS NOT NULL THEN
1029 DELETE FROM mtl_reservations
1030 WHERE organization_id = p_organization_id
1031 AND inventory_item_id = p_inventory_item_id
1032 AND lpn_id IN (SELECT lpn_id
1033 FROM wms_license_plate_numbers
1034 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id ); --nested lpn
1035 ELSE
1036 DELETE FROM mtl_reservations
1037 WHERE organization_id = p_organization_id
1038 AND lpn_id IN (SELECT lpn_id
1039 FROM wms_license_plate_numbers
1040 WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id ); --nested lpn
1041 END IF;
1042 END IF;
1043
1044
1045
1046 -- call inv_reservation_pvt.transfer_serial_rsv_in_LPN to
1047 -- and pass the outermost_lpn_id to transfer any serial
1048 -- reservations with no lpn in the same reservation in that lpn.
1049 inv_reservation_pvt.transfer_serial_rsv_in_LPN
1050 (
1051 x_return_status => x_return_status
1052 , x_msg_count => x_msg_count
1053 , x_msg_data => x_msg_data
1054 , p_organization_id => p_organization_id
1055 , p_inventory_item_id => p_inventory_item_id
1056 , p_lpn_id => null
1057 , p_outermost_lpn_id => p_transfer_lpn_id
1058 , p_to_subinventory_code => p_to_subinventory_code
1059 , p_to_locator_id => p_to_locator_id
1060 );
1061
1062 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1063 IF (l_debug = 1) THEN
1064 debug_print('transfer_reserved_lpn_contents:Error return status from transfer_serial_rsv_in_LPN');
1065 END IF;
1066
1067 RAISE fnd_api.g_exc_error;
1068 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1069 IF (l_debug = 1) THEN
1070 debug_print('transfer_reserved_lpn_contents:Unexpected return status from transfer_serial_rsv_in_LPN');
1071 END IF;
1072
1073 RAISE fnd_api.g_exc_unexpected_error;
1074 END IF;
1075
1076 inv_quantity_tree_pvt.clear_quantity_cache;
1077
1078 x_return_status := fnd_api.g_ret_sts_success;
1079 EXCEPTION
1080 WHEN fnd_api.g_exc_error THEN
1081 x_return_status := fnd_api.g_ret_sts_error;
1082 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1083 WHEN fnd_api.g_exc_unexpected_error THEN
1084 x_return_status := fnd_api.g_ret_sts_unexp_error;
1085 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1086 WHEN OTHERS THEN
1087 x_return_status := fnd_api.g_ret_sts_unexp_error;
1088
1089 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1090 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1091 END IF;
1092 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1093 END transfer_reserved_lpn_contents;
1094
1095 -- ER 7307189 changes end
1096
1097 END inv_lpn_reservations_pvt;