[Home] [Help]
PACKAGE BODY: APPS.INV_MO_BACKORDER_PVT
Source
1 PACKAGE BODY inv_mo_backorder_pvt AS
2 /* $Header: INVMOBOB.pls 120.4.12020000.2 2013/05/15 08:22:22 brana ship $ */
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.4.12020000.2 2013/05/15 08:22:22 brana ship $',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 , status_date =sysdate --BUG 6932648
293 WHERE line_id = p_line_id;
294 END IF;
295 -- INVCONV END
296 END IF; -- quantity detailed >= 0
297
298 dEBUG('check MO type ' || l_mo_type, 'BACKORDER');
299 IF l_mo_type = inv_globals.g_move_order_pick_wave THEN
300 dEBUG('before calling inv_transfer_order_pvt.clean_reservations ' || l_mo_line_rec.txn_source_line_id, 'BACKORDER');
301 inv_transfer_order_pvt.clean_reservations(
302 p_source_line_id => l_mo_line_rec.txn_source_line_id
303 , x_return_status => x_return_status
304 , x_msg_count => x_msg_count
305 , x_msg_data => x_msg_data
306 );
307 dEBUG('x_return_status ' || x_return_status, 'BACKORDER');
308 IF x_return_status = fnd_api.g_ret_sts_error THEN
309 IF (l_debug = 1) THEN
310 DEBUG('Clean Reservations - Expected Error occurred', 'BACKORDER');
311 END IF;
312 RAISE fnd_api.g_exc_error;
313 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
314 IF (l_debug = 1) THEN
315 DEBUG('Clean Reservations - Unexpected Error occurred', 'BACKORDER');
316 END IF;
317 RAISE fnd_api.g_exc_unexpected_error;
318 END IF;
319 END IF;
320
321 IF l_allow_backordering = 'N' THEN
322 fnd_message.set_name('WMS', 'WMS_ACTIVE_LOADED_TASKS_EXIST');
323 fnd_message.set_token('LINE_ID', p_line_id);
324 fnd_msg_pub.ADD;
325 RAISE fnd_api.g_exc_error;
326 END IF;
327
328 COMMIT;
329 EXCEPTION
330 WHEN fnd_api.g_exc_error THEN
331 x_return_status := fnd_api.g_ret_sts_error;
332 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded=> 'F');
333
334 IF l_allow_backordering = 'Y' THEN
335 ROLLBACK TO inv_start_proc;
336 END IF;
337 WHEN fnd_api.g_exc_unexpected_error THEN
338 x_return_status := fnd_api.g_ret_sts_unexp_error;
339 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded=> 'F');
340 ROLLBACK TO inv_start_proc;
341 WHEN OTHERS THEN
342 x_return_status := fnd_api.g_ret_sts_unexp_error;
343
344 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
345 fnd_msg_pub.add_exc_msg(g_pkg_name, 'BACKORDER');
346 END IF;
347
348 ROLLBACK TO inv_start_proc;
349 END backorder;
350
351 PROCEDURE delete_details(
352 p_transaction_temp_id IN NUMBER
353 , p_move_order_line_id IN NUMBER
354 , p_reservation_id IN NUMBER
355 , p_transaction_quantity IN NUMBER
356 , p_primary_trx_qty IN NUMBER
357 , p_secondary_trx_qty IN NUMBER
358 , x_return_status OUT NOCOPY VARCHAR2
359 , x_msg_count OUT NOCOPY NUMBER
360 , x_msg_data OUT NOCOPY VARCHAR2
361 ) IS
362 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
363 l_mtl_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
364 l_mtl_reservation_tbl_count NUMBER;
365 l_original_serial_number inv_reservation_global.serial_number_tbl_type;
366 l_to_serial_number inv_reservation_global.serial_number_tbl_type;
367 l_error_code NUMBER;
368 l_count NUMBER;
369 l_success BOOLEAN;
370 l_umconvert_trans_quantity NUMBER := 0;
371 l_mmtt_rec inv_mo_line_detail_util.g_mmtt_rec;
372 l_primary_uom VARCHAR2(10);
373 l_ato_item NUMBER := 0;
374 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
375
376 --bugfix 16778046
377 CURSOR c_fullfill_base IS
378 SELECT nvl(fulfillment_base,'P')
379 FROM mtl_material_transactions_temp
380 WHERE transaction_temp_id = p_transaction_temp_id;
381
382 l_fulfill_base varchar2(1) := 'P' ;
383
384 l_rsv_detailed_qty NUMBER;
385 l_rsv_secondary_detailed_qty NUMBER; -- INVCONV
386 l_rsv_reservation_qty NUMBER;
387 l_rsv_pri_reservation_qty NUMBER;
388 l_rsv_sec_reservation_qty NUMBER; -- INVCONV
389
390 BEGIN
391 x_return_status := fnd_api.g_ret_sts_success;
392 IF (l_debug = 1) THEN
393 DEBUG('Transaction Temp ID = ' || p_transaction_temp_id, 'DELETE_DETAILS');
394 DEBUG('Move Order Line ID = ' || p_move_order_line_id, 'DELETE_DETAILS');
395 DEBUG('Transaction Qty = ' || p_transaction_quantity, 'DELETE_DETAILS');
396 DEBUG('Secondary Qty = ' || p_secondary_trx_qty, 'DELETE_DETAILS');
397 DEBUG('Reservation ID = ' || p_reservation_id, 'DELETE_DETAILS');
398 END IF;
399
400 --bugfix 16778046
401 OPEN c_fullfill_base;
402 FETCH c_fullfill_base INTO l_fulfill_base;
403 CLOSE c_fullfill_base;
404
405 IF p_reservation_id IS NOT NULL THEN
406 l_mtl_reservation_rec.reservation_id := p_reservation_id;
407 inv_reservation_pub.query_reservation(
408 p_api_version_number => 1.0
409 , x_return_status => x_return_status
410 , x_msg_count => x_msg_count
411 , x_msg_data => x_msg_data
412 , p_query_input => l_mtl_reservation_rec
413 , x_mtl_reservation_tbl => l_mtl_reservation_tbl
414 , x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count
415 , x_error_code => l_error_code
416 );
417
418 DEBUG('x_return_status = ' || x_return_status, 'DELETE_DETAILS');
419 DEBUG('l_error_code = ' || l_error_code, 'DELETE_DETAILS');
420 DEBUG('l_mtl_reservation_tbl_count = ' || l_mtl_reservation_tbl_count, 'DELETE_DETAILS');
421 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
422 RAISE fnd_api.g_exc_error;
423 ELSIF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
424 RAISE fnd_api.g_exc_unexpected_error;
425 END IF;
426
427 IF l_mtl_reservation_tbl_count > 0 THEN
428 -- Bug#2621481: If reservations exist, check if the item is an ATO Item
429 -- only if the profile WSH_RETAIN_ATO_RESERVATIONS = 'Y'
430
431 IF g_retain_ato_profile = 'Y' THEN
432 DEBUG('g_retain_ato_profile = Y', 'DELETE_DETAILS');
433 BEGIN
434 SELECT 1, primary_uom_code
435 INTO l_ato_item, l_primary_uom
436 FROM mtl_system_items
437 WHERE replenish_to_order_flag = 'Y'
438 AND bom_item_type = 4
439 AND inventory_item_id = l_mtl_reservation_tbl(1).inventory_item_id
440 AND organization_id = l_mtl_reservation_tbl(1).organization_id;
441 EXCEPTION
442 WHEN OTHERS THEN
443 l_ato_item := 0;
444 END;
445 END IF;
446
447 DEBUG('l_ato_item = ' || l_ato_item, 'DELETE_DETAILS');
448
449 /* Bug# 2925113 */
450 l_rsv_detailed_qty := NVL(l_mtl_reservation_tbl(1).detailed_quantity,0);
451 l_rsv_secondary_detailed_qty := l_mtl_reservation_tbl(1).secondary_detailed_quantity; -- INVCONV - do not use NVL
452 l_rsv_reservation_qty := NVL(l_mtl_reservation_tbl(1).reservation_quantity,0);
453 l_rsv_pri_reservation_qty := NVL(l_mtl_reservation_tbl(1).primary_reservation_quantity,0);
454 l_rsv_sec_reservation_qty := l_mtl_reservation_tbl(1).secondary_reservation_quantity; -- INVCONV - do not use NVL
455 /* End of 2925113 */
456
457
458 IF l_ato_item = 1 THEN
459 DEBUG('l_ato_item = 1', 'DELETE_DETAILS');
460 -- If item is ato item, reduce the detailed quantity by the transaction
461 -- quantity and retain the reservation. Convert to primary uom before
462 -- reducing detailed quantity.
463 l_mmtt_rec := inv_mo_line_detail_util.query_row(p_transaction_temp_id);
464 l_umconvert_trans_quantity := p_transaction_quantity;
465
466
467 IF l_mmtt_rec.inventory_item_id IS NOT NULL
468 AND l_mmtt_rec.transaction_uom IS NOT NULL THEN
469 DEBUG('UOM Convert = ', 'DELETE_DETAILS');
470 l_umconvert_trans_quantity :=
471 inv_convert.inv_um_convert(
472 item_id => l_mmtt_rec.inventory_item_id
473 , PRECISION => NULL
474 , from_quantity => p_transaction_quantity
475 , from_unit => l_mmtt_rec.transaction_uom
476 , to_unit => l_primary_uom
477 , from_name => NULL
478 , to_name => NULL
479 );
480 END IF;
481
482 l_mtl_reservation_rec := l_mtl_reservation_tbl(1);
483 /* Bug# 2925113 */
484 IF(l_rsv_detailed_qty > ABS(l_umconvert_trans_quantity)) THEN
485 l_mtl_reservation_tbl(1).detailed_quantity :=
486 l_rsv_detailed_qty - ABS(l_umconvert_trans_quantity);
487 -- INVCONV BEGIN
488 -- For dual control items, compute the secondary detailed
489 IF l_mmtt_rec.secondary_uom_code is NOT NULL and l_mmtt_rec.secondary_uom_code <> FND_API.G_MISS_CHAR THEN
490 l_mtl_reservation_tbl(1).secondary_detailed_quantity :=
491 l_rsv_secondary_detailed_qty - ABS(p_secondary_trx_qty);
492 END IF;
493 -- INVCONV END
494 ELSE
495 l_mtl_reservation_tbl(1).detailed_quantity := 0;
496 -- INVCONV BEGIN
497 IF l_mmtt_rec.secondary_uom_code is NOT NULL THEN
498 l_mtl_reservation_tbl(1).secondary_detailed_quantity := 0;
499 END IF;
500 -- INVCONV END
501 END IF;
502 /* End of Bug# 2925113 */
503
504
505 DEBUG('call inv_reservation_pub.update_reservation = ', 'DELETE_DETAILS');
506 inv_reservation_pub.update_reservation(
507 p_api_version_number => 1.0
508 , x_return_status => x_return_status
509 , x_msg_count => x_msg_count
510 , x_msg_data => x_msg_data
511 , p_original_rsv_rec => l_mtl_reservation_rec
512 , p_to_rsv_rec => l_mtl_reservation_tbl(1)
513 , p_original_serial_number => l_original_serial_number
514 , p_to_serial_number => l_to_serial_number
515 );
516
517 DEBUG('x_return_status' || x_return_status, 'DELETE_DETAILS');
518 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
519 RAISE fnd_api.g_exc_error;
520 ELSIF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
521 RAISE fnd_api.g_exc_unexpected_error;
522 END IF;
523 ELSE
524 l_mtl_reservation_rec := l_mtl_reservation_tbl(1);
525 l_mmtt_rec := inv_mo_line_detail_util.query_row(p_transaction_temp_id);
526 DEBUG('Allocation UOM = ' || l_mmtt_rec.transaction_uom, 'DELETE_DETAILS');
527 DEBUG('Reservation UOM = ' || l_mtl_reservation_rec.reservation_uom_code, 'DELETE_DETAILS');
528
529 IF l_mmtt_rec.transaction_uom <> l_mtl_reservation_rec.reservation_uom_code THEN
530 l_umconvert_trans_quantity :=
531 inv_convert.inv_um_convert(
532 item_id => l_mmtt_rec.inventory_item_id
533 , PRECISION => NULL
534 , from_quantity => ABS(p_transaction_quantity)
535 , from_unit => l_mmtt_rec.transaction_uom
536 , to_unit => l_mtl_reservation_rec.reservation_uom_code
537 , from_name => NULL
538 , to_name => NULL
539 );
540
541 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
542 RAISE fnd_api.g_exc_error;
543 ELSIF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
544 RAISE fnd_api.g_exc_unexpected_error;
545 END IF;
546 ELSE
547 l_umconvert_trans_quantity := ABS(p_transaction_quantity);
548 END IF;
549
550 DEBUG('After UOM Conversion TxnQty = ' || l_umconvert_trans_quantity, 'DELETE_DETAILS');
551
552 /* Bug# 2925113 */
553 IF(l_rsv_detailed_qty > ABS(p_transaction_quantity)) THEN
554 l_mtl_reservation_tbl(1).detailed_quantity :=
555 l_rsv_detailed_qty - ABS(p_transaction_quantity);
556 -- INVCONV BEGIN
557 -- For dual control items, compute the secondary detailed
558 IF l_mmtt_rec.secondary_uom_code is NOT NULL and l_mmtt_rec.secondary_uom_code <> FND_API.G_MISS_CHAR THEN
559 l_mtl_reservation_tbl(1).secondary_detailed_quantity :=
560 l_rsv_secondary_detailed_qty - ABS(p_secondary_trx_qty);
561 END IF;
562 -- INVCONV END
563 ELSE
564 l_mtl_reservation_tbl(1).detailed_quantity := 0;
565 -- INVCONV BEGIN
566 -- For dual control items, zero the secondary detailed
567 IF l_mmtt_rec.secondary_uom_code is NOT NULL and l_mmtt_rec.secondary_uom_code <> FND_API.G_MISS_CHAR THEN
568 l_mtl_reservation_tbl(1).secondary_detailed_quantity := 0;
569 END IF;
570 -- INVCONV END
571 END IF;
572
573 IF(l_rsv_reservation_qty > ABS(l_umconvert_trans_quantity)) THEN
574 l_mtl_reservation_tbl(1).reservation_quantity :=
575 l_rsv_reservation_qty - ABS(l_umconvert_trans_quantity);
576 -- INVCONV BEGIN
577 -- For dual control items, compute the secondary reservation qty
578 IF l_mmtt_rec.secondary_uom_code is NOT NULL and l_mmtt_rec.secondary_uom_code <> FND_API.G_MISS_CHAR THEN
579 l_mtl_reservation_tbl(1).secondary_reservation_quantity :=
580 l_rsv_sec_reservation_qty - ABS(p_secondary_trx_qty);
581 END IF;
582 -- INVCONV END
583 ELSE
584 l_mtl_reservation_tbl(1).reservation_quantity := 0;
585 -- INVCONV BEGIN
586 -- For dual control items, zero the secondary reservation qty
587 IF l_mmtt_rec.secondary_uom_code is NOT NULL and l_mmtt_rec.secondary_uom_code <> FND_API.G_MISS_CHAR THEN
588 l_mtl_reservation_tbl(1).secondary_reservation_quantity := 0;
589 END IF;
590 -- INVCONV END
591 END IF;
592
593 IF(l_rsv_pri_reservation_qty > ABS(p_primary_trx_qty)) AND l_fulfill_base <> 'S' THEN
594 l_mtl_reservation_tbl(1).primary_reservation_quantity :=
595 l_rsv_pri_reservation_qty - ABS(p_primary_trx_qty);
596 ELSE
597 l_mtl_reservation_tbl(1).primary_reservation_quantity := 0;
598 l_mtl_reservation_tbl(1).secondary_reservation_quantity := 0; --bugfix 16778046
599 END IF;
600
601 --bugfix 16778046
602 IF l_mmtt_rec.secondary_uom_code is NOT NULL AND l_fulfill_base = 'S' THEN
603 --
604 IF(l_rsv_sec_reservation_qty > ABS(p_secondary_trx_qty)) THEN
605 l_mtl_reservation_tbl(1).secondary_reservation_quantity := l_rsv_sec_reservation_qty - ABS(p_secondary_trx_qty);
606 ELSE
607 l_mtl_reservation_tbl(1).secondary_reservation_quantity := 0;
608 l_mtl_reservation_tbl(1).primary_reservation_quantity := 0;
609 END IF;
610 --
611 END IF ;
612 --
613 /* End of Bug# 2925113 */
614
615 inv_reservation_pub.update_reservation(
616 p_api_version_number => 1.0
617 , x_return_status => x_return_status
618 , x_msg_count => x_msg_count
619 , x_msg_data => x_msg_data
620 , p_original_rsv_rec => l_mtl_reservation_rec
621 , p_to_rsv_rec => l_mtl_reservation_tbl(1)
622 , p_original_serial_number => l_original_serial_number
623 , p_to_serial_number => l_to_serial_number
624 );
625
626 DEBUG('x_return_status from inv_reservation_pub.update_reservation ' || x_return_status , 'DELETE_DETAILS');
627 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
628 RAISE fnd_api.g_exc_error;
629 ELSIF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
630 RAISE fnd_api.g_exc_unexpected_error;
631 END IF;
632 END IF; -- reservation count > 0
633 END IF; -- ato item check
634 END IF;
635
636 /* Bug 5474441 Commenting out the revert locator capacity as updation of locator does not happen during pic release */
637 /*
638 -- Bug 5361517
639 debug('l_mmtt_rec.transaction_action_id = ' || l_mmtt_rec.transaction_action_id,'delete_details');
640 debug('l_mmtt_rec.transaction_status = ' || l_mmtt_rec.transaction_status,'delete_details');
641
642 IF ((l_mmtt_rec.transaction_status = 2)
643 AND (l_mmtt_rec.transaction_action_id = INV_GLOBALS.G_ACTION_STGXFR))
644 THEN
645
646 inv_loc_wms_utils.revert_loc_suggested_capacity
647 (
648 x_return_status => x_return_status
649 , x_msg_count => x_msg_count
650 , x_msg_data => x_msg_data
651 , p_organization_id => l_mmtt_rec.organization_id
652 , p_inventory_location_id => l_mmtt_rec.transfer_to_location
653 , p_inventory_item_id => l_mmtt_rec.inventory_item_id
654 , p_primary_uom_flag => 'Y'
655 , p_transaction_uom_code => NULL
656 , p_quantity => p_transaction_quantity
657 );
658 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
659 RAISE fnd_api.g_exc_error;
660 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
661 RAISE fnd_api.g_exc_unexpected_error;
662 END IF;
663 END IF;
664
665 -- End Bug 5361517
666 */
667 /* End of Bug 5474441 */
668
669 DEBUG('call inv_trx_util_pub.delete_transaction ' , 'DELETE_DETAILS');
670 inv_trx_util_pub.delete_transaction(
671 x_return_status => x_return_status
672 , x_msg_data => x_msg_data
673 , x_msg_count => x_msg_count
674 , p_transaction_temp_id => p_transaction_temp_id
675 );
676
677 DEBUG('x_return_status ' || x_return_status , 'DELETE_DETAILS');
678 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
679 RAISE fnd_api.g_exc_error;
680 ELSIF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
681 RAISE fnd_api.g_exc_unexpected_error;
682 END IF;
683 EXCEPTION
684 WHEN fnd_api.g_exc_error THEN
685 x_return_status := fnd_api.g_ret_sts_error;
686 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
687 WHEN fnd_api.g_exc_unexpected_error THEN
688 x_return_status := fnd_api.g_ret_sts_unexp_error;
689 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
690 WHEN OTHERS THEN
691 x_return_status := fnd_api.g_ret_sts_unexp_error;
692
693 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
694 fnd_msg_pub.add_exc_msg(g_pkg_name, 'DELETE_DETAILS');
695 END IF;
696 END delete_details;
697
698 END inv_mo_backorder_pvt;