[Home] [Help]
Skip to content
PACKAGE BODY: APPS.INV_SERIAL_PICK_PKG
Source
1 PACKAGE BODY INV_SERIAL_PICK_PKG AS
2 /* $Header: INVSNPIB.pls 120.3 2008/06/26 08:34:09 ksivasa ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_SERIAL_PICK_PKG';
5
6 PROCEDURE DEBUG(p_message IN VARCHAR2) IS
7 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8 BEGIN
9 -- inv_debug.message('wshtxn', p_message);
10 IF (l_debug = 1) THEN
11 inv_trx_util_pub.trace(p_message, 'SNPICK.', 1);
12 END IF;
13 --dbms_output.put_line(p_message);
14 -- null;
15
16 END;
17 procedure delete_move_order_reservations(
18 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
19 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
20 ,p_move_order_line_id NUMBER) IS
21
22 l_mtl_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
23 l_original_serial_number inv_reservation_global.serial_number_tbl_type;
24 l_txn_source_line_id NUMBER;
25 l_msg_count NUMBER;
26 CURSOR unstaged_reservations_csr(p_source_line_id NUMBER) IS
27 SELECT reservation_id
28 FROM mtl_reservations
29 WHERE nvl(staged_flag,'N') = 'N'
30 AND supply_source_type_id = 13
31 AND demand_source_type_id in (2,8)
32 AND demand_source_line_id = p_source_line_id;
33 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
34 BEGIN
35 x_return_status := fnd_api.g_ret_sts_success;
36
37 BEGIN
38 select txn_source_line_id
39 into l_txn_source_line_id
40 from mtl_txn_request_lines
41 where line_id = p_move_order_line_id;
42 EXCEPTION
43 WHEN NO_DATA_FOUND THEN
44 IF (l_debug = 1) THEN
45 debug('No move order find for line id'||to_char(p_move_order_line_id));
46 END IF;
47 x_return_status := fnd_api.g_ret_sts_unexp_error;
48 return;
49 END;
50 IF (l_debug = 1) THEN
51 debug('Cleaning reservations');
52 END IF;
53 FOR l_unstaged_reservation IN unstaged_reservations_csr(l_txn_source_line_id) LOOP
54 l_mtl_reservation_rec.reservation_id := l_unstaged_reservation.reservation_id;
55
56 IF (l_debug = 1) THEN
57 debug('Deleting reservation: ' || l_unstaged_reservation.reservation_id);
58 END IF;
59
60 inv_reservation_pub.delete_reservation
61 (p_api_version_number => 1.0,
62 p_init_msg_lst => fnd_api.g_true,
63 x_return_status => x_return_status,
64 x_msg_count => l_msg_count,
65 x_msg_data => x_msg_data,
66 p_rsv_rec => l_mtl_reservation_rec,
67 p_serial_number => l_original_serial_number);
68
69 IF (l_debug = 1) THEN
70 debug('after delete reservation return status is ' || x_return_status);
71 debug('Message'||x_msg_data);
72 END IF;
73 IF x_return_status = fnd_api.g_ret_sts_error THEN
74 RAISE fnd_api.g_exc_error;
75 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
76 RAISE fnd_api.g_exc_unexpected_error;
77 END IF;
78 END LOOP;
79 EXCEPTION
80 WHEN OTHERS THEN
81 x_return_status := fnd_api.g_ret_sts_unexp_error;
82 END;
83
84
85 procedure delete_move_order_allocation(
86 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
87 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
88 ,p_move_order_line_id NUMBER) IS
89 l_txn_source_line_id number;
90 l_msg_count number;
91 l_return_status VARCHAR2(1);
92 l_msg_data VARCHAR2(2000);
93 l_unstaged_so_exists NUMBER := 0;
94 /*
95 l_mtl_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
96 l_original_serial_number inv_reservation_global.serial_number_tbl_type;
97
98 CURSOR unstaged_reservations_csr(p_source_line_id NUMBER) IS
99 SELECT reservation_id
100 FROM mtl_reservations
101 WHERE nvl(staged_flag,'N') = 'N'
102 AND supply_source_type_id = 13
103 AND demand_source_type_id in (2,8)
104 AND demand_source_line_id = p_source_line_id;
105 */
106
107 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
108 BEGIN
109 x_return_status := fnd_api.g_ret_sts_success;
110
111 -- clean up the marks for the serial numbers
112 update mtl_serial_numbers
113 set group_mark_id = null
114 where serial_number in (select fm_serial_number
115 from mtl_serial_numbers_temp msnt,
116 mtl_transaction_lots_temp mtlt,
117 mtl_material_transactions_temp mmtt
118 where mmtt.move_order_line_id = p_move_order_line_id
119 and mtlt.transaction_temp_id = mmtt.transaction_temp_id
120 and msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
121 UNION
122 select fm_serial_number
123 from mtl_serial_numbers_temp msnt,
124 mtl_material_transactions_temp mmtt
125 where mmtt.move_order_line_id = p_move_order_line_id
126 and msnt.transaction_temp_id = mmtt.transaction_temp_id);
127
128 delete from mtl_serial_numbers_temp
129 where transaction_temp_id in (select transaction_temp_id
130 from mtl_material_transactions_temp mmtt
131 where mmtt.move_order_line_id = p_move_order_line_id
132 UNION
133 select mtlt.serial_transaction_temp_id
134 from mtl_material_transactions_temp mmtt,
135 mtl_transaction_lots_temp mtlt
136 where mmtt.move_order_line_id = p_move_order_line_id
137 and mtlt.transaction_temp_id = mmtt.transaction_temp_id);
138
139 delete from mtl_transaction_lots_temp
140 where transaction_temp_id in (select transaction_temp_id
141 from mtl_material_transactions_temp mmtt
142 where mmtt.move_order_line_id = p_move_order_line_id);
143
144 delete from mtl_material_transactions_temp
145 where move_order_line_id = p_move_order_line_id;
146 EXCEPTION
147 WHEN OTHERS THEN
148 x_return_status := fnd_api.g_ret_sts_unexp_error;
149 END;
150
151 procedure process_serial_picking(
152 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
153 x_error_msg OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
154 p_initial_serial IN VARCHAR2,
155 p_organization_id IN NUMBER,
156 p_move_order_line_id IN NUMBER,
157 p_serial_number IN VARCHAR2,
158 p_inventory_item_id IN NUMBER,
159 p_revision IN VARCHAR2,
160 p_subinventory_code IN VARCHAR2,
161 p_locator_id IN NUMBER,
162 p_to_subinventory_code IN VARCHAR2,
163 p_to_locator_id IN NUMBER,
164 p_reason_id IN NUMBER,
165 p_lot_number IN VARCHAR2,
166 p_wms_installed IN VARCHAR2,
167 p_transaction_action_id IN NUMBER,
168 p_transaction_type_id IN VARCHAR2,
169 p_source_type_id IN NUMBER,
170 p_user_id IN NUMBER
171 ) IS
172 CURSOR MMTT_TEMP_ID is
173 select transaction_temp_id,transaction_quantity
174 from mtl_material_transactions_temp
175 where move_order_line_id = p_move_order_line_id;
176
177 l_mmtt_rec mtl_material_transactions_temp%ROWTYPE;
178 l_trx_temp_id NUMBER;
179 l_new_temp_id NUMBER;
180 l_serial_temp_id NUMBER;
181 l_return_result NUMBER;
182 l_status_allowed VARCHAR2(1);
183 l_item_name VARCHAR2(300);
184 l_msg_count NUMBER;
185 l_msg_data VARCHAR2(2000);
186 l_trx_source_line_id NUMBER;
187 l_trx_source_id NUMBER;
188
189 l_is_revision_control BOOLEAN;
190 l_is_lot_control BOOLEAN;
191
192 l_tree_mode NUMBER := inv_quantity_tree_pub.g_transaction_mode;
193 l_quantity_type NUMBER := inv_quantity_tree_pvt.g_qoh;
194 l_onhand_source NUMBER := inv_quantity_tree_pvt.g_all_subs;
195 l_qoh NUMBER;
196 l_rqoh NUMBER;
197 l_qr NUMBER;
198 l_qs NUMBER;
199 l_att NUMBER;
200 l_atr NUMBER;
201
202 l_serial_number VARCHAR2(30);
203 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
204 l_lot_number VARCHAR2(80);
205 l_revision VARCHAR2(30);
206 l_locator_id NUMBER;
207 l_to_locator_id NUMBER;
208 l_reason_id NUMBER;
209 l_lot_expiration_date DATE;
210 l_period_id NUMBER;
211 -- Bug 7190635
212 l_ato_item NUMBER := 0;
213 l_retain_ato_profile VARCHAR2(1) := NVL(fnd_profile.VALUE('WSH_RETAIN_ATO_RESERVATIONS'),'N');
214 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
215 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
216 l_update_rec inv_reservation_global.mtl_reservation_rec_type;
217 l_error_code NUMBER;
218 l_rsv_count NUMBER;
219 l_prim_quantity_to_delete NUMBER;
220 l_return_status VARCHAR2(1);
221 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
222
223
224 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
225 -- Bug 7190635, Adding new cursor to fetch reservation id based on move order line id.
226 CURSOR mrsv_record is
227 SELECT MRSV.*
228 FROM MTL_RESERVATIONS MRSV, MTL_TXN_REQUEST_LINES MTRL
229 WHERE MTRL.LINE_ID = P_MOVE_ORDER_LINE_ID
230 AND MTRL.TXN_SOURCE_LINE_ID = MRSV.DEMAND_SOURCE_LINE_ID
231 AND Nvl(MRSV.inventory_item_id,'-9999') = Nvl(p_inventory_item_id,'-9999')
232 AND Nvl(MRSV.subinventory_code,'@@@@') = Nvl(p_subinventory_code,'@@@@')
233 AND NVL(MRSV.locator_id,'-9999') = NVL(l_locator_id,'-9999')
234 AND Nvl(MRSV.revision,'@@@@') = Nvl(l_revision,'@@@@')
235 AND Nvl(MRSV.LOT_NUMBER,'@@@@') = Nvl(l_lot_number,'@@@@')
236 AND ROWNUM=1;
237 BEGIN
238 x_return_status := fnd_api.g_ret_sts_success;
239
240 if (p_serial_number = 'NULL') then l_serial_number :=null;
241 else l_serial_number := p_serial_number; end if;
242 if (p_lot_number = 'NULL') then l_lot_number :=null;
243 else l_lot_number := p_lot_number; end if;
244 if (p_revision = 'NULL') then l_revision := null;
245 else l_revision := p_revision; end if;
246 if (p_locator_id = 0) then l_locator_id :=null;
247 else l_locator_id := p_locator_id; end if;
248 if (p_to_locator_id = 0) then l_to_locator_id :=null;
249 else l_to_locator_id := p_to_locator_id; end if;
250 if (p_reason_id = 0) then l_reason_id :=null;
251 else l_reason_id := p_reason_id; end if;
252
253 IF (l_debug = 1) THEN
254 debug('locator id:'||to_char(l_locator_id));
255 debug ('1:' ||p_initial_serial);
256 debug ('2:' ||p_organization_id);
257 debug ('3:' ||p_move_order_line_id );
258 debug ('4:' ||p_serial_number );
259 debug ('5:' ||p_inventory_item_id );
260 debug ('6:' ||p_revision );
261 debug ('7:' ||p_subinventory_code );
262 debug ('8:' ||p_locator_id );
263 debug ('9:' ||p_to_subinventory_code);
264 debug ('10:' ||p_to_locator_id );
265 debug ('11:' ||p_reason_id );
266 debug ('12:' ||p_lot_number );
267 debug ('13:' ||p_wms_installed );
268 debug ('14:' ||p_transaction_action_id);
269 debug ('15:' ||p_transaction_type_id );
270 debug ('16:' ||p_source_type_id );
271 END IF;
272
273 -- add the status checking and qty checking here
274
275 -- Check if the serial number is available to be transacted for this transaction
276 /* this has been checked in the serial LOV
277 l_status_allowed := inv_material_status_grp.is_status_applicable
278 (p_wms_installed => p_wms_installed,
279 p_trx_status_enabled => NULL,
280 p_trx_type_id => p_transaction_type_id,
281 p_lot_status_enabled => NULL,
282 p_serial_status_enabled => NULL,
283 p_organization_id => p_organization_id,
284 p_inventory_item_id => p_inventory_item_id,
285 p_sub_code => p_subinventory_code,
286 p_locator_id => l_locator_id,
287 p_lot_number => l_lot_number,
288 p_serial_number => l_serial_number,
289 p_object_type => 'A');
290
291 IF (l_debug = 1) THEN
292 inv_log_util.trace('Status Allowed: ' || l_status_allowed, 'SNPICK');
293 END IF;
294 IF l_status_allowed <> 'Y' THEN
295 select concatenated_segments
296 into l_item_name
297 from mtl_system_items_kfv
298 where inventory_item_id = p_inventory_item_id
299 and organization_id = p_organization_id;
300
301 fnd_message.set_name('INV', 'INV_TRX_SER_NA_DUE_MS');
302 fnd_message.set_token('TOKEN1', p_serial_number);
303 fnd_message.set_token('TOKEN2', l_item_name);
304 x_error_msg := fnd_message.get;
305 RAISE fnd_api.g_exc_error;
306 END IF;
307 */
308
309 SAVEPOINT process_serial;
310
311
312 -- check lot expiration date. if it is already expired, can't pick it
313 if (l_lot_number is not null ) then
314 BEGIN
315 SELECT expiration_date INTO l_lot_expiration_date
316 FROM mtl_lot_numbers
317 WHERE inventory_item_id = p_inventory_item_id
318 AND organization_id = p_organization_id
319 AND lot_number = l_lot_number;
320 --
321 IF l_lot_expiration_date IS NOT NULL
322 AND l_lot_expiration_date < Sysdate THEN
323 fnd_message.set_name('INV', 'INV_LOT_EXPIRED');
324 x_error_msg := fnd_message.get;
325 RAISE fnd_api.g_exc_error;
326 END IF;
327 --
328 EXCEPTION
329 WHEN NO_DATA_FOUND then
330 fnd_message.set_name('INV','INVALID_LOT');
331 x_error_msg := fnd_message.get;
332 RAISE fnd_api.g_exc_error;
333 END;
334 END IF;
335
336
337 -- reserve the first record to act as a base when inserting the record
338 select * into l_mmtt_rec
339 from mtl_material_transactions_temp
340 where move_order_line_id = p_move_order_line_id
341 and rownum = 1;
342
343
344
345 -- check available qty to see if the location where serial is has the availability
346 if (l_lot_number is not null ) then
347 l_is_lot_control := TRUE;
348 IF (l_debug = 1) THEN
349 debug('Lot controlled');
350 END IF;
351 ELSE
352 l_is_lot_control := FALSE;
353 END IF;
354
355 IF l_revision is not null THEN
356 IF (l_debug = 1) THEN
357 debug('revision controlled');
358 END IF;
359 l_is_revision_control := TRUE;
360 ELSE
361 l_is_revision_control := FALSE;
362 END IF;
363
364 -- Bug 7190635, The Reservation was getting deleted for ato items without check against the
365 -- WSH: Retain ATO Reservation profile.Check if the profile is set to yes and if ATO item,
366 -- the reservation needs to be updated
367
368 --Checking whether item is ATO item for the profile option ATO RETAIN RESERVATION 7190635
369 IF l_retain_ato_profile = 'Y' THEN
370 BEGIN
371 SELECT 1
372 INTO l_ato_item
373 FROM mtl_system_items msi
374 WHERE msi.inventory_item_id = p_inventory_item_id
375 AND msi.organization_id = p_organization_id
376 AND msi.replenish_to_order_flag ='Y'
377 AND msi.bom_item_type = 4;
378 EXCEPTION
379 WHEN no_data_found then
380 l_ato_item := 0;
381 END;
382 END IF;
383
384 IF (l_debug = 1) THEN
385 debug('l_ato_item is:'||l_ato_item);
386 END IF;
387
388 IF l_ato_item <> 1 THEN
389 -- to make the ATT accurate, delete the reservation first
390 if p_initial_serial = 'Y' then
391 -- delete reservation and allocation
392 for l_transaction_rec in MMTT_TEMP_ID LOOP
393 INV_MO_Cancel_PVT.reduce_rsv_allocation( x_return_status => x_return_status
394 ,x_msg_count => l_msg_count
395 ,x_msg_data => x_error_msg
396 ,p_transaction_temp_id =>l_transaction_rec.transaction_temp_id
397 ,p_quantity_to_delete =>l_transaction_rec.transaction_quantity);
398 if (x_return_status <> fnd_api.g_ret_sts_success) then
399 IF (l_debug = 1) THEN
400 debug('Error from reducing reservations');
401 END IF;
402 RAISE fnd_api.g_exc_unexpected_error;
403 end if;
404 end LOOP;
405 -- clean up the qty tree cache so that the qty tree can be rebuild
406 inv_quantity_tree_pub.clear_quantity_cache;
407 end if; --p_initial_serial
408 ELSIF l_ato_item = 1 THEN
409 if p_initial_serial = 'Y' then
410 FOR l_transaction_rec in MMTT_TEMP_ID LOOP
411
412 IF (l_debug = 1) THEN
413 debug('Calling reduce_rsv_allocation for ato item for transaction_temp_id:'||l_transaction_rec.transaction_temp_id);
414 END IF;
415 -- Bug 7190635, Currently we are deleting entire reservations which is not correct for ATO items
416 -- so we will not reduce reservation here. We will just delete entire allocations here
417 -- and reduce the reservations at later.
418 INV_MO_Cancel_PVT.reduce_rsv_allocation(
419 x_return_status => x_return_status
420 , x_msg_count => l_msg_count
421 , x_msg_data => x_error_msg
422 , p_transaction_temp_id => l_transaction_rec.transaction_temp_id
423 , p_quantity_to_delete => 0
424 , p_ato_serial_pick =>'Y');
425 if (x_return_status <> fnd_api.g_ret_sts_success) then
426 IF (l_debug = 1) THEN
427 debug('Error from reducing reservations');
428 END IF;
429 RAISE fnd_api.g_exc_unexpected_error;
430 end if;
431 END LOOP;
432 END if;
433 END IF; -- l_ato_item <> 1
434
435 -- 7190635 Reducing reservations for 1 qty. for an ATO item when the profile is set.
436 -- Here reservation_id will be queried based on Demand Source Line Id and then will
437 -- process so that only 1 qty of reservation will be reduced*/
438 If l_ato_item = 1 then
439 FOR l_reservation_rec in mrsv_record LOOP
440 l_prim_quantity_to_delete := 1;
441 l_rsv_rec.reservation_id := l_reservation_rec.reservation_id;
442 IF (l_debug = 1) THEN
443 DEBUG('query reservation');
444 END IF;
445
446 -- query reservation
447 inv_reservation_pvt.query_reservation(
448 p_api_version_number => 1.0
449 , p_init_msg_lst => fnd_api.g_true
450 , x_return_status => l_return_status
451 , x_msg_count => l_msg_count
452 , x_msg_data => l_msg_data
453 , p_query_input => l_rsv_rec
454 , x_mtl_reservation_tbl => l_rsv_tbl
455 , x_mtl_reservation_tbl_count => l_rsv_count
456 , x_error_code => l_error_code
457 );
458
459 IF l_return_status = fnd_api.g_ret_sts_error THEN
460 IF (l_debug = 1) THEN
461 DEBUG('Query reservation returned error');
462 END IF;
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('Query reservation returned unexpected error');
467 END IF;
468 RAISE fnd_api.g_exc_unexpected_error;
469 END IF;
470
471 l_update_rec := l_rsv_tbl(1);
472
473 -- update detailed quantity
474 IF l_update_rec.detailed_quantity > l_prim_quantity_to_delete THEN
475 l_update_rec.detailed_quantity := l_update_rec.detailed_quantity - l_prim_quantity_to_delete;
476 ELSE
477 l_update_rec.detailed_quantity := 0;
478 END IF;
479
480 l_update_rec.reservation_quantity := NULL;
481
482 --set primary reservation quantity
483 IF l_update_rec.primary_reservation_quantity > l_prim_quantity_to_delete THEN
484 l_update_rec.primary_reservation_quantity := l_update_rec.primary_reservation_quantity - l_prim_quantity_to_delete;
485 ELSE -- delete entire reservation
486 l_update_rec.primary_reservation_quantity := 0;
487 END IF;
488
489 IF (l_debug = 1) THEN
490 DEBUG('update reservation');
491 END IF;
492
493 -- update reservations
494 inv_reservation_pub.update_reservation(
495 p_api_version_number => 1.0
496 , p_init_msg_lst => fnd_api.g_true
497 , x_return_status => l_return_status
498 , x_msg_count => l_msg_count
499 , x_msg_data => l_msg_data
500 , p_original_rsv_rec => l_rsv_tbl(1)
501 , p_to_rsv_rec => l_update_rec
502 , p_original_serial_number => l_dummy_sn
503 , p_to_serial_number => l_dummy_sn
504 , p_validation_flag => fnd_api.g_true
505 );
506
507 IF l_return_status = fnd_api.g_ret_sts_error THEN
508 IF (l_debug = 1) THEN
509 DEBUG('Update reservation returned error');
510 END IF;
511
512 RAISE fnd_api.g_exc_error;
513 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
514 IF (l_debug = 1) THEN
515 DEBUG('Update reservation returned unexpected error');
516 END IF;
517
518 RAISE fnd_api.g_exc_unexpected_error;
519 END IF;
520 END LOOP;
521 END IF;
522 -- End of Bug 7190635
523
524 IF (l_debug = 1) THEN
525 debug('before Query Tree');
526 debug ('p_organization_id:' ||p_organization_id);
527 debug ('p_inventory_item_id:' ||p_inventory_item_id );
528 debug ('l_tree_mode:' ||l_tree_mode );
529 debug ('l_revision:' ||l_revision );
530 debug ('l_lot_number:' ||l_lot_number );
531 debug ('p_subinventory_code:' ||p_subinventory_code);
532 debug ('l_locator_id:' ||l_locator_id );
533 END IF;
534
535
536 -- Query the quantity tree for available to transact quantity
537 inv_quantity_tree_pub.query_quantities
538 (p_api_version_number => 1.0,
539 p_init_msg_lst => fnd_api.g_false,
540 x_return_status => x_return_status,
541 x_msg_count => l_msg_count,
542 x_msg_data => l_msg_data,
543 p_organization_id => p_organization_id,
544 p_inventory_item_id => p_inventory_item_id,
545 p_tree_mode => l_tree_mode,
546 p_is_revision_control => l_is_revision_control,
547 p_is_lot_control => l_is_lot_control,
548 p_is_serial_control => TRUE,
549 -- p_demand_source_type_id => p_source_type_id,
550 -- p_demand_source_header_id => l_trx_source_id,
551 -- p_demand_source_line_id => l_trx_source_line_id,
552 p_revision => l_revision,
553 p_lot_number => l_lot_number,
554 p_subinventory_code => p_subinventory_code,
555 p_locator_id => l_locator_id,
556 x_qoh => l_qoh,
557 x_rqoh => l_rqoh,
558 x_qr => l_qr,
559 x_qs => l_qs,
560 x_att => l_att,
561 x_atr => l_atr);
562 IF (l_debug = 1) THEN
563 debug('after query:');
564 debug ('l_qoh:' ||l_qoh);
565 debug ('l_rqoh:' ||l_rqoh);
566 debug ('l_qr:' ||l_qr );
567 debug ('l_qs:' ||l_qs );
568 debug ('l_att:' ||l_att );
569 debug ('l_atr:' ||l_atr );
570 END IF;
571
572 IF x_return_status <> fnd_api.g_ret_sts_success THEN
573 FND_MESSAGE.set_name('INV', 'INV_ERR_CREATETREE');
574 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
575 x_error_msg := fnd_message.get;
576
577 RAISE fnd_api.g_exc_unexpected_error;
578 END IF;
579 IF (l_debug = 1) THEN
580 inv_log_util.trace('ATT: ' || l_att, 'SNPICK');
581 END IF;
582
583 IF l_att > 0 THEN
584 -- Update the quantity tree so that the serial transaction is
585 -- reflected in the available quantity
586 inv_quantity_tree_pub.update_quantities
587 (p_api_version_number => 1.0,
588 p_init_msg_lst => fnd_api.g_false,
589 x_return_status => x_return_status,
590 x_msg_count => l_msg_count,
591 x_msg_data => l_msg_data,
592 p_organization_id => p_organization_id,
593 p_inventory_item_id => p_inventory_item_id,
594 p_tree_mode => l_tree_mode,
595 p_is_revision_control => l_is_revision_control,
596 p_is_lot_control => l_is_lot_control,
597 p_is_serial_control => TRUE,
598 -- p_demand_source_type_id => p_source_type_id,
599 -- p_demand_source_header_id => l_trx_source_id,
600 -- p_demand_source_line_id => l_trx_source_line_id,
601 p_revision => l_revision,
602 p_lot_number => l_lot_number,
603 p_subinventory_code => p_subinventory_code,
604 p_locator_id => l_locator_id,
605 p_primary_quantity => -1,
606 p_quantity_type => l_quantity_type,
607 p_onhand_source => l_onhand_source,
608 x_qoh => l_qoh,
609 x_rqoh => l_rqoh,
610 x_qr => l_qr,
611 x_qs => l_qs,
612 x_att => l_att,
613 x_atr => l_atr);
614
615 IF x_return_status <> fnd_api.g_ret_sts_success THEN
616 FND_MESSAGE.set_name('INV', 'INV_ERR_CREATETREE');
617 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
618 x_error_msg := fnd_message.get;
619 RAISE fnd_api.g_exc_unexpected_error;
620 END IF;
621 IF (l_debug = 1) THEN
622 debug('ATT in source: ' || l_att);
623 END IF;
624
625 -- update the destionation qty bug # 2470050
626 inv_quantity_tree_pub.update_quantities
627 (p_api_version_number => 1.0,
628 p_init_msg_lst => fnd_api.g_false,
629 x_return_status => x_return_status,
630 x_msg_count => l_msg_count,
631 x_msg_data => l_msg_data,
632 p_organization_id => p_organization_id,
633 p_inventory_item_id => p_inventory_item_id,
634 p_tree_mode => l_tree_mode,
635 p_is_revision_control => l_is_revision_control,
636 p_is_lot_control => l_is_lot_control,
637 p_is_serial_control => TRUE,
638 -- p_demand_source_type_id => p_source_type_id,
639 -- p_demand_source_header_id => l_trx_source_id,
640 -- p_demand_source_line_id => l_trx_source_line_id,
641 p_revision => l_revision,
642 p_lot_number => l_lot_number,
643 p_subinventory_code => p_to_subinventory_code,
644 p_locator_id => l_to_locator_id,
645 p_primary_quantity => 1,
646 p_quantity_type => l_quantity_type,
647 p_onhand_source => l_onhand_source,
648 x_qoh => l_qoh,
649 x_rqoh => l_rqoh,
650 x_qr => l_qr,
651 x_qs => l_qs,
652 x_att => l_att,
653 x_atr => l_atr);
654
655 IF x_return_status <> fnd_api.g_ret_sts_success THEN
656 FND_MESSAGE.set_name('INV', 'INV_ERR_CREATETREE');
657 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
658 x_error_msg := fnd_message.get;
659 RAISE fnd_api.g_exc_unexpected_error;
660 END IF;
661
662 ELSE
663 x_return_status := fnd_api.g_ret_sts_unexp_error;
664 FND_MESSAGE.set_name('INV', 'INV_SERIAL_EXCEED_AVAILABLE');
665 x_error_msg := fnd_message.get;
666 RAISE fnd_api.g_exc_error;
667 END IF;
668
669 -- process the serial number
670
671 SELECT mtl_material_transactions_s.NEXTVAL
672 INTO l_new_temp_id
673 FROM dual;
674
675 IF (l_debug = 1) THEN
676 debug('serial number:'||p_serial_number);
677 END IF;
678
679 --Bug 3940938, getting the account_period_id for sysdate and stamping transaction_date
680 --as sysdate with the respective period_id on MMTT.
681 INV_INV_LOVS.tdatechk(p_org_id => p_organization_id,
682 p_transaction_date => sysdate,
683 x_period_id => l_period_id);
684
685 IF (l_debug = 1) THEN
686 debug('l_period_id: '||l_period_id);
687 END IF;
688
689 IF (l_period_id = -1 or l_period_id = 0) THEN
690 x_return_status := fnd_api.g_ret_sts_error;
691 FND_MESSAGE.SET_NAME('INV', 'INV_NO_OPEN_PERIOD');
692 x_error_msg := fnd_message.get;
693 RAISE fnd_api.g_exc_error;
694 END IF;
695
696 -- insert a new record for the new serial number
697 INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP
698 (
699 TRANSACTION_HEADER_ID
700 , TRANSACTION_TEMP_ID
701 , SOURCE_CODE
702 , SOURCE_LINE_ID
703 , TRANSACTION_MODE
704 , LOCK_FLAG
705 , LAST_UPDATE_DATE
706 , LAST_UPDATED_BY
707 , CREATION_DATE
708 , CREATED_BY
709 , LAST_UPDATE_LOGIN
710 , REQUEST_ID
711 , PROGRAM_APPLICATION_ID
712 , PROGRAM_ID
713 , PROGRAM_UPDATE_DATE
714 , INVENTORY_ITEM_ID
715 , REVISION
716 , ORGANIZATION_ID
717 , SUBINVENTORY_CODE
718 , LOCATOR_ID
719 , TRANSACTION_QUANTITY
720 , PRIMARY_QUANTITY
721 , TRANSACTION_UOM
722 , TRANSACTION_COST
723 , TRANSACTION_TYPE_ID
724 , TRANSACTION_ACTION_ID
725 , TRANSACTION_SOURCE_TYPE_ID
726 , TRANSACTION_SOURCE_ID
727 , TRANSACTION_SOURCE_NAME
728 , TRANSACTION_DATE
729 , ACCT_PERIOD_ID
730 , DISTRIBUTION_ACCOUNT_ID
731 , TRANSACTION_REFERENCE
732 , REQUISITION_LINE_ID
733 , REQUISITION_DISTRIBUTION_ID
734 , REASON_ID
735 , LOT_NUMBER
736 , LOT_EXPIRATION_DATE
737 , SERIAL_NUMBER
738 , RECEIVING_DOCUMENT
739 , DEMAND_ID
740 , RCV_TRANSACTION_ID
741 , MOVE_TRANSACTION_ID
742 , COMPLETION_TRANSACTION_ID
743 , WIP_ENTITY_TYPE
744 , SCHEDULE_ID
745 , REPETITIVE_LINE_ID
746 , EMPLOYEE_CODE
747 , PRIMARY_SWITCH
748 , SCHEDULE_UPDATE_CODE
749 , SETUP_TEARDOWN_CODE
750 , ITEM_ORDERING
751 , NEGATIVE_REQ_FLAG
752 , OPERATION_SEQ_NUM
753 , PICKING_LINE_ID
754 , TRX_SOURCE_LINE_ID
755 , TRX_SOURCE_DELIVERY_ID
756 , PHYSICAL_ADJUSTMENT_ID
757 , CYCLE_COUNT_ID
758 , RMA_LINE_ID
759 , CUSTOMER_SHIP_ID
760 , CURRENCY_CODE
761 , CURRENCY_CONVERSION_RATE
762 , CURRENCY_CONVERSION_TYPE
763 , CURRENCY_CONVERSION_DATE
764 , USSGL_TRANSACTION_CODE
765 , VENDOR_LOT_NUMBER
766 , ENCUMBRANCE_ACCOUNT
767 , ENCUMBRANCE_AMOUNT
768 , SHIP_TO_LOCATION
769 , SHIPMENT_NUMBER
770 , TRANSFER_COST
771 , TRANSPORTATION_COST
772 , TRANSPORTATION_ACCOUNT
773 , FREIGHT_CODE
774 , CONTAINERS
775 , WAYBILL_AIRBILL
776 , EXPECTED_ARRIVAL_DATE
777 , TRANSFER_SUBINVENTORY
778 , TRANSFER_ORGANIZATION
779 , TRANSFER_TO_LOCATION
780 , NEW_AVERAGE_COST
781 , VALUE_CHANGE
782 , PERCENTAGE_CHANGE
783 , MATERIAL_ALLOCATION_TEMP_ID
784 , DEMAND_SOURCE_HEADER_ID
785 , DEMAND_SOURCE_LINE
786 , DEMAND_SOURCE_DELIVERY
787 , ITEM_SEGMENTS
788 , ITEM_DESCRIPTION
789 , ITEM_TRX_ENABLED_FLAG
790 , ITEM_LOCATION_CONTROL_CODE
791 , ITEM_RESTRICT_SUBINV_CODE
792 , ITEM_RESTRICT_LOCATORS_CODE
793 , ITEM_REVISION_QTY_CONTROL_CODE
794 , ITEM_PRIMARY_UOM_CODE
795 , ITEM_UOM_CLASS
796 , ITEM_SHELF_LIFE_CODE
797 , ITEM_SHELF_LIFE_DAYS
798 , ITEM_LOT_CONTROL_CODE
799 , ITEM_SERIAL_CONTROL_CODE
800 , ITEM_INVENTORY_ASSET_FLAG
801 , ALLOWED_UNITS_LOOKUP_CODE
802 , DEPARTMENT_ID
803 , DEPARTMENT_CODE
804 , WIP_SUPPLY_TYPE
805 , SUPPLY_SUBINVENTORY
806 , SUPPLY_LOCATOR_ID
807 , VALID_SUBINVENTORY_FLAG
808 , VALID_LOCATOR_FLAG
809 , LOCATOR_SEGMENTS
810 , CURRENT_LOCATOR_CONTROL_CODE
811 , NUMBER_OF_LOTS_ENTERED
812 , WIP_COMMIT_FLAG
813 , NEXT_LOT_NUMBER
814 , LOT_ALPHA_PREFIX
815 , NEXT_SERIAL_NUMBER
816 , SERIAL_ALPHA_PREFIX
817 , SHIPPABLE_FLAG
818 , POSTING_FLAG
819 , REQUIRED_FLAG
820 , PROCESS_FLAG
821 , ERROR_CODE
822 , ERROR_EXPLANATION
823 , ATTRIBUTE_CATEGORY
824 , ATTRIBUTE1
825 , ATTRIBUTE2
826 , ATTRIBUTE3
827 , ATTRIBUTE4
828 , ATTRIBUTE5
829 , ATTRIBUTE6
830 , ATTRIBUTE7
831 , ATTRIBUTE8
832 , ATTRIBUTE9
833 , ATTRIBUTE10
834 , ATTRIBUTE11
835 , ATTRIBUTE12
836 , ATTRIBUTE13
837 , ATTRIBUTE14
838 , ATTRIBUTE15
839 , MOVEMENT_ID
840 , RESERVATION_QUANTITY
841 , SHIPPED_QUANTITY
842 , TRANSACTION_LINE_NUMBER
843 , TASK_ID
844 , TO_TASK_ID
845 , SOURCE_TASK_ID
846 , PROJECT_ID
847 , SOURCE_PROJECT_ID
848 , PA_EXPENDITURE_ORG_ID
849 , TO_PROJECT_ID
850 , EXPENDITURE_TYPE
851 , FINAL_COMPLETION_FLAG
852 , TRANSFER_PERCENTAGE
853 , TRANSACTION_SEQUENCE_ID
854 , MATERIAL_ACCOUNT
855 , MATERIAL_OVERHEAD_ACCOUNT
856 , RESOURCE_ACCOUNT
857 , OUTSIDE_PROCESSING_ACCOUNT
858 , OVERHEAD_ACCOUNT
859 , FLOW_SCHEDULE
860 , COST_GROUP_ID
861 , DEMAND_CLASS
862 , QA_COLLECTION_ID
863 , KANBAN_CARD_ID
864 , OVERCOMPLETION_TRANSACTION_ID
865 , OVERCOMPLETION_PRIMARY_QTY
866 , OVERCOMPLETION_TRANSACTION_QTY
867 , END_ITEM_UNIT_NUMBER
868 , SCHEDULED_PAYBACK_DATE
869 , LINE_TYPE_CODE
870 , PARENT_TRANSACTION_TEMP_ID
871 , PUT_AWAY_STRATEGY_ID
872 , PUT_AWAY_RULE_ID
873 , PICK_STRATEGY_ID
874 , PICK_RULE_ID
875 , COMMON_BOM_SEQ_ID
876 , COMMON_ROUTING_SEQ_ID
877 , COST_TYPE_ID
878 , ORG_COST_GROUP_ID
879 , MOVE_ORDER_LINE_ID
880 , TASK_GROUP_ID
881 , PICK_SLIP_NUMBER
882 , RESERVATION_ID
883 , TRANSACTION_STATUS
884 , TRANSFER_COST_GROUP_ID
885 , LPN_ID
886 , transfer_lpn_id
887 , content_lpn_id
888 , cartonization_id
889 , standard_operation_id
890 , wms_task_type
891 , task_priority
892 , container_item_id
893 ) VALUES
894 (
895 l_mmtt_rec.TRANSACTION_HEADER_ID
896 , l_new_temp_id
897 , l_mmtt_rec.SOURCE_CODE
898 , l_mmtt_rec.SOURCE_LINE_ID
899 , l_mmtt_rec.TRANSACTION_MODE
900 , l_mmtt_rec.LOCK_FLAG
901 , sysdate
902 , p_user_id
903 , sysdate
904 , p_user_id
905 , l_mmtt_rec.LAST_UPDATE_LOGIN
906 , l_mmtt_rec.REQUEST_ID
907 , l_mmtt_rec.PROGRAM_APPLICATION_ID
908 , l_mmtt_rec.PROGRAM_ID
909 , l_mmtt_rec.PROGRAM_UPDATE_DATE
910 , l_mmtt_rec.INVENTORY_ITEM_ID
911 , l_REVISION
912 , P_ORGANIZATION_ID
913 , P_SUBINVENTORY_CODE
914 , decode(P_LOCATOR_ID,0,null, P_LOCATOR_ID)
915 , 1
916 , 1
917 , l_mmtt_rec.TRANSACTION_UOM
918 , l_mmtt_rec.TRANSACTION_COST
919 , l_mmtt_rec.TRANSACTION_TYPE_ID
920 , l_mmtt_rec.TRANSACTION_ACTION_ID
921 , l_mmtt_rec.TRANSACTION_SOURCE_TYPE_ID
922 , l_mmtt_rec.TRANSACTION_SOURCE_ID
923 , l_mmtt_rec.TRANSACTION_SOURCE_NAME
924 , sysdate --, l_mmtt_rec.TRANSACTION_DATE
925 , l_period_id --l_mmtt_rec.ACCT_PERIOD_ID
926 , l_mmtt_rec.DISTRIBUTION_ACCOUNT_ID
927 , l_mmtt_rec.TRANSACTION_REFERENCE
928 , l_mmtt_rec.REQUISITION_LINE_ID
929 , l_mmtt_rec.REQUISITION_DISTRIBUTION_ID
930 , l_REASON_ID
931 , l_mmtt_rec.LOT_NUMBER
932 , l_mmtt_rec.LOT_EXPIRATION_DATE
933 , l_mmtt_rec.SERIAL_NUMBER
934 , l_mmtt_rec.RECEIVING_DOCUMENT
935 , l_mmtt_rec.DEMAND_ID
936 , l_mmtt_rec.RCV_TRANSACTION_ID
937 , l_mmtt_rec.MOVE_TRANSACTION_ID
938 , l_mmtt_rec.COMPLETION_TRANSACTION_ID
939 , l_mmtt_rec.WIP_ENTITY_TYPE
940 , l_mmtt_rec.SCHEDULE_ID
941 , l_mmtt_rec.REPETITIVE_LINE_ID
942 , l_mmtt_rec.EMPLOYEE_CODE
943 , l_mmtt_rec.PRIMARY_SWITCH
944 , l_mmtt_rec.SCHEDULE_UPDATE_CODE
945 , l_mmtt_rec.SETUP_TEARDOWN_CODE
946 , l_mmtt_rec.ITEM_ORDERING
947 , l_mmtt_rec.NEGATIVE_REQ_FLAG
948 , l_mmtt_rec.OPERATION_SEQ_NUM
949 , l_mmtt_rec.PICKING_LINE_ID
950 , l_mmtt_rec.TRX_SOURCE_LINE_ID
951 , l_mmtt_rec.TRX_SOURCE_DELIVERY_ID
952 , l_mmtt_rec.PHYSICAL_ADJUSTMENT_ID
953 , l_mmtt_rec.CYCLE_COUNT_ID
954 , l_mmtt_rec.RMA_LINE_ID
955 , l_mmtt_rec.CUSTOMER_SHIP_ID
956 , l_mmtt_rec.CURRENCY_CODE
957 , l_mmtt_rec.CURRENCY_CONVERSION_RATE
958 , l_mmtt_rec.CURRENCY_CONVERSION_TYPE
959 , l_mmtt_rec.CURRENCY_CONVERSION_DATE
960 , l_mmtt_rec.USSGL_TRANSACTION_CODE
961 , l_mmtt_rec.VENDOR_LOT_NUMBER
962 , l_mmtt_rec.ENCUMBRANCE_ACCOUNT
963 , l_mmtt_rec.ENCUMBRANCE_AMOUNT
964 , l_mmtt_rec.SHIP_TO_LOCATION
965 , l_mmtt_rec.SHIPMENT_NUMBER
966 , l_mmtt_rec.TRANSFER_COST
967 , l_mmtt_rec.TRANSPORTATION_COST
968 , l_mmtt_rec.TRANSPORTATION_ACCOUNT
969 , l_mmtt_rec.FREIGHT_CODE
970 , l_mmtt_rec.CONTAINERS
971 , l_mmtt_rec.WAYBILL_AIRBILL
972 , l_mmtt_rec.EXPECTED_ARRIVAL_DATE
973 , p_to_SUBINVENTORY_code
974 , l_mmtt_rec.TRANSFER_ORGANIZATION
975 , decode(p_to_locator_id,0,null,p_to_locator_id)
976 , l_mmtt_rec.NEW_AVERAGE_COST
977 , l_mmtt_rec.VALUE_CHANGE
978 , l_mmtt_rec.PERCENTAGE_CHANGE
979 , l_mmtt_rec.MATERIAL_ALLOCATION_TEMP_ID
980 , l_mmtt_rec.DEMAND_SOURCE_HEADER_ID
981 , l_mmtt_rec.DEMAND_SOURCE_LINE
982 , l_mmtt_rec.DEMAND_SOURCE_DELIVERY
983 , l_mmtt_rec.ITEM_SEGMENTS
984 , l_mmtt_rec.ITEM_DESCRIPTION
985 , l_mmtt_rec.ITEM_TRX_ENABLED_FLAG
986 , l_mmtt_rec.ITEM_LOCATION_CONTROL_CODE
987 , l_mmtt_rec.ITEM_RESTRICT_SUBINV_CODE
988 , l_mmtt_rec.ITEM_RESTRICT_LOCATORS_CODE
989 , l_mmtt_rec.ITEM_REVISION_QTY_CONTROL_CODE
990 , l_mmtt_rec.ITEM_PRIMARY_UOM_CODE
991 , l_mmtt_rec.ITEM_UOM_CLASS
992 , l_mmtt_rec.ITEM_SHELF_LIFE_CODE
993 , l_mmtt_rec.ITEM_SHELF_LIFE_DAYS
994 , l_mmtt_rec.ITEM_LOT_CONTROL_CODE
995 , l_mmtt_rec.ITEM_SERIAL_CONTROL_CODE
996 , l_mmtt_rec.ITEM_INVENTORY_ASSET_FLAG
997 , l_mmtt_rec.ALLOWED_UNITS_LOOKUP_CODE
998 , l_mmtt_rec.DEPARTMENT_ID
999 , l_mmtt_rec.DEPARTMENT_CODE
1000 , l_mmtt_rec.WIP_SUPPLY_TYPE
1001 , l_mmtt_rec.SUPPLY_SUBINVENTORY
1002 , l_mmtt_rec.SUPPLY_LOCATOR_ID
1003 , l_mmtt_rec.VALID_SUBINVENTORY_FLAG
1004 , l_mmtt_rec.VALID_LOCATOR_FLAG
1005 , l_mmtt_rec.LOCATOR_SEGMENTS
1006 , l_mmtt_rec.CURRENT_LOCATOR_CONTROL_CODE
1007 , l_mmtt_rec.NUMBER_OF_LOTS_ENTERED
1008 , l_mmtt_rec.WIP_COMMIT_FLAG
1009 , l_mmtt_rec.NEXT_LOT_NUMBER
1010 , l_mmtt_rec.LOT_ALPHA_PREFIX
1011 , l_mmtt_rec.NEXT_SERIAL_NUMBER
1012 , l_mmtt_rec.SERIAL_ALPHA_PREFIX
1013 , l_mmtt_rec.SHIPPABLE_FLAG
1014 , l_mmtt_rec.POSTING_FLAG
1015 , l_mmtt_rec.REQUIRED_FLAG
1016 , l_mmtt_rec.PROCESS_FLAG
1017 , l_mmtt_rec.ERROR_CODE
1018 , l_mmtt_rec.ERROR_EXPLANATION
1019 , l_mmtt_rec.ATTRIBUTE_CATEGORY
1020 , l_mmtt_rec.ATTRIBUTE1
1021 , l_mmtt_rec.ATTRIBUTE2
1022 , l_mmtt_rec.ATTRIBUTE3
1023 , l_mmtt_rec.ATTRIBUTE4
1024 , l_mmtt_rec.ATTRIBUTE5
1025 , l_mmtt_rec.ATTRIBUTE6
1026 , l_mmtt_rec.ATTRIBUTE7
1027 , l_mmtt_rec.ATTRIBUTE8
1028 , l_mmtt_rec.ATTRIBUTE9
1029 , l_mmtt_rec.ATTRIBUTE10
1030 , l_mmtt_rec.ATTRIBUTE11
1031 , l_mmtt_rec.ATTRIBUTE12
1032 , l_mmtt_rec.ATTRIBUTE13
1033 , l_mmtt_rec.ATTRIBUTE14
1034 , l_mmtt_rec.ATTRIBUTE15
1035 , l_mmtt_rec.MOVEMENT_ID
1036 , null -- reservation quantity
1037 , l_mmtt_rec.SHIPPED_QUANTITY
1038 , l_mmtt_rec.TRANSACTION_LINE_NUMBER
1039 , l_mmtt_rec.TASK_ID
1040 , l_mmtt_rec.TO_TASK_ID
1041 , l_mmtt_rec.SOURCE_TASK_ID
1042 , l_mmtt_rec.PROJECT_ID
1043 , l_mmtt_rec.SOURCE_PROJECT_ID
1044 , l_mmtt_rec.PA_EXPENDITURE_ORG_ID
1045 , l_mmtt_rec.TO_PROJECT_ID
1046 , l_mmtt_rec.EXPENDITURE_TYPE
1047 , l_mmtt_rec.FINAL_COMPLETION_FLAG
1048 , l_mmtt_rec.TRANSFER_PERCENTAGE
1049 , l_mmtt_rec.TRANSACTION_SEQUENCE_ID
1050 , l_mmtt_rec.MATERIAL_ACCOUNT
1051 , l_mmtt_rec.MATERIAL_OVERHEAD_ACCOUNT
1052 , l_mmtt_rec.RESOURCE_ACCOUNT
1053 , l_mmtt_rec.OUTSIDE_PROCESSING_ACCOUNT
1054 , l_mmtt_rec.OVERHEAD_ACCOUNT
1055 , l_mmtt_rec.FLOW_SCHEDULE
1056 , l_mmtt_rec.COST_GROUP_ID
1057 , l_mmtt_rec.DEMAND_CLASS
1058 , l_mmtt_rec.QA_COLLECTION_ID
1059 , l_mmtt_rec.KANBAN_CARD_ID
1060 , l_mmtt_rec.OVERCOMPLETION_TRANSACTION_ID
1061 , l_mmtt_rec.OVERCOMPLETION_PRIMARY_QTY
1062 , l_mmtt_rec.OVERCOMPLETION_TRANSACTION_QTY
1063 , l_mmtt_rec.END_ITEM_UNIT_NUMBER
1064 , l_mmtt_rec.SCHEDULED_PAYBACK_DATE
1065 , l_mmtt_rec.LINE_TYPE_CODE
1066 , l_mmtt_rec.PARENT_TRANSACTION_TEMP_ID
1067 , l_mmtt_rec.PUT_AWAY_STRATEGY_ID
1068 , l_mmtt_rec.PUT_AWAY_RULE_ID
1069 , l_mmtt_rec.PICK_STRATEGY_ID
1070 , l_mmtt_rec.PICK_RULE_ID
1071 , l_mmtt_rec.COMMON_BOM_SEQ_ID
1072 , l_mmtt_rec.COMMON_ROUTING_SEQ_ID
1073 , l_mmtt_rec.COST_TYPE_ID
1074 , l_mmtt_rec.ORG_COST_GROUP_ID
1075 , l_mmtt_rec.MOVE_ORDER_LINE_ID
1076 , l_mmtt_rec.TASK_GROUP_ID
1077 , l_mmtt_rec.PICK_SLIP_NUMBER
1078 , null -- reservation id
1079 , 3 -- TRANSACTION_STATUS
1080 , l_mmtt_rec.TRANSFER_COST_GROUP_ID
1081 , l_mmtt_rec.lpn_id
1082 , l_mmtt_rec.transfer_lpn_id
1083 , l_mmtt_rec.content_lpn_id
1084 , l_mmtt_rec.cartonization_id
1085 , l_mmtt_rec.standard_operation_id
1086 , l_mmtt_rec.wms_task_type
1087 , l_mmtt_rec.task_priority
1088 , l_mmtt_rec.container_item_id
1089 );
1090
1091
1092 IF (l_debug = 1) THEN
1093 debug('After inserting to the MMTT');
1094 END IF;
1095
1096 l_serial_temp_id := l_new_temp_id;
1097 -- insert the lot number if it is not null
1098 if (l_lot_number is not null) then
1099 IF (l_debug = 1) THEN
1100 debug('Inserting the lot '||p_lot_number);
1101 END IF;
1102
1103
1104 l_return_result := INV_TRX_UTIL_PUB.insert_lot_trx
1105 (p_trx_tmp_id => l_new_temp_id,
1106 p_user_id => p_user_id,
1107 p_lot_number => p_lot_number,
1108 p_trx_qty => 1,
1109 p_pri_qty => 1,
1110 x_ser_trx_id => l_serial_temp_id,
1111 x_proc_msg => x_error_msg);
1112
1113 if (l_return_result = 1) then
1114 x_return_status := fnd_api.g_ret_sts_unexp_error;
1115 RAISE fnd_api.g_exc_unexpected_error;
1116 end if;
1117 end if;
1118
1119 IF (l_debug = 1) THEN
1120 debug('Before inserting serial number, temp_id is '||to_char(l_new_temp_id));
1121 END IF;
1122
1123 -- insert the serial number
1124 IF (l_debug = 1) THEN
1125 debug('serial number:'||p_serial_number);
1126 END IF;
1127
1128 if (l_serial_number is not null ) then
1129 IF (l_debug = 1) THEN
1130 debug('inserting serial number '||p_serial_number);
1131 END IF;
1132
1133 l_return_result := inv_trx_util_pub.insert_ser_trx
1134 (p_trx_tmp_id => l_serial_temp_id,
1135 p_user_id => p_user_id,
1136 p_fm_ser_num => p_serial_number,
1137 p_to_ser_num => NULL,
1138 x_proc_msg => x_error_msg);
1139
1140 if (l_return_result = 1) then
1141 IF (l_debug = 1) THEN
1142 debug('failed to insert serial '||p_serial_number);
1143 END IF;
1144 x_return_status := fnd_api.g_ret_sts_unexp_error;
1145 RAISE fnd_api.g_exc_unexpected_error;
1146 end if;
1147 IF (l_debug = 1) THEN
1148 debug('inserted the serial number '||p_serial_number);
1149 END IF;
1150 end if;
1151
1152
1153 EXCEPTION
1154 WHEN fnd_api.g_exc_error THEN
1155 ROLLBACK TO PROCESS_SERIAL;
1156 x_return_status := fnd_api.g_ret_sts_error;
1157 WHEN OTHERS THEN
1158 ROLLBACK TO PROCESS_SERIAL;
1159 IF (l_debug = 1) THEN
1160 debug('Others error' || Sqlerrm);
1161 END IF;
1162 x_return_status := fnd_api.g_ret_sts_unexp_error;
1163 END;
1164
1165 procedure backorder_nonpick_quantity(
1166 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1167 x_error_msg OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1168 p_quantity IN NUMBER,
1169 p_move_order_line_id IN NUMBER) IS
1170 l_source_header_id NUMBER;
1171 l_source_line_id NUMBER;
1172 l_delivery_detail_id NUMBER;
1173 l_organization_id NUMBER;
1174 l_sub_code VARCHAR(30);
1175 l_locator_id NUMBER;
1176 l_shipping_attr WSH_INTERFACE.ChangedAttributeTabType;
1177 l_api_return_status VARCHAR2(1);
1178
1179 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1180 BEGIN
1181 x_return_status := fnd_api.g_ret_sts_success;
1182
1183 BEGIN
1184 select organization_id,from_subinventory_code,from_locator_id
1185 into l_organization_id,l_sub_code,l_locator_id
1186 from mtl_txn_request_lines
1187 where line_id = p_move_order_line_id;
1188 EXCEPTION
1189 WHEN NO_DATA_FOUND THEN
1190 IF (l_debug = 1) THEN
1191 debug('No move order line found');
1192 END IF;
1193 raise FND_API.G_EXC_ERROR;
1194 END;
1195
1196 BEGIN
1197 select source_header_id,source_line_id,delivery_detail_id
1198 into l_source_header_id,l_source_line_id,l_delivery_detail_id
1199 from wsh_delivery_details
1200 where move_order_line_id = p_move_order_line_id
1201 and released_status = 'S';
1202 EXCEPTION
1203 WHEN NO_DATA_FOUND THEN
1204 IF (l_debug = 1) THEN
1205 debug('No delivery detail found');
1206 END IF;
1207 raise FND_API.G_EXC_ERROR;
1208 END;
1209
1210 -- update shipping to back order the quantity
1211 --Call Update_Shipping_Attributes to backorder detail line
1212 l_shipping_attr(1).source_header_id := l_source_header_id;
1213 l_shipping_attr(1).source_line_id := l_source_line_id;
1214 l_shipping_attr(1).ship_from_org_id := l_organization_id;
1215 l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
1216 l_shipping_attr(1).action_flag := 'B';
1217 l_shipping_attr(1).cycle_count_quantity := p_quantity;
1218 l_shipping_attr(1).subinventory :=
1219 l_sub_code;
1220 l_shipping_attr(1).locator_id := l_locator_id;
1221
1222
1223 WSH_INTERFACE.Update_Shipping_Attributes
1224 (p_source_code => 'INV',
1225 p_changed_attributes => l_shipping_attr,
1226 x_return_status => l_api_return_status
1227 );
1228 if( l_api_return_status = FND_API.G_RET_STS_ERROR ) then
1229 IF (l_debug = 1) THEN
1230 debug('return error from update shipping attributes');
1231 END IF;
1232 raise FND_API.G_EXC_ERROR;
1233 elsif l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1234 IF (l_debug = 1) THEN
1235 debug('return error from update shipping attributes');
1236 END IF;
1237 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1238 end if;
1239
1240
1241 EXCEPTION
1242 WHEN others THEN
1243 x_return_status := fnd_api.g_ret_sts_unexp_error;
1244 END backorder_nonpick_quantity;
1245
1246 END INV_SERIAL_PICK_PKG;