[Home] [Help]
PACKAGE BODY: APPS.INV_LPN_RESERVATIONS_PVT
Source
1 PACKAGE BODY inv_lpn_reservations_pvt AS
2 /* $Header: INVRSVLB.pls 120.7.12020000.4 2013/05/10 06:01:10 ssrikaku 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 sum (secondary_transaction_quantity) -- 16773889
181 FROM mtl_onhand_quantities_detail
182 WHERE lpn_id = p_lpn_id;
183
184 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
185 -- 16773889
186 l_fb VARCHAR2(1) := 'P';
187 l_lpn_sec_rsv_qty NUMBER;
188 l_sec_rsv_qty NUMBER;
189 l_sec_qty NUMBER;
190 l_sec_ordered_qty NUMBER;
191 -- 16773889
192
193 BEGIN
194 IF (l_debug = 1) THEN
195 debug_print('Enter Create_LPN_Reservations');
196 debug_print('OrgID = ' || p_organization_id || ' : ItemID = ' || p_inventory_item_id || ' : LPN ID = ' || p_lpn_id);
197 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);
198 END IF;
199 SAVEPOINT entire_lpn;
200 --validate input values
201 IF p_organization_id IS NULL
202 OR p_inventory_item_id IS NULL
203 OR p_demand_source_type_id IS NULL
204 OR p_lpn_id IS NULL THEN
205 IF (l_debug = 1) THEN
206 debug_print('Missing input parameters');
207 END IF;
208
209 RAISE fnd_api.g_exc_unexpected_error;
210 END IF;
211
212 --bug#2402957. call is_lpn_reserved to see if lpn is reserved for some other demand.
213 l_lpn_already_resv := is_lpn_reserved(
214 p_org_id => p_organization_id
215 , p_item_id => p_inventory_item_id
216 , p_demand_source_type_id => p_demand_source_type_id
217 , p_demand_source_header_id => p_demand_source_header_id
218 , p_demand_source_line_id => p_demand_source_line_id
219 , p_demand_source_name => p_demand_source_name
220 , p_lpn_id => p_lpn_id
221 );
222 IF l_lpn_already_resv THEN
223 IF (l_debug = 1) THEN
224 debug_print('Error: LPN is reserved for some other demand line');
225 END IF;
226 fnd_message.set_name('INV', 'INV_CANNOT_RESERVE_LPN');
227 fnd_msg_pub.ADD;
228 RAISE fnd_api.g_exc_error;
229 END IF;
230
231 OPEN c_item_controls;
232 -- INVCONV - incorporate secondary uom and tracking quantity below
233 FETCH c_item_controls INTO l_revision_control_code, l_lot_control_code,
234 l_primary_uom_code, l_secondary_uom_code, l_tracking_quantity_ind;
235 CLOSE c_item_controls;
236
237 IF l_revision_control_code = 2 THEN
238 l_revision_control := TRUE;
239 ELSE
240 l_revision_control := FALSE;
241 END IF;
242
243 IF l_lot_control_code = 2 THEN
244 l_lot_control := TRUE;
245 ELSE
246 l_lot_control := FALSE;
247 END IF;
248
249 --bug#2402957. get orderline qty, current reservation qty for the order line and current qty
250 --reserved for the orderline by the current lpn.
251 IF p_demand_source_type_id IN (2,8) THEN -- 16773889
252 BEGIN
253 SELECT nvl(ordered_quantity,0),order_quantity_uom
254 INTO l_ord_qty,l_order_qty_uom
255 FROM oe_order_lines_all
256 WHERE line_id = p_demand_source_line_id;
257 EXCEPTION
258 WHEN NO_DATA_FOUND THEN
259 l_ord_qty := 0;
260 END;
261
262 l_fb := nvl(OE_DUAL_UOM_UTIL.get_fulfillment_base(p_demand_source_line_id), 'P'); -- 16773889
263 END IF; -- 16773889
264 --convert ordered qty into primary UOM.
265 IF l_ord_qty <> 0 THEN
266
267 IF (l_debug = 1) THEN
268 debug_print('l_tracking_quantity_ind = '||l_tracking_quantity_ind);
269 END IF;
270
271 l_ordered_qty := nvl(inv_convert.inv_um_convert(
272 item_id => p_inventory_item_id
273 , PRECISION => NULL
274 , from_quantity => l_ord_qty
275 , from_unit => l_order_qty_uom
276 , to_unit => l_primary_uom_code
277 , from_name => NULL
278 , to_name => NULL
279 ), 0);
280
281 -- 16773889
282 IF l_tracking_quantity_ind = 'PS' THEN
283
284 l_sec_ordered_qty := nvl(inv_convert.inv_um_convert(
285 item_id => p_inventory_item_id
286 , PRECISION => NULL
287 , from_quantity => l_ord_qty
288 , from_unit => l_order_qty_uom
289 , to_unit => l_secondary_uom_code
290 , from_name => NULL
291 , to_name => NULL
292 ), 0);
293 END IF;
294
295 -- 16773889
296
297 END IF;
298
299 -- {{
300 -- R12: Create an LPN reservation for a sales order line that
301 -- is not pick released, using an LPN that is fully available
302 --
303 -- Create an LPN reservation for a sales order line that is
304 -- not pick released, using an LPN that is partially crossdock-
305 -- pegged to another order line - should fail
306 --
307 -- Create an LPN reservation for a sales order line that is
308 -- that is partially pegged to an LPN. Should be able to
309 -- peg the remaining order qty to the remaining LPN qty }}
310 --
311 SELECT nvl(sum(primary_reservation_quantity),0), nvl(sum(SECONDARY_RESERVATION_QUANTITY),0) -- 16773889
312 INTO l_reserved_qty, l_sec_rsv_qty --16773889
313 FROM mtl_reservations
314 WHERE demand_source_line_id = p_demand_source_line_id
315 AND organization_id = p_organization_id
316 AND inventory_item_id = p_inventory_item_id;
317
318 SELECT nvl(sum(primary_reservation_quantity),0), nvl(sum(SECONDARY_RESERVATION_QUANTITY),0) -- 16773889
319 INTO l_lpn_reserved_qty, l_lpn_sec_rsv_qty -- 16773889
320 FROM mtl_reservations
321 WHERE demand_source_line_id = p_demand_source_line_id
322 AND lpn_id = p_lpn_id
323 AND organization_id = p_organization_id
324 AND inventory_item_id = p_inventory_item_id
325 AND demand_source_line_detail IS NULL;
326
327 OPEN c_lpn_qty;
328 FETCH c_lpn_qty INTO l_quantity, l_sec_qty; --16773889
329 CLOSE c_lpn_qty;
330
331 IF (l_debug = 1) THEN
332 debug_print('Order Qty in Order UOM = ' || l_ord_qty);
333 debug_print('Order Qty in Primary UOM = ' || l_ordered_qty);
334 debug_print('LPN Qty in Primary UOM = ' || l_quantity);
335 debug_print('Reserved Qty in Primary UOM = ' || l_reserved_qty);
336 debug_print('LPN Reserved Qty in Primary UOM = ' || l_lpn_reserved_qty);
337 debug_print('Order Qty in SEC UOM = ' || l_sec_ordered_qty); -- 16773889
338 debug_print('LPN Qty in SEC UOM = ' || l_sec_qty); -- 16773889
339 debug_print('Reserved Qty in SEC UOM = ' || l_sec_rsv_qty); -- 16773889
340 debug_print('LPN Reserved Qty in SEC UOM = ' || l_lpn_sec_rsv_qty); -- 16773889
341 debug_print('Fulfillment base = ' || l_fb); --16773889
342 END IF;
343
344 --bug#2402957. if the reservation is for a sales/internal order. order qty will be > 0.
345 IF (l_ordered_qty <> 0 and l_fb = 'P') OR (l_sec_ordered_qty <> 0 and l_fb = 'S') then -- 16773889
346 IF (l_ordered_qty = l_reserved_qty and l_fb = 'P') or (l_sec_ordered_qty = l_sec_rsv_qty and l_fb = 'S') THEN -- 16773889
347 --show error that the order is completely reserved.
348 IF (l_debug = 1) THEN
349 debug_print('Error: Order Line completely reserved');
350 END IF;
351 fnd_message.set_name('INV', 'INV_CANNOT_CREATE_RESERVATION');
352 fnd_msg_pub.ADD;
353 RAISE fnd_api.g_exc_error;
354 ELSIF ((l_ordered_qty - l_reserved_qty + l_lpn_reserved_qty < l_quantity) and l_fb = 'P')
355 or ((l_sec_ordered_qty - l_sec_rsv_qty + l_lpn_sec_rsv_qty < l_sec_qty) and l_fb = 'S') -- 16773889
356 THEN
357 --error as lpn has more qty than order line.
358 IF (l_debug = 1) THEN
359 debug_print('Error: LPN Qty > Order Line Qty');
360 END IF;
361 fnd_message.set_name('INV', 'INV_LPN_QTY_GREATER');
362 fnd_msg_pub.ADD;
363 RAISE fnd_api.g_exc_error;
364 END IF;
365 END IF;
366
367 -- create the quantity tree
368 inv_quantity_tree_pvt.create_tree(
369 p_api_version_number => 1.0
370 , p_init_msg_lst => fnd_api.g_true
371 , x_return_status => l_return_status
372 , x_msg_count => l_msg_count
373 , x_msg_data => l_msg_data
374 , p_organization_id => p_organization_id
375 , p_inventory_item_id => p_inventory_item_id
376 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
377 , p_is_revision_control => l_revision_control
378 , p_is_lot_control => l_lot_control
379 , p_is_serial_control => FALSE
380 , p_asset_sub_only => FALSE
381 , p_include_suggestion => TRUE
382 , p_demand_source_type_id => p_demand_source_type_id
383 , p_demand_source_header_id => p_demand_source_header_id
384 , p_demand_source_line_id => p_demand_source_line_id
385 , p_demand_source_name => p_demand_source_name
386 , p_demand_source_delivery => NULL
387 , p_lot_expiration_date => SYSDATE -- Bug#2716563
388 , x_tree_id => l_tree_id
389 );
390
391 IF l_return_status = fnd_api.g_ret_sts_error THEN
392 IF (l_debug = 1) THEN
393 debug_print('Error creating quantity tree');
394 END IF;
395
396 RAISE fnd_api.g_exc_error;
397 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
398 IF (l_debug = 1) THEN
399 debug_print('Unexpected error creating quantity tree');
400 END IF;
401
402 RAISE fnd_api.g_exc_unexpected_error;
403 END IF;
404
405 OPEN c_lpn_contents;
406
407 IF (l_debug = 1) THEN
408 debug_print('Looping through each LPN contents');
409 END IF;
410
411 --for each content record in the lpn
412 LOOP
413 -- INVCONV - secondary_quantity below
414 FETCH c_lpn_contents INTO l_revision, l_lot_number, l_subinventory_code, l_locator_id,
415 l_quantity, l_secondary_quantity;
416 EXIT WHEN c_lpn_contents%NOTFOUND;
417
418 IF (l_debug = 1) THEN
419 debug_print('Fetched a LPN Record');
420 debug_print(' --> Revision : '|| l_revision);
421 debug_print(' --> Lot Number : '|| l_lot_number);
422 debug_print(' --> SubInventory : '|| l_subinventory_code);
423 debug_print(' --> Locator ID : '|| l_locator_id);
424 debug_print(' --> Current Qty : '|| l_quantity);
425 debug_print(' --> Secondary Qty : '|| l_secondary_quantity);
426 END IF;
427
428 --Query to see if this reservation already exists
429 l_query_rec.organization_id := p_organization_id;
430 l_query_rec.inventory_item_id := p_inventory_item_id;
431 l_query_rec.demand_source_type_id := p_demand_source_type_id;
432 l_query_rec.demand_source_header_id := p_demand_source_header_id;
433 l_query_rec.demand_source_line_id := p_demand_source_line_id;
434 l_query_rec.demand_source_name := p_demand_source_name;
435 l_query_rec.revision := l_revision;
436 l_query_rec.lot_number := l_lot_number;
437 l_query_rec.subinventory_code := l_subinventory_code;
438 l_query_rec.locator_id := l_locator_id;
439 l_query_rec.lpn_id := p_lpn_id;
440 l_query_rec.demand_source_line_detail := NULL;
441
442 IF (l_debug = 1) THEN
443 debug_print('Querying for existing reservations');
444 END IF;
445
446 inv_reservation_pvt.query_reservation(
447 p_api_version_number => 1.0
448 , p_init_msg_lst => fnd_api.g_false
449 , x_return_status => l_return_status
450 , x_msg_count => l_msg_count
451 , x_msg_data => l_msg_data
452 , p_query_input => l_query_rec
453 , x_mtl_reservation_tbl => l_rsv_tbl
454 , x_mtl_reservation_tbl_count => l_rsv_count
455 , x_error_code => l_error_code
456 );
457
458 IF l_return_status = fnd_api.g_ret_sts_error THEN
459 IF (l_debug = 1) THEN
460 debug_print('Error in query_reservation');
461 END IF;
462
463 RAISE fnd_api.g_exc_error;
464 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
465 IF (l_debug = 1) THEN
466 debug_print('Unexpected error in query_reservation');
467 END IF;
468
469 RAISE fnd_api.g_exc_unexpected_error;
470 END IF;
471
472 -- If reservation exists
473 IF l_rsv_count >= 1 THEN
474 IF (l_debug = 1) THEN
475 debug_print('Reservation exists');
476 END IF;
477
478 l_total_rsv_qty := 0;
479 FOR l_count IN 1 .. l_rsv_count LOOP
480 l_total_rsv_qty := l_total_rsv_qty + l_rsv_tbl(l_count).primary_reservation_quantity;
481 -- INVCONV BEGIN
482 IF l_tracking_quantity_ind = 'PS' THEN
483 l_total_secondary_rsv_qty :=
484 NVL(l_total_secondary_rsv_qty,0) + l_rsv_tbl(l_count).secondary_reservation_quantity;
485 END IF;
486 -- INVCONV END
487 END LOOP;
488 l_increase_quantity := l_quantity - l_total_rsv_qty;
489 -- INVCONV BEGIN
490 IF l_tracking_quantity_ind = 'PS' THEN
491 l_increase_secondary := l_secondary_quantity - l_total_secondary_rsv_qty;
492 END IF;
493 -- INVCONV END
494
495 -- If not all the quantity is reserved, increase reservation quantity
496 IF (l_increase_quantity > 0 and l_fb = 'P') or (l_increase_secondary > 0 and l_fb = 'S') THEN -- 16773889
497 IF (l_debug = 1) THEN
498 debug_print('Trying to increase the Reservation Qty by '|| l_increase_quantity);
499 debug_print('Trying to increase the Secondary Rsv Qty by '|| l_increase_secondary); -- INVCONV
500 END IF;
501
502 --query quantity tree to make sure quantity is available to increase reservations
503 inv_quantity_tree_pvt.query_tree(
504 p_api_version_number => 1.0
505 , p_init_msg_lst => fnd_api.g_true
506 , x_return_status => l_return_status
507 , x_msg_count => l_msg_count
508 , x_msg_data => l_msg_data
509 , p_tree_id => l_tree_id
510 , p_revision => l_revision
511 , p_lot_number => l_lot_number
512 , p_subinventory_code => l_subinventory_code
513 , p_locator_id => l_locator_id
514 , x_qoh => l_qoh
515 , x_rqoh => l_rqoh
516 , x_qr => l_qr
517 , x_qs => l_qs
518 , x_att => l_att
519 , x_atr => l_atr
520 , x_sqoh => l_sqoh -- INVCONV
521 , x_srqoh => l_srqoh -- INVCONV
522 , x_sqr => l_sqr -- INVCONV
523 , x_sqs => l_sqs -- INVCONV
524 , x_satt => l_satt -- INVCONV
525 , x_satr => l_satr -- INVCONV
526 , p_transfer_subinventory_code => NULL
527 , p_cost_group_id => NULL
528 , p_lpn_id => p_lpn_id
529 , p_transfer_locator_id => NULL
530 );
531
532 IF l_return_status = fnd_api.g_ret_sts_error THEN
533 IF (l_debug = 1) THEN
534 debug_print('Error from query_tree');
535 END IF;
536
537 RAISE fnd_api.g_exc_error;
538 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
539 IF (l_debug = 1) THEN
540 debug_print('Unexpected error from query_tree');
541 END IF;
542
543 RAISE fnd_api.g_exc_unexpected_error;
544 END IF;
545
546 --Bug#2402957:
547 -- If (ATR + AlreadyReserved) <> LPN Qty => LPN is reserved for some other demand, so error.
548 -- This is possible only when there are Higher Level Reservations. Otherwise it will
549 -- be caught at is_lpn_reserved
550 IF (l_quantity <> (l_atr + l_total_rsv_qty) and l_fb = 'P') or -- 16773889
551 (l_secondary_quantity <> (l_satr + l_total_secondary_rsv_qty) and l_fb = 'S')THEN --16773889
552 --show error that lpn is reserved for some other demand.
553 IF (l_debug = 1) THEN
554 debug_print('Error: LPN is reserved for some other demand line');
555 END IF;
556 fnd_message.set_name('INV', 'INV_CANNOT_RESERVE_LPN');
557 fnd_msg_pub.ADD;
558 RAISE fnd_api.g_exc_error;
559 END IF;
560
561 l_increase_quantity := l_atr + l_rsv_tbl(1).primary_reservation_quantity;
562 -- INVCONV BEGIN
563 IF l_tracking_quantity_ind = 'PS' THEN
564 l_increase_secondary := l_satr + l_rsv_tbl(1).secondary_reservation_quantity;
565 END IF;
566 -- INVCONV END
567 IF (l_debug = 1) THEN
568 debug_print('New Reservation Qty = '|| l_increase_quantity);
569 debug_print('New Secondary Reservation Qty = '|| l_increase_secondary);
570 END IF;
571 IF (l_increase_quantity > 0 and l_fb = 'P') or (l_increase_secondary > 0 and l_fb = 'S') THEN
572 l_rsv_rec := l_rsv_tbl(1);
573 l_rsv_rec.primary_reservation_quantity := l_increase_quantity;
574 l_rsv_rec.reservation_quantity := NULL;
575 l_rsv_rec.secondary_reservation_quantity := l_increase_secondary; -- INVCONV
576
577 -- Call update reservation to increase quantity
578 IF (l_debug = 1) THEN
579 debug_print('Calling Update Reservation');
580 END IF;
581
582 inv_reservation_pvt.update_reservation(
583 p_api_version_number => 1.0
584 , p_init_msg_lst => fnd_api.g_false
585 , x_return_status => l_return_status
586 , x_msg_count => l_msg_count
587 , x_msg_data => l_msg_data
588 , p_original_rsv_rec => l_rsv_tbl(1)
589 , p_to_rsv_rec => l_rsv_rec
590 , p_original_serial_number => l_dummy_sn
591 , p_to_serial_number => l_dummy_sn
592 , p_validation_flag => fnd_api.g_true
593 );
594
595 IF l_return_status = fnd_api.g_ret_sts_error THEN
596 IF (l_debug = 1) THEN
597 debug_print('Error from update_reservation');
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 from update_reservation');
604 END IF;
605
606 RAISE fnd_api.g_exc_unexpected_error;
607 END IF;
608 END IF; -- if new increase quantity > 0
609 END IF; -- if increase quantity > 0
610 ELSE --Else, create new reservation
611 IF (l_debug = 1) THEN
612 debug_print('Reservation doesnt exists');
613 END IF;
614
615 inv_quantity_tree_pvt.query_tree(
616 p_api_version_number => 1.0
617 , p_init_msg_lst => fnd_api.g_true
618 , x_return_status => l_return_status
619 , x_msg_count => l_msg_count
620 , x_msg_data => l_msg_data
621 , p_tree_id => l_tree_id
622 , p_revision => l_revision
623 , p_lot_number => l_lot_number
624 , p_subinventory_code => l_subinventory_code
625 , p_locator_id => l_locator_id
626 , x_qoh => l_qoh
627 , x_rqoh => l_rqoh
628 , x_qr => l_qr
629 , x_qs => l_qs
630 , x_att => l_att
631 , x_atr => l_atr
632 -- 16773889
633 , x_sqoh => l_sqoh -- INVCONV
634 , x_srqoh => l_srqoh -- INVCONV
635 , x_sqr => l_sqr -- INVCONV
636 , x_sqs => l_sqs -- INVCONV
637 , x_satt => l_satt -- INVCONV
638 , x_satr => l_satr -- INVCONV
639 -- 16773889
640
641 , p_transfer_subinventory_code => NULL
642 , p_cost_group_id => NULL
643 , p_lpn_id => p_lpn_id
644 , p_transfer_locator_id => NULL
645 );
646
647 IF l_return_status = fnd_api.g_ret_sts_error THEN
648 IF (l_debug = 1) THEN
649 debug_print('Error in query_tree');
650 END IF;
651
652 RAISE fnd_api.g_exc_error;
653 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
654 IF (l_debug = 1) THEN
655 debug_print('Unexpected error in query_tree');
656 END IF;
657
658 RAISE fnd_api.g_exc_unexpected_error;
659 END IF;
660
661 --Bug#2402957:
662 -- If ATR <> LPN Qty => LPN is also reserved for some other demand, so error.
663 -- This is possible only when there are Higher Level Reservations. Otherwise it will
664 -- be caught at is_lpn_reserved
665 IF (l_quantity <> l_atr and l_fb = 'P') or (l_secondary_quantity <> l_satr and l_fb = 'S') THEN --16773889
666 --show error that lpn is reserved for some other demand.
667 IF (l_debug = 1) THEN
668 debug_print('Error: LPN is reserved for some other order line');
669 END IF;
670 fnd_message.set_name('INV', 'INV_CANNOT_RESERVE_LPN');
671 fnd_msg_pub.ADD;
672 RAISE fnd_api.g_exc_error;
673 END IF;
674
675 IF (l_debug = 1) THEN
676 debug_print('New Reservation Qty = '|| l_quantity);
677 debug_print('Secondary Reservation Qty = '|| l_secondary_quantity);
678 END IF;
679 IF (l_quantity > 0 and l_fb = 'P') or (l_secondary_quantity > 0 and l_fb = 'S') THEN --16773889
680 --create reservation for available quantity
681 l_rsv_rec.reservation_id := NULL; -- cannot know
682 l_rsv_rec.requirement_date := p_need_by_date;
683 l_rsv_rec.organization_id := p_organization_id;
684 l_rsv_rec.inventory_item_id := p_inventory_item_id;
685 l_rsv_rec.demand_source_type_id := p_demand_source_type_id;
686 l_rsv_rec.demand_source_name := p_demand_source_name;
687 l_rsv_rec.demand_source_header_id := p_demand_source_header_id;
688 l_rsv_rec.demand_source_line_id := p_demand_source_line_id;
689 l_rsv_rec.demand_source_delivery := NULL;
690 l_rsv_rec.primary_uom_code := l_primary_uom_code;
691 l_rsv_rec.primary_uom_id := NULL;
692 l_rsv_rec.secondary_uom_code := l_secondary_uom_code; -- INVCONV
693 l_rsv_rec.secondary_uom_id := NULL; -- INVCONV
694 l_rsv_rec.reservation_uom_code := l_primary_uom_code;
695 l_rsv_rec.reservation_uom_id := NULL;
696 l_rsv_rec.primary_reservation_quantity := l_quantity;
697 l_rsv_rec.secondary_reservation_quantity:= l_secondary_quantity;
698 l_rsv_rec.reservation_quantity := l_quantity;
699 l_rsv_rec.autodetail_group_id := NULL;
700 l_rsv_rec.external_source_code := NULL;
701 l_rsv_rec.external_source_line_id := NULL;
702 l_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
703 l_rsv_rec.supply_source_header_id := NULL;
704 l_rsv_rec.supply_source_line_id := NULL;
705 l_rsv_rec.supply_source_name := NULL;
706 l_rsv_rec.supply_source_line_detail := NULL;
707 l_rsv_rec.revision := l_revision;
708 l_rsv_rec.lot_number := l_lot_number;
709 l_rsv_rec.subinventory_code := l_subinventory_code;
710 l_rsv_rec.subinventory_id := NULL;
711 l_rsv_rec.locator_id := l_locator_id;
712 l_rsv_rec.lot_number_id := NULL;
713 l_rsv_rec.pick_slip_number := NULL;
714 l_rsv_rec.lpn_id := p_lpn_id;
715 l_rsv_rec.attribute_category := NULL;
716 l_rsv_rec.attribute1 := NULL;
717 l_rsv_rec.attribute2 := NULL;
718 l_rsv_rec.attribute3 := NULL;
719 l_rsv_rec.attribute4 := NULL;
720 l_rsv_rec.attribute5 := NULL;
721 l_rsv_rec.attribute6 := NULL;
722 l_rsv_rec.attribute7 := NULL;
723 l_rsv_rec.attribute8 := NULL;
724 l_rsv_rec.attribute9 := NULL;
725 l_rsv_rec.attribute10 := NULL;
726 l_rsv_rec.attribute11 := NULL;
727 l_rsv_rec.attribute12 := NULL;
728 l_rsv_rec.attribute13 := NULL;
729 l_rsv_rec.attribute14 := NULL;
730 l_rsv_rec.attribute15 := NULL;
731 l_rsv_rec.ship_ready_flag := NULL;
732 l_rsv_rec.detailed_quantity := 0;
733 -- INVCONV BEGIN
734 IF l_tracking_quantity_ind = 'PS' THEN
735 l_rsv_rec.secondary_detailed_quantity := 0;
736 END IF;
737 -- INVCONV END
738
739 IF (l_debug = 1) THEN
740 debug_print('Calling create_reservation');
741 END IF;
742
743 -- INVCONV - Upgrade call for Inventory Convergence
744 inv_reservation_pvt.create_reservation(
745 p_api_version_number => 1.0
746 , p_init_msg_lst => fnd_api.g_false
747 , x_return_status => l_return_status
748 , x_msg_count => l_msg_count
749 , x_msg_data => l_msg_data
750 , p_rsv_rec => l_rsv_rec
751 , p_serial_number => l_dummy_sn
752 , x_serial_number => l_dummy_sn
753 , p_partial_reservation_flag => fnd_api.g_true
754 , p_force_reservation_flag => fnd_api.g_false
755 , p_validation_flag => fnd_api.g_true
756 , x_quantity_reserved => l_quantity_reserved
757 , x_secondary_quantity_reserved => l_secondary_quantity_reserved --INVCONV
758 , x_reservation_id => l_reservation_id
759 );
760
761 IF l_return_status = fnd_api.g_ret_sts_error THEN
762 IF (l_debug = 1) THEN
763 debug_print('Error in create_reservation');
764 END IF;
765
766 RAISE fnd_api.g_exc_error;
767 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
768 IF (l_debug = 1) THEN
769 debug_print('Unexpected error in create_reservation');
770 END IF;
771
772 RAISE fnd_api.g_exc_unexpected_error;
773 END IF;
774 END IF; -- if quantity > 0
775 END IF; -- if reservation count > 0
776 END LOOP;
777
778 CLOSE c_lpn_contents;
779 x_return_status := fnd_api.g_ret_sts_success;
780
781 IF (l_debug = 1) THEN
782 debug_print('Exit Create_LPN_Reservations');
783 END IF;
784 EXCEPTION
785 WHEN fnd_api.g_exc_error THEN
786 x_return_status := fnd_api.g_ret_sts_error;
787 ROLLBACK TO entire_lpn; --bug#2402957.
788 IF (l_debug = 1) THEN
789 debug_print('Error in Create_LPN_Reservations');
790 END IF;
791 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded=> 'F');
792 WHEN fnd_api.g_exc_unexpected_error THEN
793 x_return_status := fnd_api.g_ret_sts_unexp_error;
794
795 IF (l_debug = 1) THEN
796 debug_print('Unexpected error in Create_LPN_Reservations');
797 END IF;
798 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded=> 'F');
799 WHEN OTHERS THEN
800 x_return_status := fnd_api.g_ret_sts_unexp_error;
801
802 IF (l_debug = 1) THEN
803 debug_print('Other error in Create_LPN_Reservations');
804 END IF;
805
806 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
807 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
808 END IF;
809 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded=> 'F');
810 END create_lpn_reservations;
811
812 --Transfer_LPN_Reservations
813 --
814 -- This API is designed to be called from the mobile subinventory transfer
815 -- and putaway forms. This procedure will transfer all the reservations
816 -- for a given LPN from the current subinventory and locator to a new
817 -- subinventory and locator. This is useful for moving reserved LPNs around
818 -- the warehouse.
819 -- For bug 14778937, use INV API to update MR table
820 PROCEDURE transfer_lpn_reservations(
821 x_return_status OUT NOCOPY VARCHAR2
822 , x_msg_count OUT NOCOPY NUMBER
823 , x_msg_data OUT NOCOPY VARCHAR2
824 , p_organization_id IN NUMBER
825 , p_inventory_item_id IN NUMBER DEFAULT NULL
826 , p_lpn_id IN NUMBER
827 , p_to_subinventory_code IN VARCHAR2
828 , p_to_locator_id IN NUMBER
829 , p_system_task_type IN NUMBER DEFAULT NULL -- 9794776
830 ) IS
831 l_api_name VARCHAR2(30) := 'transfer_lpn_reservations';
832 l_debug NUMBER;
833 l_reservable_type NUMBER;
834 l_lpn_controlled_flag NUMBER;
835
836 CURSOR mrc(v_organization_id NUMBER, v_lpn_id NUMBER, v_inventory_item_id NUMBER) IS
837 SELECT reservation_id, subinventory_code, locator_id, lpn_id
838 FROM mtl_reservations
839 WHERE organization_id = v_organization_id
840 AND (v_inventory_item_id IS NULL OR inventory_item_id = v_inventory_item_id)
841 AND lpn_id IN (SELECT lpn_id
842 FROM wms_license_plate_numbers
843 WHERE outermost_lpn_id = v_lpn_id OR lpn_id = v_lpn_id)
844 AND demand_source_type_id not in decode(p_system_task_type,3,-1,9);
845
846 CURSOR serials_lpn(v_reservation_id NUMBER) IS
847 SELECT msn.inventory_item_id,
848 msn.serial_number
849 FROM mtl_reservations mr,
850 mtl_serial_numbers msn
851 WHERE mr.organization_id = p_organization_id
852 AND (p_inventory_item_id IS NULL OR mr.inventory_item_id = p_inventory_item_id)
853 AND mr.reservation_id = msn.reservation_id
854 AND mr.lpn_id=p_lpn_id
855 AND mr.reservation_id=v_reservation_id
856 AND (msn.lpn_id IS NULL OR msn.lpn_id = p_lpn_id); -- lpn may be unpack...
857
858 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
859 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
860 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
861 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
862 l_mtl_reservation_tbl_count NUMBER;
863 l_error_code NUMBER;
864
865 BEGIN
866 IF (g_debug IS NULL) THEN
867 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
868 END IF;
869
870 l_debug := g_debug;
871
872 IF (l_debug = 1) THEN
873 debug_print('In '||l_api_name);
874 debug_print(l_api_name||': p_organization_id = ' || p_organization_id);
875 debug_print(l_api_name||': p_inventory_item_id = ' || p_inventory_item_id);
876 debug_print(l_api_name||': p_lpn_id = ' || p_lpn_id);
877 debug_print(l_api_name||': p_to_subinventory_code = ' || p_to_subinventory_code);
878 debug_print(l_api_name||': p_to_locator_id = ' || p_to_locator_id);
879 END IF;
880
881 SELECT reservable_type,lpn_controlled_flag
882 INTO l_reservable_type, l_lpn_controlled_flag
883 FROM mtl_secondary_inventories
884 WHERE secondary_inventory_name LIKE (p_to_subinventory_code)
885 AND organization_id = p_organization_id;
886
887 IF (l_debug = 1) THEN
888 debug_print(l_api_name||' l_reservable_type '|| l_reservable_type);
889 debug_print(l_api_name||' l_lpn_controlled_flag '|| l_lpn_controlled_flag);
890 END IF;
891
892 IF l_reservable_type = 1 THEN --transfer Sub is reservable, keep the reservation record
893 OPEN mrc(p_organization_id,p_lpn_id,p_inventory_item_id);
894 LOOP
895 FETCH mrc INTO l_rsv_rec.reservation_id, l_rsv_rec.subinventory_code, l_rsv_rec.locator_id, l_rsv_rec.lpn_id;
896 EXIT WHEN mrc%NOTFOUND;
897
898 OPEN serials_lpn(l_rsv_rec.reservation_id);
899 FETCH serials_lpn BULK COLLECT INTO l_serial_number_tbl;
900 CLOSE serials_lpn;
901
902 IF (l_debug = 1) THEN
903 debug_print(l_api_name||': l_serial_number_tbl.count '|| l_serial_number_tbl.Count);
904 END IF;
905
906 l_to_rsv_rec := l_rsv_rec;
907 l_to_rsv_rec.subinventory_code :=p_to_subinventory_code;
908 l_to_rsv_rec.locator_id:= p_to_locator_id;
909 IF l_lpn_controlled_flag <> 1 THEN -- Not (transfer Sub is LPN controlled, keep the lpn_id stamping)
910 l_to_rsv_rec.lpn_id :=NULL;
911 END IF;
912
913 IF (l_debug = 1) THEN
914 debug_print('Try to use API to Update MR');
915 END IF;
916 inv_reservation_pvt.update_reservation
917 (p_api_version_number => 1.0,
918 p_init_msg_lst => fnd_api.g_false,
919 x_return_status => x_return_status,
920 x_msg_count => x_msg_count,
921 x_msg_data => x_msg_data,
922 p_original_rsv_rec => l_rsv_rec,
923 p_to_rsv_rec => l_to_rsv_rec,
924 p_original_serial_number => l_serial_number_tbl,
925 p_to_serial_number => l_serial_number_tbl,
926 p_validation_flag => fnd_api.g_false
927 );
928
929 IF l_debug=1 THEN
930 debug_print ('Return Status after update reservations '||x_return_status);
931 END IF;
932
933 IF x_return_status = fnd_api.g_ret_sts_error THEN
934
935 IF l_debug=1 THEN
936 debug_print('Raising expected error'||x_return_status);
937 END IF;
938 RAISE fnd_api.g_exc_error;
939
940 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
941
942 IF l_debug=1 THEN
943 debug_print('Rasing Unexpected error'||x_return_status);
944 END IF;
945 RAISE fnd_api.g_exc_unexpected_error;
946
947 END IF;
948
949 END LOOP;
950 ELSE
951 -- sub is non-reservable
952 --need to delete the reservations
953 IF (l_debug = 1) THEN
954 debug_print(l_api_name||': Delete MR');
955 END IF;
956
957 OPEN mrc(p_organization_id,p_lpn_id,p_inventory_item_id);
958 LOOP
959 FETCH mrc INTO l_rsv_rec.reservation_id, l_rsv_rec.subinventory_code, l_rsv_rec.locator_id, l_rsv_rec.lpn_id;
960 EXIT WHEN mrc%NOTFOUND;
961
962 IF (l_debug = 1) THEN
963 debug_print(l_api_name||': l_rsv_rec.reservation_id '|| l_rsv_rec.reservation_id);
964 END IF;
965
966 inv_reservation_pvt.delete_reservation(
967 p_api_version_number => 1.0
968 , p_init_msg_lst => fnd_api.g_false
969 , x_return_status => x_return_status
970 , x_msg_count => x_msg_count
971 , x_msg_data => x_msg_data
972 , p_rsv_rec => l_rsv_rec
973 , p_original_serial_number => l_serial_number_tbl
974
975 );
976
977 IF x_return_status = fnd_api.g_ret_sts_error THEN
978 RAISE fnd_api.g_exc_error;
979 END IF;
980
981 --
982 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
983 RAISE fnd_api.g_exc_unexpected_error;
984 -- END IF;
985 END IF;
986
987 END LOOP;
988 CLOSE mrc;
989 END IF;
990
991 -- call inv_reservation_pvt.transfer_serial_rsv_in_LPN to
992 -- and pass the outermost_lpn_id to transfer any serial
993 -- reservations with no lpn in the same reservation in that lpn.
994 inv_reservation_pvt.transfer_serial_rsv_in_LPN
995 (
996 x_return_status => x_return_status
997 , x_msg_count => x_msg_count
998 , x_msg_data => x_msg_data
999 , p_organization_id => p_organization_id
1000 , p_inventory_item_id => p_inventory_item_id
1001 , p_lpn_id => null
1002 , p_outermost_lpn_id => p_lpn_id
1003 , p_to_subinventory_code => p_to_subinventory_code
1004 , p_to_locator_id => p_to_locator_id
1005 );
1006
1007 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1008 IF (l_debug = 1) THEN
1009 debug_print(l_api_name||':Error return status from transfer_serial_rsv_in_LPN');
1010 END IF;
1011
1012 RAISE fnd_api.g_exc_error;
1013 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1014 IF (l_debug = 1) THEN
1015 debug_print(l_api_name||':Unexpected return status from transfer_serial_rsv_in_LPN');
1016 END IF;
1017
1018 RAISE fnd_api.g_exc_unexpected_error;
1019 END IF;
1020
1021 inv_quantity_tree_pvt.clear_quantity_cache;
1022 x_return_status := fnd_api.g_ret_sts_success;
1023
1024 EXCEPTION
1025 WHEN fnd_api.g_exc_error THEN
1026 x_return_status := fnd_api.g_ret_sts_error;
1027 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1028 WHEN fnd_api.g_exc_unexpected_error THEN
1029 x_return_status := fnd_api.g_ret_sts_unexp_error;
1030 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1031 WHEN OTHERS THEN
1032 x_return_status := fnd_api.g_ret_sts_unexp_error;
1033
1034 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1035 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1036 END IF;
1037 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1038 END transfer_lpn_reservations;
1039
1040
1041 -- ER 7307189 changes start
1042
1043 --transfer_reserved_lpn_contents
1044 --
1045 -- This API is designed to be called from the mobile Move any LPN (transfer contents scenario) .
1046 -- This procedure will transfer all the reservations
1047 -- from lpn to transfer lpn ,current subinventory and locator to a new
1048 -- subinventory and locator. This is useful for moving reserved LPNs around
1049 -- the warehouse.
1050 -- For bug 14778937, use INV API to update MR table
1051 PROCEDURE transfer_reserved_lpn_contents(
1052 x_return_status OUT NOCOPY VARCHAR2
1053 , x_msg_count OUT NOCOPY NUMBER
1054 , x_msg_data OUT NOCOPY VARCHAR2
1055 , p_organization_id IN NUMBER
1056 , p_inventory_item_id IN NUMBER DEFAULT NULL
1057 , p_lpn_id IN NUMBER
1058 , p_transfer_lpn_id IN NUMBER
1059 , p_to_subinventory_code IN VARCHAR2
1060 , p_to_locator_id IN NUMBER
1061 , p_system_task_type IN NUMBER DEFAULT NULL -- 9794776
1062 ) IS
1063 l_api_name VARCHAR2(30) := 'transfer_reserved_lpn_contents';
1064 l_debug NUMBER;
1065 l_reservable_type NUMBER;
1066 l_lpn_controlled_flag NUMBER;
1067
1068 CURSOR mrc(v_organization_id NUMBER, v_lpn_id NUMBER, v_inventory_item_id NUMBER,v_check_dsld_flag VARCHAR2) IS
1069 SELECT reservation_id, subinventory_code, locator_id, lpn_id
1070 FROM mtl_reservations
1071 WHERE organization_id = v_organization_id
1072 AND (v_inventory_item_id IS NULL OR inventory_item_id = v_inventory_item_id)
1073 AND lpn_id IN (SELECT lpn_id
1074 FROM wms_license_plate_numbers
1075 WHERE outermost_lpn_id = v_lpn_id OR lpn_id = v_lpn_id)
1076 AND (v_check_dsld_flag='FALSE' OR demand_source_line_detail IS NULL)
1077 AND demand_source_type_id not in decode(p_system_task_type,3,-1,9);
1078
1079 CURSOR serials_lpn(v_reservation_id NUMBER) IS
1080 SELECT msn.inventory_item_id,
1081 msn.serial_number
1082 FROM mtl_reservations mr,
1083 mtl_serial_numbers msn
1084 WHERE mr.organization_id = p_organization_id
1085 AND (p_inventory_item_id IS NULL OR mr.inventory_item_id = p_inventory_item_id)
1086 AND mr.reservation_id = msn.reservation_id
1087 AND mr.lpn_id=p_lpn_id
1088 AND mr.reservation_id=v_reservation_id
1089 AND (msn.lpn_id IS NULL OR msn.lpn_id = p_lpn_id); -- lpn may be unpack...
1090
1091 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
1092 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1093 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1094 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
1095 l_mtl_reservation_tbl_count NUMBER;
1096 l_error_code NUMBER;
1097
1098
1099 BEGIN
1100 IF (g_debug IS NULL) THEN
1101 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1102 END IF;
1103
1104 l_debug := g_debug;
1105
1106 IF (l_debug = 1) THEN
1107 debug_print('In '||l_api_name);
1108 debug_print(l_api_name||':p_organization_id = ' || p_organization_id);
1109 debug_print(l_api_name||':p_lpn_id = ' || p_lpn_id);
1110 debug_print(l_api_name||':p_to_subinventory_code = ' || p_to_subinventory_code);
1111 debug_print(l_api_name||':p_to_locator_id = ' || p_to_locator_id);
1112 debug_print(l_api_name||':p_transfer_lpn_id = ' || p_transfer_lpn_id);
1113 debug_print(l_api_name||':p_inventory_item_id = ' || p_inventory_item_id);
1114 END IF;
1115
1116 SELECT reservable_type,lpn_controlled_flag
1117 INTO l_reservable_type, l_lpn_controlled_flag
1118 FROM mtl_secondary_inventories
1119 WHERE secondary_inventory_name LIKE (p_to_subinventory_code)
1120 AND organization_id = p_organization_id;
1121
1122 IF (l_debug = 1) THEN
1123 debug_print(l_api_name||' l_reservable_type '|| l_reservable_type);
1124 debug_print(l_api_name||' l_lpn_controlled_flag '|| l_lpn_controlled_flag);
1125 END IF;
1126
1127 IF l_reservable_type = 1 THEN --transfer Sub is reservable, keep the reservation record
1128 OPEN mrc(p_organization_id,p_lpn_id,p_inventory_item_id,'TRUE');
1129 LOOP
1130 FETCH mrc INTO l_rsv_rec.reservation_id, l_rsv_rec.subinventory_code, l_rsv_rec.locator_id, l_rsv_rec.lpn_id;
1131 EXIT WHEN mrc%NOTFOUND;
1132
1133 OPEN serials_lpn(l_rsv_rec.reservation_id);
1134 FETCH serials_lpn BULK COLLECT INTO l_serial_number_tbl;
1135 CLOSE serials_lpn;
1136
1137 IF (l_debug = 1) THEN
1138 debug_print(l_api_name||': l_serial_number_tbl.count '|| l_serial_number_tbl.Count);
1139 END IF;
1140
1141 l_to_rsv_rec := l_rsv_rec;
1142 l_to_rsv_rec.subinventory_code :=p_to_subinventory_code;
1143 l_to_rsv_rec.locator_id:= p_to_locator_id;
1144 IF l_lpn_controlled_flag <> 1 THEN -- Not (transfer Sub is LPN controlled, keep the lpn_id stamping)
1145 l_to_rsv_rec.lpn_id :=NULL;
1146 ELSE
1147 l_to_rsv_rec.lpn_id :=p_transfer_lpn_id;
1148 END IF;
1149
1150 IF (l_debug = 1) THEN
1151 debug_print('Try to use API to Update MR');
1152 END IF;
1153 inv_reservation_pvt.update_reservation
1154 (p_api_version_number => 1.0,
1155 p_init_msg_lst => fnd_api.g_false,
1156 x_return_status => x_return_status,
1157 x_msg_count => x_msg_count,
1158 x_msg_data => x_msg_data,
1159 p_original_rsv_rec => l_rsv_rec,
1160 p_to_rsv_rec => l_to_rsv_rec,
1161 p_original_serial_number => l_serial_number_tbl,
1162 p_to_serial_number => l_serial_number_tbl,
1163 p_validation_flag => fnd_api.g_false
1164 );
1165
1166 IF l_debug=1 THEN
1167 debug_print ('Return Status after update reservations '||x_return_status);
1168 END IF;
1169
1170 IF x_return_status = fnd_api.g_ret_sts_error THEN
1171
1172 IF l_debug=1 THEN
1173 debug_print('Raising expected error'||x_return_status);
1174 END IF;
1175 RAISE fnd_api.g_exc_error;
1176
1177 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1178
1179 IF l_debug=1 THEN
1180 debug_print('Rasing Unexpected error'||x_return_status);
1181 END IF;
1182 RAISE fnd_api.g_exc_unexpected_error;
1183
1184 END IF;
1185
1186 END LOOP;
1187 ELSE
1188 -- sub is non-reservable
1189 --need to delete the reservations
1190 IF (l_debug = 1) THEN
1191 debug_print(l_api_name||': Delete MR');
1192 END IF;
1193
1194 OPEN mrc(p_organization_id,p_lpn_id,p_inventory_item_id,'FALSE');
1195 LOOP
1196 FETCH mrc INTO l_rsv_rec.reservation_id, l_rsv_rec.subinventory_code, l_rsv_rec.locator_id, l_rsv_rec.lpn_id;
1197 EXIT WHEN mrc%NOTFOUND;
1198
1199 IF (l_debug = 1) THEN
1200 debug_print(l_api_name||': l_rsv_rec.reservation_id '|| l_rsv_rec.reservation_id);
1201 END IF;
1202
1203 inv_reservation_pvt.delete_reservation(
1204 p_api_version_number => 1.0
1205 , p_init_msg_lst => fnd_api.g_false
1206 , x_return_status => x_return_status
1207 , x_msg_count => x_msg_count
1208 , x_msg_data => x_msg_data
1209 , p_rsv_rec => l_rsv_rec
1210 , p_original_serial_number => l_serial_number_tbl
1211
1212 );
1213
1214 IF x_return_status = fnd_api.g_ret_sts_error THEN
1215 RAISE fnd_api.g_exc_error;
1216 END IF;
1217
1218 --
1219 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1220 RAISE fnd_api.g_exc_unexpected_error;
1221 -- END IF;
1222 END IF;
1223
1224 END LOOP;
1225 CLOSE mrc;
1226 END IF;
1227
1228 -- call inv_reservation_pvt.transfer_serial_rsv_in_LPN to
1229 -- and pass the outermost_lpn_id to transfer any serial
1230 -- reservations with no lpn in the same reservation in that lpn.
1231 inv_reservation_pvt.transfer_serial_rsv_in_LPN
1232 (
1233 x_return_status => x_return_status
1234 , x_msg_count => x_msg_count
1235 , x_msg_data => x_msg_data
1236 , p_organization_id => p_organization_id
1237 , p_inventory_item_id => p_inventory_item_id
1238 , p_lpn_id => null
1239 , p_outermost_lpn_id => p_transfer_lpn_id
1240 , p_to_subinventory_code => p_to_subinventory_code
1241 , p_to_locator_id => p_to_locator_id
1242 );
1243
1244 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1245 IF (l_debug = 1) THEN
1246 debug_print(l_api_name||':Error return status from transfer_serial_rsv_in_LPN');
1247 END IF;
1248
1249 RAISE fnd_api.g_exc_error;
1250 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1251 IF (l_debug = 1) THEN
1252 debug_print(l_api_name||':Unexpected return status from transfer_serial_rsv_in_LPN');
1253 END IF;
1254
1255 RAISE fnd_api.g_exc_unexpected_error;
1256 END IF;
1257
1258 inv_quantity_tree_pvt.clear_quantity_cache;
1259
1260 x_return_status := fnd_api.g_ret_sts_success;
1261 EXCEPTION
1262 WHEN fnd_api.g_exc_error THEN
1263 x_return_status := fnd_api.g_ret_sts_error;
1264 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1265 WHEN fnd_api.g_exc_unexpected_error THEN
1266 x_return_status := fnd_api.g_ret_sts_unexp_error;
1267 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1268 WHEN OTHERS THEN
1269 x_return_status := fnd_api.g_ret_sts_unexp_error;
1270
1271 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1272 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1273 END IF;
1274 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1275
1276 END transfer_reserved_lpn_contents;
1277
1278 -- ER 7307189 changes end
1279
1280 END inv_lpn_reservations_pvt;