[Home] [Help]
PACKAGE BODY: APPS.INV_MO_BACKORDER_PVT
Source
1 PACKAGE BODY inv_mo_backorder_pvt AS
2 /* $Header: INVMOBOB.pls 120.3 2006/09/14 09:08:21 pannapra noship $ */
3
4 -- Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MO_BACKORDER_PVT';
6 g_version_printed BOOLEAN := FALSE;
7 g_retain_ato_profile VARCHAR2(1) := fnd_profile.VALUE('WSH_RETAIN_ATO_RESERVATIONS');
8
9 PROCEDURE DEBUG(p_message IN VARCHAR2, p_module IN VARCHAR2) IS
10 BEGIN
11 IF NOT g_version_printed THEN
12 inv_log_util.trace('$Header: INVMOBOB.pls 120.3 2006/09/14 09:08:21 pannapra noship $',g_pkg_name, 9);
13 g_version_printed := TRUE;
14 END IF;
15 inv_log_util.trace(p_message, g_pkg_name || '.' || p_module, 9);
16 END;
17
18 PROCEDURE backorder_source(
19 x_return_status OUT NOCOPY VARCHAR2
20 , x_msg_count OUT NOCOPY NUMBER
21 , x_msg_data OUT NOCOPY VARCHAR2
22 , p_move_order_type NUMBER
23 , p_mo_line_rec inv_move_order_pub.trolin_rec_type
24 ) IS
25 l_shipping_attr wsh_interface.changedattributetabtype;
26 l_released_status VARCHAR2(1);
27 l_delivery_detail_id NUMBER;
28 l_source_header_id NUMBER;
29 l_source_line_id NUMBER;
30 l_qty_to_backorder NUMBER := 0;
31 l_second_qty_to_backorder NUMBER; --INVCONV dont default to zero
32
33 CURSOR c_wsh_info IS
34 SELECT delivery_detail_id, oe_header_id, oe_line_id, released_status
35 FROM wsh_inv_delivery_details_v
36 WHERE move_order_line_id = p_mo_line_rec.line_id
37 AND move_order_line_id IS NOT NULL
38 AND released_status = 'S';
39
40 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
41 BEGIN
42 x_return_status := fnd_api.g_ret_sts_success;
43 l_qty_to_backorder := NVL(p_mo_line_rec.quantity, 0) - NVL(p_mo_line_rec.quantity_delivered, 0);
44 DEBUG('l_qty_to_backorder' || l_qty_to_backorder, 'BACKORDER_SOURCE');
45
46 /*Bug#5505709. Added the below If statement to set 'l_qty_to_backorder' to 0
47 when overpicking has been done.*/
48 IF (l_qty_to_backorder < 0 ) THEN
49 l_qty_to_backorder := 0;
50 END IF;
51 -- INVCONV BEGIN
52 IF p_mo_line_rec.secondary_uom is not null and p_mo_line_rec.secondary_uom <> FND_API.G_MISS_CHAR THEN
53 l_second_qty_to_backorder :=
54 NVL(p_mo_line_rec.secondary_quantity, 0) - NVL(p_mo_line_rec.secondary_quantity_delivered, 0);
55 DEBUG('l_second_qty_to_backorder' || l_second_qty_to_backorder, 'BACKORDER_SOURCE');
56 END IF;
57 -- INVCONV END
58
59 IF p_move_order_type = inv_globals.g_move_order_pick_wave THEN
60 DEBUG('in mo type pick wabve' , 'BACKORDER_SOURCE');
61 OPEN c_wsh_info;
62 FETCH c_wsh_info INTO l_delivery_detail_id, l_source_header_id, l_source_line_id, l_released_status;
63 IF c_wsh_info%NOTFOUND THEN
64 CLOSE c_wsh_info;
65 DEBUG('NOTFOUND c_wsh_info' , 'BACKORDER_SOURCE');
66 RAISE fnd_api.g_exc_error;
67 END IF;
68 CLOSE c_wsh_info;
69
70 DEBUG('finished fetching' , 'BACKORDER_SOURCE');
71 --Call Update_Shipping_Attributes to backorder detail line
72 l_shipping_attr(1).source_header_id := l_source_header_id;
73 l_shipping_attr(1).source_line_id := l_source_line_id;
74 l_shipping_attr(1).ship_from_org_id := p_mo_line_rec.organization_id;
75 l_shipping_attr(1).released_status := l_released_status;
76 l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
77 l_shipping_attr(1).action_flag := 'B';
78 l_shipping_attr(1).cycle_count_quantity := l_qty_to_backorder;
79 l_shipping_attr(1).cycle_count_quantity2 := l_second_qty_to_backorder; -- INVCONV
80 l_shipping_attr(1).subinventory := p_mo_line_rec.from_subinventory_code;
81 l_shipping_attr(1).locator_id := p_mo_line_rec.from_locator_id;
82
83 IF (l_debug = 1) THEN
84 DEBUG('Calling Update Shipping Attributes', 'BACKORDER_SOURCE');
85 DEBUG(' Source Header ID = ' || l_shipping_attr(1).source_header_id, 'BACKORDER_SOURCE');
86 DEBUG(' Source Line ID = ' || l_shipping_attr(1).source_line_id, 'BACKORDER_SOURCE');
87 DEBUG(' Ship From Org ID = ' || l_shipping_attr(1).ship_from_org_id, 'BACKORDER_SOURCE');
88 DEBUG(' Released Status = ' || l_shipping_attr(1).released_status, 'BACKORDER_SOURCE');
89 DEBUG(' Delivery Detail ID = ' || l_shipping_attr(1).delivery_detail_id, 'BACKORDER_SOURCE');
90 DEBUG(' Action Flag = ' || l_shipping_attr(1).action_flag, 'BACKORDER_SOURCE');
91 DEBUG(' Cycle Count Qty = ' || l_shipping_attr(1).cycle_count_quantity, 'BACKORDER_SOURCE');
92 DEBUG(' Sec Cycle Count Qty= ' || l_shipping_attr(1).cycle_count_quantity2, 'BACKORDER_SOURCE'); --INVCONV
93 DEBUG(' Subinventory = ' || l_shipping_attr(1).subinventory, 'BACKORDER_SOURCE');
94 DEBUG(' Locator ID = ' || l_shipping_attr(1).locator_id, 'BACKORDER_SOURCE');
95 END IF;
96
97 wsh_interface.update_shipping_attributes(
98 p_source_code => 'INV'
99 , p_changed_attributes => l_shipping_attr
100 , x_return_status => x_return_status
101 );
102
103 IF (l_debug = 1) THEN
104 DEBUG('Updated Shipping Attributes - Return Status = ' || x_return_status, 'BACKORDER_SOURCE');
105 END IF;
106
107 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
108 RAISE fnd_api.g_exc_error;
109 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
110 RAISE fnd_api.g_exc_unexpected_error;
111 END IF;
112
113 ELSIF p_move_order_type = inv_globals.g_move_order_mfg_pick THEN
114 IF l_debug = 1 THEN
115 debug('Calling Unallocate WIP Material', 'BACKORDER_SOURCE');
116 debug(' WIP Entity ID = ' || p_mo_line_rec.txn_source_id, 'BACKORDER_SOURCE');
117 debug(' Operation Seq Num = ' || p_mo_line_rec.txn_source_line_id, 'BACKORDER_SOURCE');
118 debug(' Inventory Item ID = ' || p_mo_line_rec.inventory_item_id, 'BACKORDER_SOURCE');
119 debug(' Repetitive Sch ID = ' || p_mo_line_rec.reference_id, 'BACKORDER_SOURCE');
120 debug(' Primary Qty = ' || l_qty_to_backorder, 'BACKORDER_SOURCE');
121 END IF;
122 wip_picking_pub.unallocate_material(
123 x_return_status => x_return_status
124 , x_msg_data => x_msg_data
125 , p_wip_entity_id => p_mo_line_rec.txn_source_id
126 , p_operation_seq_num => p_mo_line_rec.txn_source_line_id
127 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
128 , p_repetitive_schedule_id => p_mo_line_rec.reference_id
129 , p_primary_quantity => l_qty_to_backorder
130 );
131 IF (l_debug = 1) THEN
132 DEBUG('Unallocated WIP Material - Return Status = ' || x_return_status, 'BACKORDER_SOURCE');
133 END IF;
134
135 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
136 RAISE fnd_api.g_exc_error;
137 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
138 RAISE fnd_api.g_exc_unexpected_error;
139 END IF;
140 END IF;
141 EXCEPTION
142 WHEN fnd_api.g_exc_error THEN
143 x_return_status := fnd_api.g_ret_sts_error;
144 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
145 WHEN fnd_api.g_exc_unexpected_error THEN
146 x_return_status := fnd_api.g_ret_sts_unexp_error;
147 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
148 WHEN OTHERS THEN
149 x_return_status := fnd_api.g_ret_sts_unexp_error;
150 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
151 fnd_msg_pub.add_exc_msg(g_pkg_name, 'BACKORDER_SOURCE');
152 END IF;
153 END backorder_source;
154
155 PROCEDURE backorder(
156 p_line_id IN NUMBER
157 , x_return_status OUT NOCOPY VARCHAR2
158 , x_msg_count OUT NOCOPY NUMBER
159 , x_msg_data OUT NOCOPY VARCHAR2
160 ) IS
161 l_mo_line_rec inv_move_order_pub.trolin_rec_type;
162 l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
163 l_mo_type NUMBER;
164 l_allow_backordering VARCHAR2(1) := 'Y';
165
166 CURSOR c_allow_backordering IS
167 SELECT 'N' FROM DUAL
168 WHERE EXISTS( SELECT 1
169 FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
170 WHERE mmtt.move_order_line_id = l_mo_line_rec.line_id
171 AND wdt.transaction_temp_id = nvl(mmtt.parent_line_id, mmtt.transaction_temp_id)
172 AND wdt.status IN (4,9));
173
174 CURSOR c_mo_type IS
175 SELECT mtrh.move_order_type
176 FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
177 WHERE mtrl.line_id = l_mo_line_rec.line_id
178 AND mtrh.header_id = mtrl.header_id;
179
180 -- INVCONV - Incorporate secondary transaction quantity below
181 CURSOR c_mmtt_info IS
182 SELECT mmtt.transaction_temp_id
183 , ABS(mmtt.primary_quantity) primary_quantity
184 , ABS(mmtt.transaction_quantity) transaction_quantity
185 , ABS(mmtt.secondary_transaction_quantity) secondary_transaction_quantity
186 , mmtt.reservation_id
187 FROM mtl_material_transactions_temp mmtt
188 WHERE mmtt.move_order_line_id = p_line_id
189 AND NOT EXISTS (SELECT 1 FROM mtl_material_transactions_temp t
190 WHERE t.parent_line_id = mmtt.transaction_temp_id)
191 FOR UPDATE NOWAIT;
192
193 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
194 BEGIN
195 x_return_status := fnd_api.g_ret_sts_success;
196
197 -- Set savepoint
198 SAVEPOINT inv_start_proc;
199 IF (l_debug = 1) THEN
200 DEBUG('Backordering for MO Line ID = ' || p_line_id, 'BACKORDER');
201 END IF;
202
203 l_mo_line_rec := inv_trolin_util.query_row(p_line_id);
204
205 -- Querying the Move Order Type of the Line.
206 OPEN c_mo_type;
207 FETCH c_mo_type INTO l_mo_type;
208 CLOSE c_mo_type;
209
210 IF (inv_install.adv_inv_installed(l_mo_line_rec.organization_id)) THEN
211 OPEN c_allow_backordering;
212 FETCH c_allow_backordering INTO l_allow_backordering;
213 CLOSE c_allow_backordering;
214 END IF;
215
216 IF (l_debug = 1) THEN
217 DEBUG('Allow BackOrdering = ' || l_allow_backordering, 'BACKORDER');
218 END IF;
219
220 IF (l_allow_backordering = 'Y') THEN
221 IF NVL(l_mo_line_rec.quantity_detailed, 0) - NVL(l_mo_line_rec.quantity_delivered, 0) > 0 THEN
222 DEBUG('Before for loop.. l_mmtt_info ' , 'BACKORDER');
223
224 FOR l_mmtt_info IN c_mmtt_info LOOP
225 DEBUG('In for loop.. l_mmtt_info ' , 'BACKORDER');
226 DEBUG('l_mmtt_info.transaction_temp_id.. ' || l_mmtt_info.transaction_temp_id , 'BACKORDER');
227 DEBUG('p_line_id.. ' || p_line_id, 'BACKORDER');
228 DEBUG('l_mmtt_info.reservation_id.. ' || l_mmtt_info.reservation_id, 'BACKORDER');
229 DEBUG('l_mmtt_info.transaction_quantity.. ' || l_mmtt_info.transaction_quantity, 'BACKORDER');
230 DEBUG('l_mmtt_info.secondary_transaction_quantity.. ' || l_mmtt_info.secondary_transaction_quantity, 'BACKORDER'); -- INVCONV
231 DEBUG('l_mmtt_info.primary_quantity.. ' || l_mmtt_info.primary_quantity, 'BACKORDER');
232 -- INVCONV - add a parameter for secondary_quantity
233 delete_details(
234 x_return_status => x_return_status
235 , x_msg_data => x_msg_data
236 , x_msg_count => x_msg_count
237 , p_transaction_temp_id => l_mmtt_info.transaction_temp_id
238 , p_move_order_line_id => p_line_id
239 , p_reservation_id => l_mmtt_info.reservation_id
240 , p_transaction_quantity => l_mmtt_info.transaction_quantity
241 , p_primary_trx_qty => l_mmtt_info.primary_quantity
242 , p_secondary_trx_qty => l_mmtt_info.secondary_transaction_quantity
243 );
244
245 dEBUG('x_return_status : ' || x_return_status, 'BACKORDER');
246 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
247 RAISE fnd_api.g_exc_unexpected_error;
248 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
249 RAISE fnd_api.g_exc_error;
250 END IF;
251 END LOOP;
252 END IF;
253 dEBUG('Before calling backorder_source ', 'BACKORDER');
254 dEBUG('l_mo_type' || l_mo_type, 'BACKORDER');
255
256 backorder_source(
257 x_return_status => x_return_status
258 , x_msg_data => x_msg_data
259 , x_msg_count => x_msg_count
260 , p_move_order_type => l_mo_type
261 , p_mo_line_rec => l_mo_line_rec
262 );
263 dEBUG('x_return_status ' || x_return_status, 'BACKORDER');
264 IF x_return_status = fnd_api.g_ret_sts_error THEN
265 RAISE fnd_api.g_exc_error;
266 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
267 RAISE fnd_api.g_exc_unexpected_error;
268 END IF;
269
270 IF l_debug = 1 THEN
271 debug('Updating Move Order Line to set Status = 5 and Qty Detailed = ' || l_mo_line_rec.quantity_delivered, 'BACKORDER');
272 debug('Updating Move Order Line Quantity = ' || l_mo_line_rec.quantity_delivered, 'BACKORDER');
273 debug('Updating Move Order Line Secondary Qty = ' || l_mo_line_rec.secondary_quantity_delivered, 'BACKORDER'); -- INVCONV
274 END IF;
275 -- INVCONV BEGIN
276 -- Fork the update statement below according to whether item tracks dual qty or not
277 IF l_mo_line_rec.secondary_uom IS NULL THEN
278 -- INVCONV Tracking in primary only
279 UPDATE mtl_txn_request_lines
280 SET line_status = 5
281 , quantity_detailed = NVL(quantity_delivered,0)
282 , quantity = NVL(quantity_delivered,0)
283 WHERE line_id = p_line_id;
284 ELSE
285 -- INVCONV Tracking in primary and secondary
286 UPDATE mtl_txn_request_lines
287 SET line_status = 5
288 , quantity_detailed = NVL(quantity_delivered,0)
289 , secondary_quantity_detailed = NVL(secondary_quantity_delivered,0)
290 , quantity = NVL(quantity_delivered,0)
291 , secondary_quantity = NVL(secondary_quantity_delivered,0)
292 WHERE line_id = p_line_id;
293 END IF;
294 -- INVCONV END
295 END IF; -- quantity detailed >= 0
296
297 dEBUG('check MO type ' || l_mo_type, 'BACKORDER');
298 IF l_mo_type = inv_globals.g_move_order_pick_wave THEN
299 dEBUG('before calling inv_transfer_order_pvt.clean_reservations ' || l_mo_line_rec.txn_source_line_id, 'BACKORDER');
300 inv_transfer_order_pvt.clean_reservations(
301 p_source_line_id => l_mo_line_rec.txn_source_line_id
302 , x_return_status => x_return_status
303 , x_msg_count => x_msg_count
304 , x_msg_data => x_msg_data
305 );
306 dEBUG('x_return_status ' || x_return_status, 'BACKORDER');
307 IF x_return_status = fnd_api.g_ret_sts_error THEN
308 IF (l_debug = 1) THEN
309 DEBUG('Clean Reservations - Expected Error occurred', 'BACKORDER');
310 END IF;
311 RAISE fnd_api.g_exc_error;
312 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
313 IF (l_debug = 1) THEN
314 DEBUG('Clean Reservations - Unexpected Error occurred', 'BACKORDER');
315 END IF;
316 RAISE fnd_api.g_exc_unexpected_error;
317 END IF;
318 END IF;
319
320 IF l_allow_backordering = 'N' THEN
321 fnd_message.set_name('WMS', 'WMS_ACTIVE_LOADED_TASKS_EXIST');
322 fnd_message.set_token('LINE_ID', p_line_id);
323 fnd_msg_pub.ADD;
324 RAISE fnd_api.g_exc_error;
325 END IF;
326
327 COMMIT;
328 EXCEPTION
329 WHEN fnd_api.g_exc_error THEN
330 x_return_status := fnd_api.g_ret_sts_error;
331 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded=> 'F');
332
333 IF l_allow_backordering = 'Y' THEN
334 ROLLBACK TO inv_start_proc;
335 END IF;
336 WHEN fnd_api.g_exc_unexpected_error THEN
337 x_return_status := fnd_api.g_ret_sts_unexp_error;
338 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded=> 'F');
339 ROLLBACK TO inv_start_proc;
340 WHEN OTHERS THEN
341 x_return_status := fnd_api.g_ret_sts_unexp_error;
342
343 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
344 fnd_msg_pub.add_exc_msg(g_pkg_name, 'BACKORDER');
345 END IF;
346
347 ROLLBACK TO inv_start_proc;
348 END backorder;
349
350 PROCEDURE delete_details(
351 p_transaction_temp_id IN NUMBER
352 , p_move_order_line_id IN NUMBER
353 , p_reservation_id IN NUMBER
354 , p_transaction_quantity IN NUMBER
355 , p_primary_trx_qty IN NUMBER
356 , p_secondary_trx_qty IN NUMBER
357 , x_return_status OUT NOCOPY VARCHAR2
358 , x_msg_count OUT NOCOPY NUMBER
359 , x_msg_data OUT NOCOPY VARCHAR2
360 ) IS
361 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
362 l_mtl_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
363 l_mtl_reservation_tbl_count NUMBER;
364 l_original_serial_number inv_reservation_global.serial_number_tbl_type;
365 l_to_serial_number inv_reservation_global.serial_number_tbl_type;
366 l_error_code NUMBER;
367 l_count NUMBER;
368 l_success BOOLEAN;
369 l_umconvert_trans_quantity NUMBER := 0;
370 l_mmtt_rec inv_mo_line_detail_util.g_mmtt_rec;
371 l_primary_uom VARCHAR2(10);
372 l_ato_item NUMBER := 0;
373 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
374
375 l_rsv_detailed_qty NUMBER;
376 l_rsv_secondary_detailed_qty NUMBER; -- INVCONV
377 l_rsv_reservation_qty NUMBER;
378 l_rsv_pri_reservation_qty NUMBER;
379 l_rsv_sec_reservation_qty NUMBER; -- INVCONV
380
381 BEGIN
382 x_return_status := fnd_api.g_ret_sts_success;
383 IF (l_debug = 1) THEN
384 DEBUG('Transaction Temp ID = ' || p_transaction_temp_id, 'DELETE_DETAILS');
385 DEBUG('Move Order Line ID = ' || p_move_order_line_id, 'DELETE_DETAILS');
386 DEBUG('Transaction Qty = ' || p_transaction_quantity, 'DELETE_DETAILS');
387 DEBUG('Secondary Qty = ' || p_secondary_trx_qty, 'DELETE_DETAILS');
388 DEBUG('Reservation ID = ' || p_reservation_id, 'DELETE_DETAILS');
389 END IF;
390
391 IF p_reservation_id IS NOT NULL THEN
392 l_mtl_reservation_rec.reservation_id := p_reservation_id;
393 inv_reservation_pub.query_reservation(
394 p_api_version_number => 1.0
395 , x_return_status => x_return_status
396 , x_msg_count => x_msg_count
397 , x_msg_data => x_msg_data
398 , p_query_input => l_mtl_reservation_rec
399 , x_mtl_reservation_tbl => l_mtl_reservation_tbl
400 , x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count
401 , x_error_code => l_error_code
402 );
403
404 DEBUG('x_return_status = ' || x_return_status, 'DELETE_DETAILS');
405 DEBUG('l_error_code = ' || l_error_code, 'DELETE_DETAILS');
406 DEBUG('l_mtl_reservation_tbl_count = ' || l_mtl_reservation_tbl_count, 'DELETE_DETAILS');
407 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
408 RAISE fnd_api.g_exc_error;
409 ELSIF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
410 RAISE fnd_api.g_exc_unexpected_error;
411 END IF;
412
413 IF l_mtl_reservation_tbl_count > 0 THEN
414 -- Bug#2621481: If reservations exist, check if the item is an ATO Item
415 -- only if the profile WSH_RETAIN_ATO_RESERVATIONS = 'Y'
416
417 IF g_retain_ato_profile = 'Y' THEN
418 DEBUG('g_retain_ato_profile = Y', 'DELETE_DETAILS');
419 BEGIN
420 SELECT 1, primary_uom_code
421 INTO l_ato_item, l_primary_uom
422 FROM mtl_system_items
423 WHERE replenish_to_order_flag = 'Y'
424 AND bom_item_type = 4
425 AND inventory_item_id = l_mtl_reservation_tbl(1).inventory_item_id
426 AND organization_id = l_mtl_reservation_tbl(1).organization_id;
427 EXCEPTION
428 WHEN OTHERS THEN
429 l_ato_item := 0;
430 END;
431 END IF;
432
433 DEBUG('l_ato_item = ' || l_ato_item, 'DELETE_DETAILS');
434
435 /* Bug# 2925113 */
436 l_rsv_detailed_qty := NVL(l_mtl_reservation_tbl(1).detailed_quantity,0);
437 l_rsv_secondary_detailed_qty := l_mtl_reservation_tbl(1).secondary_detailed_quantity; -- INVCONV - do not use NVL
438 l_rsv_reservation_qty := NVL(l_mtl_reservation_tbl(1).reservation_quantity,0);
439 l_rsv_pri_reservation_qty := NVL(l_mtl_reservation_tbl(1).primary_reservation_quantity,0);
440 l_rsv_sec_reservation_qty := l_mtl_reservation_tbl(1).secondary_reservation_quantity; -- INVCONV - do not use NVL
441 /* End of 2925113 */
442
443
444 IF l_ato_item = 1 THEN
445 DEBUG('l_ato_item = 1', 'DELETE_DETAILS');
446 -- If item is ato item, reduce the detailed quantity by the transaction
447 -- quantity and retain the reservation. Convert to primary uom before
448 -- reducing detailed quantity.
449 l_mmtt_rec := inv_mo_line_detail_util.query_row(p_transaction_temp_id);
450 l_umconvert_trans_quantity := p_transaction_quantity;
451
452
453 IF l_mmtt_rec.inventory_item_id IS NOT NULL
454 AND l_mmtt_rec.transaction_uom IS NOT NULL THEN
455 DEBUG('UOM Convert = ', 'DELETE_DETAILS');
456 l_umconvert_trans_quantity :=
457 inv_convert.inv_um_convert(
458 item_id => l_mmtt_rec.inventory_item_id
459 , PRECISION => NULL
460 , from_quantity => p_transaction_quantity
461 , from_unit => l_mmtt_rec.transaction_uom
462 , to_unit => l_primary_uom
463 , from_name => NULL
464 , to_name => NULL
465 );
466 END IF;
467
468 l_mtl_reservation_rec := l_mtl_reservation_tbl(1);
469 /* Bug# 2925113 */
470 IF(l_rsv_detailed_qty > ABS(l_umconvert_trans_quantity)) THEN
471 l_mtl_reservation_tbl(1).detailed_quantity :=
472 l_rsv_detailed_qty - ABS(l_umconvert_trans_quantity);
473 -- INVCONV BEGIN
474 -- For dual control items, compute the secondary detailed
475 IF l_mmtt_rec.secondary_uom_code is NOT NULL and l_mmtt_rec.secondary_uom_code <> FND_API.G_MISS_CHAR THEN
476 l_mtl_reservation_tbl(1).secondary_detailed_quantity :=
477 l_rsv_secondary_detailed_qty - ABS(p_secondary_trx_qty);
478 END IF;
479 -- INVCONV END
480 ELSE
481 l_mtl_reservation_tbl(1).detailed_quantity := 0;
482 -- INVCONV BEGIN
483 IF l_mmtt_rec.secondary_uom_code is NOT NULL THEN
484 l_mtl_reservation_tbl(1).secondary_detailed_quantity := 0;
485 END IF;
486 -- INVCONV END
487 END IF;
488 /* End of Bug# 2925113 */
489
490
491 DEBUG('call inv_reservation_pub.update_reservation = ', 'DELETE_DETAILS');
492 inv_reservation_pub.update_reservation(
493 p_api_version_number => 1.0
494 , x_return_status => x_return_status
495 , x_msg_count => x_msg_count
496 , x_msg_data => x_msg_data
497 , p_original_rsv_rec => l_mtl_reservation_rec
498 , p_to_rsv_rec => l_mtl_reservation_tbl(1)
499 , p_original_serial_number => l_original_serial_number
500 , p_to_serial_number => l_to_serial_number
501 );
502
503 DEBUG('x_return_status' || x_return_status, 'DELETE_DETAILS');
504 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
505 RAISE fnd_api.g_exc_error;
506 ELSIF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
507 RAISE fnd_api.g_exc_unexpected_error;
508 END IF;
509 ELSE
510 l_mtl_reservation_rec := l_mtl_reservation_tbl(1);
511 l_mmtt_rec := inv_mo_line_detail_util.query_row(p_transaction_temp_id);
512 DEBUG('Allocation UOM = ' || l_mmtt_rec.transaction_uom, 'DELETE_DETAILS');
513 DEBUG('Reservation UOM = ' || l_mtl_reservation_rec.reservation_uom_code, 'DELETE_DETAILS');
514
515 IF l_mmtt_rec.transaction_uom <> l_mtl_reservation_rec.reservation_uom_code THEN
516 l_umconvert_trans_quantity :=
517 inv_convert.inv_um_convert(
518 item_id => l_mmtt_rec.inventory_item_id
519 , PRECISION => NULL
520 , from_quantity => ABS(p_transaction_quantity)
521 , from_unit => l_mmtt_rec.transaction_uom
522 , to_unit => l_mtl_reservation_rec.reservation_uom_code
523 , from_name => NULL
524 , to_name => NULL
525 );
526
527 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
528 RAISE fnd_api.g_exc_error;
529 ELSIF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
530 RAISE fnd_api.g_exc_unexpected_error;
531 END IF;
532 ELSE
533 l_umconvert_trans_quantity := ABS(p_transaction_quantity);
534 END IF;
535
536 DEBUG('After UOM Conversion TxnQty = ' || l_umconvert_trans_quantity, 'DELETE_DETAILS');
537
538 /* Bug# 2925113 */
539 IF(l_rsv_detailed_qty > ABS(p_transaction_quantity)) THEN
540 l_mtl_reservation_tbl(1).detailed_quantity :=
541 l_rsv_detailed_qty - ABS(p_transaction_quantity);
542 -- INVCONV BEGIN
543 -- For dual control items, compute the secondary detailed
544 IF l_mmtt_rec.secondary_uom_code is NOT NULL and l_mmtt_rec.secondary_uom_code <> FND_API.G_MISS_CHAR THEN
545 l_mtl_reservation_tbl(1).secondary_detailed_quantity :=
546 l_rsv_secondary_detailed_qty - ABS(p_secondary_trx_qty);
547 END IF;
548 -- INVCONV END
549 ELSE
550 l_mtl_reservation_tbl(1).detailed_quantity := 0;
551 -- INVCONV BEGIN
552 -- For dual control items, zero the secondary detailed
553 IF l_mmtt_rec.secondary_uom_code is NOT NULL and l_mmtt_rec.secondary_uom_code <> FND_API.G_MISS_CHAR THEN
554 l_mtl_reservation_tbl(1).secondary_detailed_quantity := 0;
555 END IF;
556 -- INVCONV END
557 END IF;
558
559 IF(l_rsv_reservation_qty > ABS(l_umconvert_trans_quantity)) THEN
560 l_mtl_reservation_tbl(1).reservation_quantity :=
561 l_rsv_reservation_qty - ABS(l_umconvert_trans_quantity);
562 -- INVCONV BEGIN
563 -- For dual control items, compute the secondary reservation qty
564 IF l_mmtt_rec.secondary_uom_code is NOT NULL and l_mmtt_rec.secondary_uom_code <> FND_API.G_MISS_CHAR THEN
565 l_mtl_reservation_tbl(1).secondary_reservation_quantity :=
566 l_rsv_sec_reservation_qty - ABS(p_secondary_trx_qty);
567 END IF;
568 -- INVCONV END
569 ELSE
570 l_mtl_reservation_tbl(1).reservation_quantity := 0;
571 -- INVCONV BEGIN
572 -- For dual control items, zero the secondary reservation qty
573 IF l_mmtt_rec.secondary_uom_code is NOT NULL and l_mmtt_rec.secondary_uom_code <> FND_API.G_MISS_CHAR THEN
574 l_mtl_reservation_tbl(1).secondary_reservation_quantity := 0;
575 END IF;
576 -- INVCONV END
577 END IF;
578
579 IF(l_rsv_pri_reservation_qty > ABS(p_primary_trx_qty)) THEN
580 l_mtl_reservation_tbl(1).primary_reservation_quantity :=
581 l_rsv_pri_reservation_qty - ABS(p_primary_trx_qty);
582 ELSE
583 l_mtl_reservation_tbl(1).primary_reservation_quantity := 0;
584 END IF;
585 /* End of Bug# 2925113 */
586
587 inv_reservation_pub.update_reservation(
588 p_api_version_number => 1.0
589 , x_return_status => x_return_status
590 , x_msg_count => x_msg_count
591 , x_msg_data => x_msg_data
592 , p_original_rsv_rec => l_mtl_reservation_rec
593 , p_to_rsv_rec => l_mtl_reservation_tbl(1)
594 , p_original_serial_number => l_original_serial_number
595 , p_to_serial_number => l_to_serial_number
596 );
597
598 DEBUG('x_return_status from inv_reservation_pub.update_reservation ' || x_return_status , 'DELETE_DETAILS');
599 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
600 RAISE fnd_api.g_exc_error;
601 ELSIF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
602 RAISE fnd_api.g_exc_unexpected_error;
603 END IF;
604 END IF; -- reservation count > 0
605 END IF; -- ato item check
606 END IF;
607
608 /* Bug 5474441 Commenting out the revert locator capacity as updation of locator does not happen during pic release */
609 /*
610 -- Bug 5361517
611 debug('l_mmtt_rec.transaction_action_id = ' || l_mmtt_rec.transaction_action_id,'delete_details');
612 debug('l_mmtt_rec.transaction_status = ' || l_mmtt_rec.transaction_status,'delete_details');
613
614 IF ((l_mmtt_rec.transaction_status = 2)
615 AND (l_mmtt_rec.transaction_action_id = INV_GLOBALS.G_ACTION_STGXFR))
616 THEN
617
618 inv_loc_wms_utils.revert_loc_suggested_capacity
619 (
620 x_return_status => x_return_status
621 , x_msg_count => x_msg_count
622 , x_msg_data => x_msg_data
623 , p_organization_id => l_mmtt_rec.organization_id
624 , p_inventory_location_id => l_mmtt_rec.transfer_to_location
625 , p_inventory_item_id => l_mmtt_rec.inventory_item_id
626 , p_primary_uom_flag => 'Y'
627 , p_transaction_uom_code => NULL
628 , p_quantity => p_transaction_quantity
629 );
630 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
631 RAISE fnd_api.g_exc_error;
632 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
633 RAISE fnd_api.g_exc_unexpected_error;
634 END IF;
635 END IF;
636
637 -- End Bug 5361517
638 */
639 /* End of Bug 5474441 */
640
641 DEBUG('call inv_trx_util_pub.delete_transaction ' , 'DELETE_DETAILS');
642 inv_trx_util_pub.delete_transaction(
643 x_return_status => x_return_status
644 , x_msg_data => x_msg_data
645 , x_msg_count => x_msg_count
646 , p_transaction_temp_id => p_transaction_temp_id
647 );
648
649 DEBUG('x_return_status ' || x_return_status , 'DELETE_DETAILS');
650 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
651 RAISE fnd_api.g_exc_error;
652 ELSIF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
653 RAISE fnd_api.g_exc_unexpected_error;
654 END IF;
655 EXCEPTION
656 WHEN fnd_api.g_exc_error THEN
657 x_return_status := fnd_api.g_ret_sts_error;
658 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
659 WHEN fnd_api.g_exc_unexpected_error THEN
660 x_return_status := fnd_api.g_ret_sts_unexp_error;
661 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
662 WHEN OTHERS THEN
663 x_return_status := fnd_api.g_ret_sts_unexp_error;
664
665 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
666 fnd_msg_pub.add_exc_msg(g_pkg_name, 'DELETE_DETAILS');
667 END IF;
668 END delete_details;
669
670 END inv_mo_backorder_pvt;