[Home] [Help]
PACKAGE BODY: APPS.WMS_RCV_PUP_PVT
Source
1 PACKAGE BODY wms_rcv_pup_pvt AS
2 /* $Header: WMSRCVPB.pls 120.8.12020000.3 2012/08/10 10:28:49 rdudani ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_RCV_PUP_PVT';
5
6 PROCEDURE print_debug(p_err_msg VARCHAR2,
7 p_level NUMBER default 4)
8 IS
9 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 BEGIN
11 IF (l_debug = 1) THEN
12 inv_mobile_helper_functions.tracelog
13 (p_err_msg => p_err_msg,
14 p_module => g_pkg_name||'($Revision: 120.8.12020000.3 $)',
15 p_level => p_level);
16 END IF;
17
18 END print_debug;
19
20 FUNCTION insert_lot_serial(p_transaction_temp_id IN NUMBER
21 ,p_organization_id IN NUMBER
22 ,p_item_id IN NUMBER
23 ,x_return_status OUT nocopy VARCHAR2
24 ,x_msg_count OUT nocopy NUMBER
25 ,x_msg_data OUT nocopy VARCHAR2)
26 RETURN NUMBER IS
27
28 l_group_id NUMBER;
29 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
30 l_lot_number VARCHAR2(80);
31 l_transaction_quantity NUMBER;
32 l_primary_quantity NUMBER;
33 l_serial_txn_tmp_id NUMBER;
34 l_intf_id NUMBER := NULL;
35 l_transaction_temp_id NUMBER;
36 l_new_ser_txn_id NUMBER;
37 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
38
39 l_lot_expiration_date DATE;
40 l_lot_status_id NUMBER;
41 l_lot_description VARCHAR2(256);
42 l_lot_vendor_name VARCHAR2(240);
43 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
44 l_lot_supplier_lot_number VARCHAR2(80);
45 l_lot_origination_date DATE;
46 l_lot_date_code VARCHAR2(150);
47 l_lot_grade_code VARCHAR2(150);
48 l_lot_change_date DATE;
49 l_lot_maturity_date DATE;
50 l_lot_retest_date DATE;
51 l_lot_age NUMBER;
52 l_lot_item_size NUMBER;
53 l_lot_color VARCHAR2(150);
54 l_lot_volume NUMBER;
55 l_lot_volume_uom VARCHAR2(3);
56 l_lot_place_of_origin VARCHAR2(150);
57 l_lot_best_by_date DATE;
58 l_lot_length NUMBER;
59 l_lot_length_uom VARCHAR2(3);
60 l_lot_recycled_content NUMBER;
61 l_lot_thickness NUMBER;
62 l_lot_thickness_uom VARCHAR2(3);
63 l_lot_width NUMBER;
64 l_lot_width_uom VARCHAR2(3);
65 l_lot_curl_wrinkle_fold VARCHAR2(150);
66 l_lot_vendor_id NUMBER;
67 l_lot_territory_code VARCHAR2(30);
68 l_lot_lot_attribute_category VARCHAR2(30);
69 l_lot_c_attribute1 VARCHAR2(150);
70 l_lot_c_attribute2 VARCHAR2(150);
71 l_lot_c_attribute3 VARCHAR2(150);
72 l_lot_c_attribute4 VARCHAR2(150);
73 l_lot_c_attribute5 VARCHAR2(150);
74 l_lot_c_attribute6 VARCHAR2(150);
75 l_lot_c_attribute7 VARCHAR2(150);
76 l_lot_c_attribute8 VARCHAR2(150);
77 l_lot_c_attribute9 VARCHAR2(150);
78 l_lot_c_attribute10 VARCHAR2(150);
79 l_lot_c_attribute11 VARCHAR2(150);
80 l_lot_c_attribute12 VARCHAR2(150);
81 l_lot_c_attribute13 VARCHAR2(150);
82 l_lot_c_attribute14 VARCHAR2(150);
83 l_lot_c_attribute15 VARCHAR2(150);
84 l_lot_c_attribute16 VARCHAR2(150);
85 l_lot_c_attribute17 VARCHAR2(150);
86 l_lot_c_attribute18 VARCHAR2(150);
87 l_lot_c_attribute19 VARCHAR2(150);
88 l_lot_c_attribute20 VARCHAR2(150);
89 l_lot_d_attribute1 DATE;
90 l_lot_d_attribute2 DATE;
91 l_lot_d_attribute3 DATE;
92 l_lot_d_attribute4 DATE;
93 l_lot_d_attribute5 DATE;
94 l_lot_d_attribute6 DATE;
95 l_lot_d_attribute7 DATE;
96 l_lot_d_attribute8 DATE;
97 l_lot_d_attribute9 DATE;
98 l_lot_d_attribute10 DATE;
99 l_lot_n_attribute1 NUMBER;
100 l_lot_n_attribute2 NUMBER;
101 l_lot_n_attribute3 NUMBER;
102 l_lot_n_attribute4 NUMBER;
103 l_lot_n_attribute5 NUMBER;
104 l_lot_n_attribute6 NUMBER;
105 l_lot_n_attribute7 NUMBER;
106 l_lot_n_attribute8 NUMBER;
107 l_lot_n_attribute9 NUMBER;
108 l_lot_n_attribute10 NUMBER;
109 l_lot_attribute_category VARCHAR2(30);
110 l_lot_attribute1 VARCHAR2(150);
111 l_lot_attribute2 VARCHAR2(150);
112 l_lot_attribute3 VARCHAR2(150);
113 l_lot_attribute4 VARCHAR2(150);
114 l_lot_attribute5 VARCHAR2(150);
115 l_lot_attribute6 VARCHAR2(150);
116 l_lot_attribute7 VARCHAR2(150);
117 l_lot_attribute8 VARCHAR2(150);
118 l_lot_attribute9 VARCHAR2(150);
119 l_lot_attribute10 VARCHAR2(150);
120 l_lot_attribute11 VARCHAR2(150);
121 l_lot_attribute12 VARCHAR2(150);
122 l_lot_attribute13 VARCHAR2(150);
123 l_lot_attribute14 VARCHAR2(150);
124 l_lot_attribute15 VARCHAR2(150);
125
126 CURSOR msnt_recs(l_txn_tmp_id NUMBER) IS
127 SELECT
128 fm_serial_number
129 ,to_serial_number
130 ,transaction_temp_id
131 ,vendor_serial_number
132 ,vendor_lot_number
133 ,parent_serial_number
134 ,origination_date
135 ,territory_code
136 ,time_since_new
137 ,cycles_since_new
138 ,time_since_overhaul
139 ,cycles_since_overhaul
140 ,time_since_repair
141 ,cycles_since_repair
142 ,time_since_visit
143 ,cycles_since_visit
144 ,time_since_mark
145 ,cycles_since_mark
146 ,number_of_repairs
147 ,serial_attribute_category
148 ,c_attribute1
149 ,c_attribute2
150 ,c_attribute3
151 ,c_attribute4
152 ,c_attribute5
153 ,c_attribute6
154 ,c_attribute7
155 ,c_attribute8
156 ,c_attribute9
157 ,c_attribute10
158 ,c_attribute11
159 ,c_attribute12
160 ,c_attribute13
161 ,c_attribute14
162 ,c_attribute15
163 ,c_attribute16
164 ,c_attribute17
165 ,c_attribute18
166 ,c_attribute19
167 ,c_attribute20
168 ,d_attribute1
169 ,d_attribute2
170 ,d_attribute3
171 ,d_attribute4
172 ,d_attribute5
173 ,d_attribute6
174 ,d_attribute7
175 ,d_attribute8
176 ,d_attribute9
177 ,d_attribute10
178 ,n_attribute1
179 ,n_attribute2
180 ,n_attribute3
181 ,n_attribute4
182 ,n_attribute5
183 ,n_attribute6
184 ,n_attribute7
185 ,n_attribute8
186 ,n_attribute9
187 ,n_attribute10
188 FROM
189 mtl_serial_numbers_temp
190 WHERE
191 transaction_temp_id = l_txn_tmp_id;
192
193 BEGIN
194
195 IF (l_debug = 1) THEN
196 print_debug('INSERT_LOT_SERIAL: Entering...');
197 END IF;
198
199 x_return_status := g_ret_sts_success;
200
201 /* Get MTLT associated with the temp MMTT */
202 BEGIN
203 SELECT
204 lot_number
205 ,transaction_quantity
206 ,primary_quantity
207 ,serial_transaction_temp_id
208 , lot_expiration_date
209 , status_id
210 , description
211 , vendor_name
212 , supplier_lot_number
213 , origination_date
214 , date_code
215 , grade_code
216 , change_date
217 , maturity_date
218 , retest_date
219 , age
220 , item_size
221 , color
222 , volume
223 , volume_uom
224 , place_of_origin
225 , best_by_date
226 , length
227 , length_uom
228 , recycled_content
229 , thickness
230 , thickness_uom
231 , width
232 , width_uom
233 , curl_wrinkle_fold
234 , vendor_id
235 , territory_code
236 , lot_attribute_category
237 , c_attribute1
238 , c_attribute2
239 , c_attribute3
240 , c_attribute4
241 , c_attribute5
242 , c_attribute6
243 , c_attribute7
244 , c_attribute8
245 , c_attribute9
246 , c_attribute10
247 , c_attribute11
248 , c_attribute12
249 , c_attribute13
250 , c_attribute14
251 , c_attribute15
252 , c_attribute16
253 , c_attribute17
254 , c_attribute18
255 , c_attribute19
256 , c_attribute20
257 , d_attribute1
258 , d_attribute2
259 , d_attribute3
260 , d_attribute4
261 , d_attribute5
262 , d_attribute6
263 , d_attribute7
264 , d_attribute8
265 , d_attribute9
266 , d_attribute10
267 , n_attribute1
268 , n_attribute2
269 , n_attribute3
270 , n_attribute4
271 , n_attribute5
272 , n_attribute6
273 , n_attribute7
274 , n_attribute8
275 , n_attribute9
276 , n_attribute10
277 , attribute_category
278 , attribute1
279 , attribute2
280 , attribute3
281 , attribute4
282 , attribute5
283 , attribute6
284 , attribute7
285 , attribute8
286 , attribute9
287 , attribute10
288 , attribute11
289 , attribute12
290 , attribute13
291 , attribute14
292 , attribute15
293 INTO
294 l_lot_number
295 ,l_transaction_quantity
296 ,l_primary_quantity
297 ,l_serial_txn_tmp_id
298 ,l_lot_expiration_date
299 ,l_lot_status_id
300 ,l_lot_description
301 ,l_lot_vendor_name
302 ,l_lot_supplier_lot_number
303 ,l_lot_origination_date
304 ,l_lot_date_code
305 ,l_lot_grade_code
306 ,l_lot_change_date
307 ,l_lot_maturity_date
308 ,l_lot_retest_date
309 ,l_lot_age
310 ,l_lot_item_size
311 ,l_lot_color
312 ,l_lot_volume
313 ,l_lot_volume_uom
314 ,l_lot_place_of_origin
315 ,l_lot_best_by_date
316 ,l_lot_length
317 ,l_lot_length_uom
318 ,l_lot_recycled_content
319 ,l_lot_thickness
320 ,l_lot_thickness_uom
321 ,l_lot_width
322 ,l_lot_width_uom
323 ,l_lot_curl_wrinkle_fold
324 ,l_lot_vendor_id
325 ,l_lot_territory_code
326 ,l_lot_lot_attribute_category
327 ,l_lot_c_attribute1
328 ,l_lot_c_attribute2
329 ,l_lot_c_attribute3
330 ,l_lot_c_attribute4
331 ,l_lot_c_attribute5
332 ,l_lot_c_attribute6
333 ,l_lot_c_attribute7
334 ,l_lot_c_attribute8
335 ,l_lot_c_attribute9
336 ,l_lot_c_attribute10
337 ,l_lot_c_attribute11
338 ,l_lot_c_attribute12
339 ,l_lot_c_attribute13
340 ,l_lot_c_attribute14
341 ,l_lot_c_attribute15
342 ,l_lot_c_attribute16
343 ,l_lot_c_attribute17
344 ,l_lot_c_attribute18
345 ,l_lot_c_attribute19
346 ,l_lot_c_attribute20
347 ,l_lot_d_attribute1
348 ,l_lot_d_attribute2
349 ,l_lot_d_attribute3
350 ,l_lot_d_attribute4
351 ,l_lot_d_attribute5
352 ,l_lot_d_attribute6
353 ,l_lot_d_attribute7
354 ,l_lot_d_attribute8
355 ,l_lot_d_attribute9
356 ,l_lot_d_attribute10
357 ,l_lot_n_attribute1
358 ,l_lot_n_attribute2
359 ,l_lot_n_attribute3
360 ,l_lot_n_attribute4
361 ,l_lot_n_attribute5
362 ,l_lot_n_attribute6
363 ,l_lot_n_attribute7
364 ,l_lot_n_attribute8
365 ,l_lot_n_attribute9
366 ,l_lot_n_attribute10
367 ,l_lot_attribute_category
368 ,l_lot_attribute1
369 ,l_lot_attribute2
370 ,l_lot_attribute3
371 ,l_lot_attribute4
372 ,l_lot_attribute5
373 ,l_lot_attribute6
374 ,l_lot_attribute7
375 ,l_lot_attribute8
376 ,l_lot_attribute9
377 ,l_lot_attribute10
378 ,l_lot_attribute11
379 ,l_lot_attribute12
380 ,l_lot_attribute13
381 ,l_lot_attribute14
382 ,l_lot_attribute15
383 FROM
384 mtl_transaction_lots_temp
385 WHERE
386 transaction_temp_id = p_transaction_temp_id;
387 EXCEPTION
388 WHEN no_data_found THEN
389 l_lot_number := NULL;
390 WHEN OTHERS THEN
391 IF (l_debug = 1) THEN
392 print_debug('INSERT_LOT_SERIAL: ERROR - More than 1 row of MTLT associated with MMTT');
393 END IF;
394 RAISE fnd_api.g_exc_error;
395 END;
396
397 l_transaction_temp_id := p_transaction_temp_id;
398
399 IF (l_lot_number IS NOT NULL) THEN
400
401 IF (l_debug = 1) THEN
402 print_debug('INSERT_LOT_SERIAL: MTLT exists. ID:'||l_lot_number);
403 END IF;
404
405 /* Create a dummy RT ID */
406 SELECT rcv_transactions_interface_s.NEXTVAL
407 INTO l_intf_id
408 FROM dual;
409
410 inv_rcv_integration_apis.insert_mtli
411 (p_api_version => 1.0
412 ,x_return_status => x_return_status
413 ,x_msg_count => x_msg_count
414 ,x_msg_data => x_msg_data
415 ,p_att_exist => 'N'
416 ,p_transaction_interface_id => l_transaction_temp_id
417 ,p_lot_number => l_lot_number
418 ,p_transaction_quantity => l_transaction_quantity
419 ,p_primary_quantity => l_primary_quantity
420 ,p_organization_id => p_organization_id
421 ,p_inventory_item_id => p_item_id -- from tmp mmtt
422 ,p_expiration_date => l_lot_expiration_date
423 ,p_status_id => l_lot_status_id
424 ,x_serial_transaction_temp_id => l_new_ser_txn_id
425 ,p_product_transaction_id => l_intf_id
426 ,p_product_code => 'RCV'
427 ,p_description => l_lot_description
428 ,p_vendor_name => l_lot_vendor_name
429 ,p_supplier_lot_number => l_lot_supplier_lot_number
430 ,p_origination_date => l_lot_origination_date
431 ,p_date_code => l_lot_date_code
432 ,p_grade_code => l_lot_grade_code
433 ,p_change_date => l_lot_change_date
434 ,p_maturity_date => l_lot_maturity_date
435 ,p_retest_date => l_lot_retest_date
436 ,p_age => l_lot_age
437 ,p_item_size => l_lot_item_size
438 ,p_color => l_lot_color
439 ,p_volume => l_lot_volume
440 ,p_volume_uom => l_lot_volume_uom
441 ,p_place_of_origin => l_lot_place_of_origin
442 ,p_best_by_date => l_lot_best_by_date
443 ,p_length => l_lot_length
444 ,p_length_uom => l_lot_length_uom
445 ,p_recycled_content => l_lot_recycled_content
446 ,p_thickness => l_lot_thickness
447 ,p_thickness_uom => l_lot_thickness_uom
448 ,p_width => l_lot_width
449 ,p_width_uom => l_lot_width_uom
450 ,p_curl_wrinkle_fold => l_lot_curl_wrinkle_fold
451 ,p_vendor_id => l_lot_vendor_id
452 ,p_territory_code => l_lot_territory_code
453 ,p_lot_attribute_category => l_lot_lot_attribute_category
454 ,p_c_attribute1 => l_lot_c_attribute1
455 ,p_c_attribute2 => l_lot_c_attribute2
456 ,p_c_attribute3 => l_lot_c_attribute3
457 ,p_c_attribute4 => l_lot_c_attribute4
458 ,p_c_attribute5 => l_lot_c_attribute5
459 ,p_c_attribute6 => l_lot_c_attribute6
460 ,p_c_attribute7 => l_lot_c_attribute7
461 ,p_c_attribute8 => l_lot_c_attribute8
462 ,p_c_attribute9 => l_lot_c_attribute9
463 ,p_c_attribute10 => l_lot_c_attribute10
464 ,p_c_attribute11 => l_lot_c_attribute11
465 ,p_c_attribute12 => l_lot_c_attribute12
466 ,p_c_attribute13 => l_lot_c_attribute13
467 ,p_c_attribute14 => l_lot_c_attribute14
468 ,p_c_attribute15 => l_lot_c_attribute15
469 ,p_c_attribute16 => l_lot_c_attribute16
470 ,p_c_attribute17 => l_lot_c_attribute17
471 ,p_c_attribute18 => l_lot_c_attribute18
472 ,p_c_attribute19 => l_lot_c_attribute19
473 ,p_c_attribute20 => l_lot_c_attribute20
474 ,p_d_attribute1 => l_lot_d_attribute1
475 ,p_d_attribute2 => l_lot_d_attribute2
476 ,p_d_attribute3 => l_lot_d_attribute3
477 ,p_d_attribute4 => l_lot_d_attribute4
478 ,p_d_attribute5 => l_lot_d_attribute5
479 ,p_d_attribute6 => l_lot_d_attribute6
480 ,p_d_attribute7 => l_lot_d_attribute7
481 ,p_d_attribute8 => l_lot_d_attribute8
482 ,p_d_attribute9 => l_lot_d_attribute9
483 ,p_d_attribute10 => l_lot_d_attribute10
484 ,p_n_attribute1 => l_lot_n_attribute1
485 ,p_n_attribute2 => l_lot_n_attribute2
486 ,p_n_attribute3 => l_lot_n_attribute3
487 ,p_n_attribute4 => l_lot_n_attribute4
488 ,p_n_attribute5 => l_lot_n_attribute5
489 ,p_n_attribute6 => l_lot_n_attribute6
490 ,p_n_attribute7 => l_lot_n_attribute7
491 ,p_n_attribute8 => l_lot_n_attribute8
492 ,p_n_attribute9 => l_lot_n_attribute9
493 ,p_n_attribute10 => l_lot_n_attribute10
494 ,p_attribute_category => l_lot_attribute_category
495 ,p_attribute1 => l_lot_attribute1
496 ,p_attribute2 => l_lot_attribute2
497 ,p_attribute3 => l_lot_attribute3
498 ,p_attribute4 => l_lot_attribute4
499 ,p_attribute5 => l_lot_attribute5
500 ,p_attribute6 => l_lot_attribute6
501 ,p_attribute7 => l_lot_attribute7
502 ,p_attribute8 => l_lot_attribute8
503 ,p_attribute9 => l_lot_attribute9
504 ,p_attribute10 => l_lot_attribute10
505 ,p_attribute11 => l_lot_attribute11
506 ,p_attribute12 => l_lot_attribute12
507 ,p_attribute13 => l_lot_attribute13
508 ,p_attribute14 => l_lot_attribute14
509 ,p_attribute15 => l_lot_attribute15
510 );
511 IF (x_return_status <> g_ret_sts_success) THEN
512 IF (l_debug = 1) THEN
513 print_debug('INSERT_LOT_SERIAL: ERROR - insert_mtli Fail');
514 END IF;
515 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
516 fnd_msg_pub.ADD;
517 RAISE fnd_api.g_exc_error;
518 END IF;
519
520 /* Check if there are msnt associated with this mtlt */
521 IF (l_serial_txn_tmp_id IS NOT NULL) THEN
522
523 IF (l_debug = 1) THEN
524 print_debug('INSERT_LOT_SERIAL: MSNI Exists');
525 END IF;
526
527 FOR l_msnt_rec IN msnt_recs(l_serial_txn_tmp_id) LOOP
528 inv_rcv_integration_apis.insert_msni
529 (p_api_version => 1.0
530 ,x_return_status => x_return_status
531 ,x_msg_count => x_msg_count
532 ,x_msg_data => x_msg_data
533 ,p_att_exist => 'N'
534 ,p_transaction_interface_id => l_new_ser_txn_id
535 ,p_fm_serial_number => l_msnt_rec.fm_serial_number
536 ,p_to_serial_number => l_msnt_rec.to_serial_number
537 ,p_organization_id => p_organization_id
538 ,p_inventory_item_id => p_item_id -- from tmp mmtt
539 ,p_status_id => 0 --l_msnt_rec.status_id
540 ,p_product_transaction_id => l_intf_id
541 ,p_product_code => 'RCV'
542 ,p_vendor_serial_number => l_msnt_rec.vendor_serial_number
543 ,p_vendor_lot_number => l_msnt_rec.vendor_lot_number
544 ,p_parent_serial_number => l_msnt_rec.parent_serial_number
545 ,p_origination_date => l_msnt_rec.origination_date
546 ,p_territory_code => l_msnt_rec.territory_code
547 ,p_time_since_new => l_msnt_rec.time_since_new
548 ,p_cycles_since_new => l_msnt_rec.cycles_since_new
549 ,p_time_since_overhaul => l_msnt_rec.time_since_overhaul
550 ,p_cycles_since_overhaul => l_msnt_rec.cycles_since_overhaul
551 ,p_time_since_repair => l_msnt_rec.time_since_repair
552 ,p_cycles_since_repair => l_msnt_rec.cycles_since_repair
553 ,p_time_since_visit => l_msnt_rec.time_since_visit
554 ,p_cycles_since_visit => l_msnt_rec.cycles_since_visit
555 ,p_time_since_mark => l_msnt_rec.time_since_mark
556 ,p_cycles_since_mark => l_msnt_rec.cycles_since_mark
557 ,p_number_of_repairs => l_msnt_rec.number_of_repairs
558 ,p_serial_attribute_category => l_msnt_rec.serial_attribute_category
559 ,p_c_attribute1 => l_msnt_rec.c_attribute1
560 ,p_c_attribute2 => l_msnt_rec.c_attribute2
561 ,p_c_attribute3 => l_msnt_rec.c_attribute3
562 ,p_c_attribute4 => l_msnt_rec.c_attribute4
563 ,p_c_attribute5 => l_msnt_rec.c_attribute5
564 ,p_c_attribute6 => l_msnt_rec.c_attribute6
565 ,p_c_attribute7 => l_msnt_rec.c_attribute7
566 ,p_c_attribute8 => l_msnt_rec.c_attribute8
567 ,p_c_attribute9 => l_msnt_rec.c_attribute9
568 ,p_c_attribute10 => l_msnt_rec.c_attribute10
569 ,p_c_attribute11 => l_msnt_rec.c_attribute11
570 ,p_c_attribute12 => l_msnt_rec.c_attribute12
571 ,p_c_attribute13 => l_msnt_rec.c_attribute13
572 ,p_c_attribute14 => l_msnt_rec.c_attribute14
573 ,p_c_attribute15 => l_msnt_rec.c_attribute15
574 ,p_c_attribute16 => l_msnt_rec.c_attribute16
575 ,p_c_attribute17 => l_msnt_rec.c_attribute17
576 ,p_c_attribute18 => l_msnt_rec.c_attribute18
577 ,p_c_attribute19 => l_msnt_rec.c_attribute19
578 ,p_c_attribute20 => l_msnt_rec.c_attribute20
579 ,p_d_attribute1 => l_msnt_rec.d_attribute1
580 ,p_d_attribute2 => l_msnt_rec.d_attribute2
581 ,p_d_attribute3 => l_msnt_rec.d_attribute3
582 ,p_d_attribute4 => l_msnt_rec.d_attribute4
583 ,p_d_attribute5 => l_msnt_rec.d_attribute5
584 ,p_d_attribute6 => l_msnt_rec.d_attribute6
585 ,p_d_attribute7 => l_msnt_rec.d_attribute7
586 ,p_d_attribute8 => l_msnt_rec.d_attribute8
587 ,p_d_attribute9 => l_msnt_rec.d_attribute9
588 ,p_d_attribute10 => l_msnt_rec.d_attribute10
589 ,p_n_attribute1 => l_msnt_rec.n_attribute1
590 ,p_n_attribute2 => l_msnt_rec.n_attribute2
591 ,p_n_attribute3 => l_msnt_rec.n_attribute3
592 ,p_n_attribute4 => l_msnt_rec.n_attribute4
593 ,p_n_attribute5 => l_msnt_rec.n_attribute5
594 ,p_n_attribute6 => l_msnt_rec.n_attribute6
595 ,p_n_attribute7 => l_msnt_rec.n_attribute7
596 ,p_n_attribute8 => l_msnt_rec.n_attribute8
597 ,p_n_attribute9 => l_msnt_rec.n_attribute9
598 ,p_n_attribute10 => l_msnt_rec.n_attribute10
599 );
600 IF (x_return_status <> g_ret_sts_success) THEN
601 IF (l_debug = 1) THEN
602 print_debug('INSERT_LOT_SERIAL: ERROR- insert_msni Fail');
603 RAISE fnd_api.g_exc_error;
604 END IF;
605 END IF;
606 END LOOP;
607 END IF;
608 ELSE
609 -- Check if there are msnt associated with the mmtt
610 -- Either lot/serial or lot?
611 IF (l_debug = 1) THEN
612 print_debug('INSERT_LOT_SERIAL: No MTLT exists. Check MSNT');
613 END IF;
614
615 FOR l_msnt_rec IN msnt_recs(p_transaction_temp_id) LOOP
616
617 IF (l_intf_id IS NULL) THEN
618 /* Create a dummy RT ID */
619 SELECT rcv_transactions_interface_s.NEXTVAL
620 INTO l_intf_id
621 FROM dual;
622 END IF;
623
624 inv_rcv_integration_apis.insert_msni
625 (p_api_version => 1.0
626 ,x_return_status => x_return_status
627 ,x_msg_count => x_msg_count
628 ,x_msg_data => x_msg_data
629 ,p_att_exist => 'N'
630 ,p_transaction_interface_id => l_msnt_rec.transaction_temp_id
631 ,p_fm_serial_number => l_msnt_rec.fm_serial_number
632 ,p_to_serial_number => l_msnt_rec.to_serial_number
633 ,p_organization_id => p_organization_id
634 ,p_inventory_item_id => p_item_id -- from tmp mmtt
635 ,p_status_id => 0 --l_msnt_rec.status_id
636 ,p_product_transaction_id => l_intf_id
637 ,p_product_code => 'RCV'
638 ,p_vendor_serial_number => l_msnt_rec.vendor_serial_number
639 ,p_vendor_lot_number => l_msnt_rec.vendor_lot_number
640 ,p_parent_serial_number => l_msnt_rec.parent_serial_number
641 ,p_origination_date => l_msnt_rec.origination_date
642 ,p_territory_code => l_msnt_rec.territory_code
643 ,p_time_since_new => l_msnt_rec.time_since_new
644 ,p_cycles_since_new => l_msnt_rec.cycles_since_new
645 ,p_time_since_overhaul => l_msnt_rec.time_since_overhaul
646 ,p_cycles_since_overhaul => l_msnt_rec.cycles_since_overhaul
647 ,p_time_since_repair => l_msnt_rec.time_since_repair
648 ,p_cycles_since_repair => l_msnt_rec.cycles_since_repair
649 ,p_time_since_visit => l_msnt_rec.time_since_visit
650 ,p_cycles_since_visit => l_msnt_rec.cycles_since_visit
651 ,p_time_since_mark => l_msnt_rec.time_since_mark
652 ,p_cycles_since_mark => l_msnt_rec.cycles_since_mark
653 ,p_number_of_repairs => l_msnt_rec.number_of_repairs
654 ,p_serial_attribute_category => l_msnt_rec.serial_attribute_category
655 ,p_c_attribute1 => l_msnt_rec.c_attribute1
656 ,p_c_attribute2 => l_msnt_rec.c_attribute2
657 ,p_c_attribute3 => l_msnt_rec.c_attribute3
658 ,p_c_attribute4 => l_msnt_rec.c_attribute4
659 ,p_c_attribute5 => l_msnt_rec.c_attribute5
660 ,p_c_attribute6 => l_msnt_rec.c_attribute6
661 ,p_c_attribute7 => l_msnt_rec.c_attribute7
662 ,p_c_attribute8 => l_msnt_rec.c_attribute8
663 ,p_c_attribute9 => l_msnt_rec.c_attribute9
664 ,p_c_attribute10 => l_msnt_rec.c_attribute10
665 ,p_c_attribute11 => l_msnt_rec.c_attribute11
666 ,p_c_attribute12 => l_msnt_rec.c_attribute12
667 ,p_c_attribute13 => l_msnt_rec.c_attribute13
668 ,p_c_attribute14 => l_msnt_rec.c_attribute14
669 ,p_c_attribute15 => l_msnt_rec.c_attribute15
670 ,p_c_attribute16 => l_msnt_rec.c_attribute16
671 ,p_c_attribute17 => l_msnt_rec.c_attribute17
672 ,p_c_attribute18 => l_msnt_rec.c_attribute18
673 ,p_c_attribute19 => l_msnt_rec.c_attribute19
674 ,p_c_attribute20 => l_msnt_rec.c_attribute20
675 ,p_d_attribute1 => l_msnt_rec.d_attribute1
676 ,p_d_attribute2 => l_msnt_rec.d_attribute2
677 ,p_d_attribute3 => l_msnt_rec.d_attribute3
678 ,p_d_attribute4 => l_msnt_rec.d_attribute4
679 ,p_d_attribute5 => l_msnt_rec.d_attribute5
680 ,p_d_attribute6 => l_msnt_rec.d_attribute6
681 ,p_d_attribute7 => l_msnt_rec.d_attribute7
682 ,p_d_attribute8 => l_msnt_rec.d_attribute8
683 ,p_d_attribute9 => l_msnt_rec.d_attribute9
684 ,p_d_attribute10 => l_msnt_rec.d_attribute10
685 ,p_n_attribute1 => l_msnt_rec.n_attribute1
686 ,p_n_attribute2 => l_msnt_rec.n_attribute2
687 ,p_n_attribute3 => l_msnt_rec.n_attribute3
688 ,p_n_attribute4 => l_msnt_rec.n_attribute4
689 ,p_n_attribute5 => l_msnt_rec.n_attribute5
690 ,p_n_attribute6 => l_msnt_rec.n_attribute6
691 ,p_n_attribute7 => l_msnt_rec.n_attribute7
692 ,p_n_attribute8 => l_msnt_rec.n_attribute8
693 ,p_n_attribute9 => l_msnt_rec.n_attribute9
694 ,p_n_attribute10 => l_msnt_rec.n_attribute10
695 );
696
697 IF (x_return_status <> g_ret_sts_success) THEN
698 IF (l_debug = 1) THEN
699 print_debug('INSERT_LOT_SERIAL: ERROR - insert_msni Fail');
700 END IF;
701 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
702 fnd_msg_pub.ADD;
703 RAISE fnd_api.g_exc_error;
704 END IF;
705
706 END LOOP;
707
708 END IF;
709 RETURN l_intf_id;
710 EXCEPTION
711 WHEN fnd_api.g_exc_error THEN
712 x_return_status := fnd_api.g_ret_sts_error;
713 fnd_msg_pub.count_and_get
714 ( p_count => x_msg_count
715 ,p_data => x_msg_data );
716 IF (l_debug = 1) THEN
717 print_debug('INSERT_LOT_SERIAL: Exception occured');
718 END IF;
719 WHEN OTHERS THEN
720 x_return_status:=g_ret_sts_unexp_err;
721 fnd_msg_pub.count_and_get
722 ( p_count => x_msg_count
723 ,p_data => x_msg_data );
724 IF (l_debug = 1) THEN
725 print_debug('INSERT_LOT_SERIAL: Exception occured');
726 END IF;
727 END insert_lot_serial;
728
729 PROCEDURE abort_mmtts(p_move_order_line_id IN NUMBER DEFAULT NULL
730 ,p_lpn_id IN NUMBER DEFAULT NULL
731 ,p_organization_id IN NUMBER
732 ,x_return_status OUT nocopy VARCHAR2
733 ,x_msg_count OUT nocopy NUMBER
734 ,x_msg_data OUT nocopy VARCHAR2)
735 IS
736 l_txn_tmp_id_tb number_tb_type;
737 l_error_code VARCHAR2(1);
738 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
739
740 BEGIN
741
742 IF (l_debug = 1) THEN
743 print_debug('ABORT_MMTTS: Entering...');
744 END IF;
745
746 x_return_status := g_ret_sts_success;
747
748 IF (p_move_order_line_id IS NOT NULL) THEN
749
750 IF (l_debug = 1) THEN
751 print_debug('ABORT_MMTTS: Move order line ID passed');
752 END IF;
753
754 BEGIN
755 -- Bug 5231114: Added the condition on transaction_source_type_id and
756 -- transaction_action_id for the following combinations:13/12 and 4/27
757
758 SELECT
759 transaction_temp_id
760 BULK COLLECT INTO
761 l_txn_tmp_id_tb
762 FROM
763 mtl_material_transactions_temp
764 WHERE
765 ( move_order_line_id = p_move_order_line_id AND
766 ( ( transaction_source_type_id = 1 AND
767 transaction_action_id = 27) OR
768 ( transaction_source_type_id = 7 AND
769 transaction_action_id = 12) OR
770 ( transaction_source_type_id = 12 AND
771 transaction_action_id = 27) OR
772 ( transaction_source_type_id = 13 AND
773 transaction_action_id = 12) OR
774 ( transaction_source_type_id = 4 AND
775 transaction_action_id = 27)));
776 EXCEPTION
777 WHEN OTHERS THEN
778 IF (l_debug = 1) THEN
779 print_debug('ABORT_MMTTS: Unexpected Exception Raised in Bulk Select');
780 END IF;
781 RAISE fnd_api.g_exc_error;
782 END;
783 ELSIF (p_lpn_id IS NOT NULL) THEN
784
785 IF (l_debug = 1) THEN
786 print_debug('ABORT_MMTTS: Lpn_id passed');
787 END IF;
788
789 BEGIN
790 --Bug# 3281512
791 --This query is modified for performance fix
792 -- Bug 5231114: Added the condition on transaction_source_type_id and
793 -- transaction_action_id for the following combinations:13/12 and 4/27
794 SELECT
795 mmtt.transaction_temp_id
796 BULK COLLECT INTO
797 l_txn_tmp_id_tb
798 FROM
799 mtl_material_transactions_temp mmtt,
800 mtl_txn_request_lines mtrl
801 WHERE
802 ( ( mmtt.transaction_source_type_id = 1 AND
803 mmtt.transaction_action_id = 27) OR
804 ( mmtt.transaction_source_type_id = 7 AND
805 mmtt.transaction_action_id = 12) OR
806 ( mmtt.transaction_source_type_id = 12 AND
807 mmtt.transaction_action_id = 27) OR
808 ( mmtt.transaction_source_type_id = 13 AND
809 mmtt.transaction_action_id = 12) OR
810 ( mmtt.transaction_source_type_id = 4 AND
811 mmtt.transaction_action_id = 27) ) AND
812 mmtt.move_order_line_id = mtrl.line_id AND
813 mmtt.organization_id = p_organization_id AND
814 mtrl.organization_id = p_organization_id AND
815 mtrl.lpn_id IN (SELECT wlpn.lpn_id
816 FROM wms_license_plate_numbers wlpn
817 START WITH wlpn.lpn_id = p_lpn_id
818 CONNECT BY wlpn.parent_lpn_id = PRIOR wlpn.lpn_id ) ;
819 EXCEPTION
820 WHEN OTHERS THEN
821 IF (l_debug = 1 ) THEN
822 print_debug('ABORT_MMTTS: Unexpected Exception Raised in Bulk Select');
823 END IF;
824 RAISE fnd_api.g_exc_error;
825 END;
826 END IF;
827
828 print_debug('ABORT_MMTTS: count is:'|| l_txn_tmp_id_tb.COUNT);
829
830 -- Abort all mmtts retrieved
831 FOR l_index IN 1 .. l_txn_tmp_id_tb.COUNT LOOP
832 -- Abort or cancel?
833
834 IF (l_debug = 1) THEN
835 print_debug('ABORT_MMTTS: Calling cancel_op_plan_instance on MMTT:'
836 || l_txn_tmp_id_tb(l_index));
837 END IF;
838
839 wms_atf_runtime_pub_apis.cancel_operation_plan
840 ( p_source_task_id => l_txn_tmp_id_tb(l_index)
841 ,p_activity_type_id => 1 -- INBOUND
842 ,x_return_status => x_return_status
843 ,x_msg_data => x_msg_data
844 ,x_msg_count => x_msg_count
845 ,x_error_code => l_error_code
846 );
847 IF (x_return_status <> g_ret_sts_success) THEN
848 IF (l_debug = 1) THEN
849 print_debug('ABORT_MMTTS: ERROR - cancel_op_plan_instance fail with error code ' || l_error_code);
850 END IF;
851 RAISE fnd_api.g_exc_error;
852 END IF;
853
854 END LOOP;
855
856 IF (l_debug = 1) THEN
857 print_debug('ABORT_MMTTS: Quitting...');
858 END IF;
859
860 EXCEPTION
861 WHEN fnd_api.g_exc_error THEN
862 x_return_status := fnd_api.g_ret_sts_error;
863 fnd_msg_pub.count_and_get
864 ( p_count => x_msg_count
865 ,p_data => x_msg_data );
866 IF (l_debug = 1) THEN
867 print_debug('ABORT_MMTTS: Exception occured');
868 END IF;
869
870 WHEN OTHERS THEN
871 x_return_status:=g_ret_sts_unexp_err;
872 fnd_msg_pub.count_and_get
873 ( p_count => x_msg_count
874 ,p_data => x_msg_data );
875 IF (l_debug = 1) THEN
876 print_debug('ABORT_MMTTS: Exception occured');
877 END IF;
878 END abort_mmtts;
879
880 PROCEDURE pack_unpack_split
881 (p_transaction_temp_id IN NUMBER DEFAULT NULL
882 ,p_header_id IN NUMBER DEFAULT NULL
883 ,x_return_status OUT NOCOPY VARCHAR2
884 ,x_msg_count OUT NOCOPY NUMBER
885 ,x_msg_data OUT NOCOPY VARCHAR2
886 )
887 IS
888 l_mo_lines_tb inv_rcv_integration_apis.mo_in_tb_tp;
889 BEGIN
890 pack_unpack_split
891 (p_transaction_temp_id => p_transaction_temp_id
892 ,p_header_id => p_header_id
893 ,x_return_status => x_return_status
894 ,x_msg_count => x_msg_count
895 ,x_msg_data => x_msg_data
896 ,x_mo_lines_tb => l_mo_lines_tb
897 );
898 END pack_unpack_split;
899
900 PROCEDURE pack_unpack_split
901 (p_transaction_temp_id IN NUMBER DEFAULT NULL
902 ,p_header_id IN NUMBER DEFAULT NULL
903 ,p_call_rcv_tm IN VARCHAR2 DEFAULT fnd_api.g_true
904 ,p_txn_mode_code IN VARCHAR2 DEFAULT g_default_txn_mode
905 ,x_return_status OUT NOCOPY VARCHAR2
906 ,x_msg_count OUT NOCOPY NUMBER
907 ,x_msg_data OUT NOCOPY VARCHAR2
908 ,x_mo_lines_tb OUT nocopy inv_rcv_integration_apis.mo_in_tb_tp
909 )
910 IS
911 CURSOR mol_cur(l_line_id NUMBER) IS
912 SELECT
913 line_id
914 ,txn_source_id
915 ,reference_id
916 ,reference
917 ,reference_type_code
918 ,revision
919 ,lot_number
920 ,inspection_status
921 FROM mtl_txn_request_lines
922 WHERE line_id = l_line_id;
923
924 --BUG 3634192: Break up the tmp_mmtt_cur cursor into 2. This
925 --is made for performance reasons
926 CURSOR tmp_mmtt_cur_by_hdr_id (l_header_id NUMBER) IS
927 SELECT
928 mmtt.transaction_temp_id transaction_temp_id,
929 mmtt.organization_id organization_id,
930 mmtt.transfer_organization transfer_organization,
931 mmtt.inventory_item_id inventory_item_id,
932 mmtt.lpn_id lpn_id,
933 mmtt.content_lpn_id content_lpn_id,
934 mmtt.transfer_lpn_id transfer_lpn_id,
935 mmtt.subinventory_code subinventory_code,
936 mmtt.transfer_subinventory transfer_subinventory,
937 mmtt.locator_id locator_id,
938 mmtt.transfer_to_location transfer_to_location,
939 mmtt.move_order_line_id move_order_line_id,
940 mmtt.transaction_quantity transaction_quantity,
941 mmtt.primary_quantity primary_quantity,
942 mmtt.transaction_uom transaction_uom,
943 decode(mmtt.inventory_item_id
944 ,-1
945 ,Decode(mmtt.lpn_id
946 ,NULL
947 ,Decode(mmtt.content_lpn_id
948 ,NULL
949 ,'UNKNOWN'
950 ,Decode(mmtt.transfer_lpn_id
951 ,NULL
952 ,'LPN_MOVE'
953 ,'LPN_PACK'))
954 ,Decode(mmtt.content_lpn_id
955 ,NULL
956 ,'UNKNOWN'
957 ,Decode(mmtt.transfer_lpn_id
958 ,NULL
959 ,'LPN_UNPACK'
960 ,'LPN_SPLIT')))
961 ,Decode(mmtt.lpn_id
962 ,NULL
963 ,Decode(mmtt.content_lpn_id
964 ,NULL
965 ,Decode(mmtt.transfer_lpn_id
966 ,NULL
967 ,'UNKNOWN'
968 ,'ITEM_PACK')
969 ,'UNKNOWN')
970 ,Decode(mmtt.content_lpn_id
971 ,NULL
972 ,Decode(mmtt.transfer_lpn_id
973 ,NULL
974 ,'ITEM_UNPACK'
975 ,'ITEM_SPLIT')
976 ,'UNKNOWN'))) txn_type,
977 Decode(mmtt.subinventory_code
978 ,mmtt.transfer_subinventory
979 ,Decode(Nvl(mmtt.locator_id, -1)
980 ,Nvl(mmtt.transfer_to_location, -1)
981 ,0
982 ,1)
983 ,1) sub_loc_changed,
984 msi.lot_control_code lot_control_code,
985 msi.serial_number_control_code serial_control_code,
986 msi.primary_uom_code primary_uom_code,
987 -- OPM Convergance
988 mmtt.secondary_uom_code secondary_uom_code,
989 mmtt.secondary_transaction_quantity secondary_transaction_quantity
990 -- OPM Convergance
991 FROM
992 mtl_material_transactions_temp mmtt,
993 mtl_system_items msi
994 WHERE
995 l_header_id = mmtt.transaction_header_id AND
996 mmtt.inventory_item_id = msi.inventory_item_id (+) AND
997 mmtt.organization_id = msi.organization_id (+);
998
999 CURSOR tmp_mmtt_cur_by_txn_id(l_txn_id NUMBER) IS
1000 SELECT
1001 mmtt.transaction_temp_id transaction_temp_id,
1002 mmtt.organization_id organization_id,
1003 mmtt.transfer_organization transfer_organization,
1004 mmtt.inventory_item_id inventory_item_id,
1005 mmtt.lpn_id lpn_id,
1006 mmtt.content_lpn_id content_lpn_id,
1007 mmtt.transfer_lpn_id transfer_lpn_id,
1008 mmtt.subinventory_code subinventory_code,
1009 mmtt.transfer_subinventory transfer_subinventory,
1010 mmtt.locator_id locator_id,
1011 mmtt.transfer_to_location transfer_to_location,
1012 mmtt.move_order_line_id move_order_line_id,
1013 mmtt.transaction_quantity transaction_quantity,
1014 mmtt.primary_quantity primary_quantity,
1015 mmtt.transaction_uom transaction_uom,
1016 decode(mmtt.inventory_item_id
1017 ,-1
1018 ,Decode(mmtt.lpn_id
1019 ,NULL
1020 ,Decode(mmtt.content_lpn_id
1021 ,NULL
1022 ,'UNKNOWN'
1023 ,Decode(mmtt.transfer_lpn_id
1024 ,NULL
1025 ,'LPN_MOVE'
1026 ,'LPN_PACK'))
1027 ,Decode(mmtt.content_lpn_id
1028 ,NULL
1029 ,'UNKNOWN'
1030 ,Decode(mmtt.transfer_lpn_id
1031 ,NULL
1032 ,'LPN_UNPACK'
1033 ,'LPN_SPLIT')))
1034 ,Decode(mmtt.lpn_id
1035 ,NULL
1036 ,Decode(mmtt.content_lpn_id
1037 ,NULL
1038 ,Decode(mmtt.transfer_lpn_id
1039 ,NULL
1040 ,'UNKNOWN'
1041 ,'ITEM_PACK')
1042 ,'UNKNOWN')
1043 ,Decode(mmtt.content_lpn_id
1044 ,NULL
1045 ,Decode(mmtt.transfer_lpn_id
1046 ,NULL
1047 ,'ITEM_UNPACK'
1048 ,'ITEM_SPLIT')
1049 ,'UNKNOWN'))) txn_type,
1050 Decode(mmtt.subinventory_code
1051 ,mmtt.transfer_subinventory
1052 ,Decode(Nvl(mmtt.locator_id, -1)
1053 ,Nvl(mmtt.transfer_to_location, -1)
1054 ,0
1055 ,1)
1056 ,1) sub_loc_changed,
1057 msi.lot_control_code lot_control_code,
1058 msi.serial_number_control_code serial_control_code,
1059 msi.primary_uom_code primary_uom_code,
1060 -- OPM Convergance
1061 mmtt.secondary_uom_code secondary_uom_code,
1062 mmtt.secondary_transaction_quantity secondary_transaction_quantity
1063 -- OPM Convergance
1064 FROM
1065 mtl_material_transactions_temp mmtt,
1066 mtl_system_items msi
1067 WHERE
1068 l_txn_id = mmtt.transaction_temp_id AND
1069 mmtt.inventory_item_id = msi.inventory_item_id (+) AND
1070 mmtt.organization_id = msi.organization_id (+);
1071
1072 l_tmp_mmtt_rec tmp_mmtt_cur_by_hdr_id%ROWTYPE;
1073
1074 l_group_id NUMBER := NULL;
1075 l_mol_rec mol_cur%ROWTYPE;
1076 l_old_intf_id NUMBER := NULL;
1077 l_new_intf_id NUMBER := NULL;
1078 l_mo_splt_tb inv_rcv_integration_apis.mo_in_tb_tp;
1079 l_out_mo_splt_tb inv_rcv_integration_apis.mo_in_tb_tp;
1080 l_rti_tb inv_rcv_integration_apis.child_rec_tb_tp;
1081 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1082 l_progress VARCHAR2(10) := '0.0';
1083 l_call_rm BOOLEAN := FALSE;
1084 l_txn_mode_code VARCHAR2(25);
1085 l_first_time NUMBER := 0;
1086 l_xfer_lpn_loaded NUMBER := 0;
1087 l_uom_to_insert VARCHAR2(3);
1088 l_qty_to_insert NUMBER;
1089
1090 TYPE number_tb_tp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1091 l_mmtt_ids number_tb_tp;
1092 l_mmtts_count NUMBER := 0;
1093
1094 l_receipt_count NUMBER := 0; -- Added for bug 14269755
1095 l_shipment_header_id NUMBER;
1096
1097 -- l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1098 -- x_msg_count NUMBER;
1099 -- x_msg_data VARCHAR2(2400);
1100
1101 BEGIN
1102
1103 SAVEPOINT pack_unpack_split_pub;
1104
1105 IF (l_debug = 1) THEN
1106 print_debug('PACK_UNPACK_SPLIT: Entering...');
1107 print_debug(' p_transaction_temp_id => '||p_transaction_temp_id);
1108 print_debug(' p_header_id => '||p_header_id);
1109 print_debug(' p_call_rcv_tm => '||p_call_rcv_tm);
1110 END IF;
1111
1112 x_return_status := g_ret_sts_success;
1113
1114 IF (p_transaction_temp_id IS NOT NULL) THEN
1115 OPEN tmp_mmtt_cur_by_txn_id(p_transaction_temp_id);
1116 ELSIF (p_header_id IS NOT NULL) THEN
1117 OPEN tmp_mmtt_cur_by_hdr_id(p_header_id);
1118 ELSE
1119 l_progress := '0.0.1';
1120 print_debug('PACK_UNPACK_SPLIT: Invalid parameter passed to API!');
1121 RAISE fnd_api.g_exc_error;
1122 END IF;
1123
1124 LOOP
1125 IF (p_transaction_temp_id IS NOT NULL) THEN
1126 FETCH tmp_mmtt_cur_by_txn_id INTO l_tmp_mmtt_rec;
1127 EXIT WHEN tmp_mmtt_cur_by_txn_id%notfound;
1128 ELSE
1129 FETCH tmp_mmtt_cur_by_hdr_id INTO l_tmp_mmtt_rec;
1130 EXIT WHEN tmp_mmtt_cur_by_hdr_id%notfound;
1131 END IF;
1132
1133 l_progress := '0.1';
1134 IF (l_debug = 1) THEN
1135 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Temp MMTT FETCHED');
1136 print_debug(' l_tmp_mmtt_rec.transaction_temp_id: '||l_tmp_mmtt_rec.transaction_temp_id);
1137 print_debug(' l_tmp_mmtt_rec.organization_id : '||l_tmp_mmtt_rec.organization_id);
1138 print_debug(' l_tmp_mmtt_rec.transfer_organization_id:: '||l_tmp_mmtt_rec.transfer_organization);
1139 print_debug(' l_tmp_mmtt_rec.inventory_item_id : '||l_tmp_mmtt_rec.inventory_item_id);
1140 print_debug(' l_tmp_mmtt_rec.lpn_id : '||l_tmp_mmtt_rec.lpn_id);
1141 print_debug(' l_tmp_mmtt_rec.content_lpn_id : '||l_tmp_mmtt_rec.content_lpn_id);
1142 print_debug(' l_tmp_mmtt_rec.transfer_lpn_id : '||l_tmp_mmtt_rec.transfer_lpn_id);
1143 print_debug(' l_tmp_mmtt_rec.subinventory_code : '||l_tmp_mmtt_rec.subinventory_code);
1144 print_debug(' l_tmp_mmtt_rec.transfer_subinventory : '||l_tmp_mmtt_rec.transfer_subinventory);
1145 print_debug(' l_tmp_mmtt_rec.locator_id : '||l_tmp_mmtt_rec.locator_id);
1146 print_debug(' l_tmp_mmtt_rec.transfer_to_loc_id : '||l_tmp_mmtt_rec.transfer_to_location);
1147 print_debug(' l_tmp_mmtt_rec.move_order_line_id : '||l_tmp_mmtt_rec.move_order_line_id);
1148 print_debug(' l_tmp_mmtt_rec.transaction_quantity: '||l_tmp_mmtt_rec.transaction_quantity);
1149 print_debug(' l_tmp_mmtt_rec.primary_quantity : '||l_tmp_mmtt_rec.primary_quantity);
1150 print_debug(' l_tmp_mmtt_rec.txn_type : '||l_tmp_mmtt_rec.txn_type);
1151 print_debug(' l_tmp_mmtt_rec.sub_loc_changed : '||l_tmp_mmtt_rec.sub_loc_changed);
1152 -- OPM Convergance
1153 print_debug(' l_tmp_mmtt_rec.secondary_transaction_quantity: '||l_tmp_mmtt_rec.secondary_transaction_quantity);
1154 print_debug(' l_tmp_mmtt_rec.secondary_uom_code: '||l_tmp_mmtt_rec.secondary_uom_code);
1155 -- OPM Convergance
1156 END IF;
1157
1158 -- Start bug 14269755
1159
1160 BEGIN
1161
1162 SELECT Count(DISTINCT(receipt_num)) INTO l_receipt_count
1163 FROM wms_packing_material_gtemp wpmg
1164 WHERE move_order_line_id = l_tmp_mmtt_rec.move_order_line_id
1165 AND organization_id = l_tmp_mmtt_rec.organization_id;
1166
1167 IF(l_receipt_count > 1) THEN
1168 l_shipment_header_id := NULL;
1169 ELSE
1170 SELECT rsl.shipment_header_id INTO l_shipment_header_id
1171 FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh,
1172 wms_packing_material_gtemp wpmg
1173 WHERE wpmg.move_order_line_id = l_tmp_mmtt_rec.move_order_line_id
1174 AND wpmg.organization_id = l_tmp_mmtt_rec.organization_id
1175 AND rsh.receipt_num = wpmg.receipt_num
1176 AND rsl.shipment_header_id = rsh.shipment_header_id
1177 AND ((rsl.shipment_line_id = wpmg.reference_id AND wpmg.reference='SHIPMENT_LINE_ID')
1178 OR
1179 (rsl.po_line_location_id = wpmg.reference_id AND wpmg.reference='PO_LINE_LOCATION_ID')
1180 OR
1181 (rsl.oe_order_line_id = wpmg.reference_id AND wpmg.reference='ORDER_LINE_ID')
1182 );
1183 END IF;
1184
1185 EXCEPTION
1186 WHEN No_Data_Found THEN
1187
1188 IF (l_debug = 1) THEN
1189 print_debug('Exception occurred No Data Found : ');
1190 END IF;
1191 END;
1192
1193 -- End bug 14269755
1194
1195 -- Initialize loop variables
1196 l_mo_splt_tb.DELETE;
1197
1198 -- Set up array for bulk delete later after the loop
1199 l_mmtts_count := l_mmtts_count + 1;
1200 l_mmtt_ids(l_mmtts_count) := l_tmp_mmtt_rec.transaction_temp_id;
1201
1202 l_progress := '0.2';
1203
1204 IF (l_tmp_mmtt_rec.txn_type = 'LPN_MOVE') THEN
1205 l_progress := '1.0';
1206 IF (l_debug = 1) THEN
1207 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') LPN MOVE');
1208 END IF;
1209
1210 IF (l_tmp_mmtt_rec.sub_loc_changed = 1) THEN
1211 l_progress := '1.1.0';
1212 IF (l_debug = 1) THEN
1213 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
1214 END IF;
1215
1216 abort_mmtts(p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1217 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1218 ,x_return_status => x_return_status
1219 ,x_msg_count => x_msg_count
1220 ,x_msg_data => x_msg_data
1221 );
1222 IF (x_return_status <> g_ret_sts_success) THEN
1223 IF (l_debug = 1) THEN
1224 print_debug('PACK_UNPACK_SPLIT: abort_mmtts failed');
1225 END IF;
1226 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1227 fnd_msg_pub.ADD;
1228 RAISE fnd_api.g_exc_error;
1229 END IF;
1230 l_progress := '1.1.1';
1231
1232 IF (l_debug = 1) THEN
1233 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') MMTTs successfully aborted');
1234 END IF;
1235
1236 IF (l_first_time <> 1) THEN
1237 l_first_time := 1;
1238 END IF;
1239
1240 l_group_id :=
1241 wms_putaway_utils.insert_rti
1242 (p_from_org => l_tmp_mmtt_rec.organization_id
1243 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1244 ,p_to_org => l_tmp_mmtt_rec.organization_id
1245 ,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
1246 ,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
1247 ,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1248 ,p_first_time => l_first_time
1249 ,p_mobile_txn => 'Y'
1250 ,p_txn_mode_code => p_txn_mode_code
1251 ,x_return_status => x_return_status
1252 ,x_msg_count => x_msg_count
1253 ,x_msg_data => x_msg_data
1254 );
1255 IF (x_return_status <> g_ret_sts_success) THEN
1256 IF (l_debug = 1) THEN
1257 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_rti Fail');
1258 END IF;
1259 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
1260 fnd_msg_pub.ADD;
1261 RAISE fnd_api.g_exc_error;
1262 END IF;
1263
1264 l_progress := '1.1.2';
1265
1266 IF (l_debug = 1) THEN
1267 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') RTI inserted');
1268 print_debug(' Calling insert_wlpni(');
1269 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1270 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1271 print_debug(' p_license_plate_number => ');
1272 print_debug(' p_lpn_group_id => '|| l_group_id ||')');
1273 END IF;
1274
1275 inv_rcv_integration_apis.insert_wlpni
1276 (p_api_version => 1.0
1277 ,x_return_status => x_return_status
1278 ,x_msg_count => x_msg_count
1279 ,x_msg_data => x_msg_data
1280 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1281 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1282 ,p_license_plate_number => NULL
1283 ,p_lpn_group_id => l_group_id
1284 ,p_parent_lpn_id => NULL
1285 );
1286 IF (x_return_status <> g_ret_sts_success) THEN
1287 IF (l_debug = 1) THEN
1288 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_wlpni Fail');
1289 END IF;
1290 RAISE fnd_api.g_exc_error;
1291 END IF;
1292
1293 l_progress := '1.1.3';
1294 IF (l_debug = 1) THEN
1295 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNI successfully inserted');
1296 END IF;
1297
1298 -- Signal call to TM
1299 l_call_rm := TRUE;
1300 ELSE
1301 l_progress := '1.2.0';
1302 IF (l_debug = 1) THEN
1303 print_debug('PACK_UNPACK_SPLIT: LPN Move and No location changed.' ||
1304 ' No action required, return success');
1305 END IF;
1306 RETURN;
1307 END IF;
1308 ELSIF (l_tmp_mmtt_rec.txn_type = 'LPN_PACK') THEN
1309 l_progress := '2.0';
1310 IF (l_debug = 1) THEN
1311 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') LPN PACK');
1312 END IF;
1313
1314 IF (l_tmp_mmtt_rec.sub_loc_changed = 1) THEN
1315 l_progress := '2.1.0';
1316 IF (l_debug = 1) THEN
1317 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
1318 END IF;
1319
1320 abort_mmtts
1321 (p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1322 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1323 ,x_return_status => x_return_status
1324 ,x_msg_count => x_msg_count
1325 ,x_msg_data => x_msg_data
1326 );
1327 IF (x_return_status <> g_ret_sts_success) THEN
1328 IF (l_debug = 1) THEN
1329 print_debug('PACK_UNPACK_SPLIT: ERROR - Abort MMTTs Fail');
1330 END IF;
1331 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1332 fnd_msg_pub.ADD;
1333 RAISE fnd_api.g_exc_error;
1334 END IF;
1335
1336 l_progress := '2.1.1';
1337 IF (l_debug = 1) THEN
1338 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') MMTTs aborted sucessfully');
1339 END IF;
1340
1341 IF (l_first_time <> 1) THEN
1342 l_first_time := 1;
1343 END IF;
1344 l_group_id :=
1345 wms_putaway_utils.insert_rti
1346 (p_from_org => l_tmp_mmtt_rec.organization_id
1347 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1348 ,p_to_org => l_tmp_mmtt_rec.organization_id
1349 ,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
1350 ,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
1351 ,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1352 ,p_first_time => l_first_time
1353 ,p_mobile_txn => 'Y'
1354 ,p_txn_mode_code => p_txn_mode_code
1355 ,x_return_status => x_return_status
1356 ,x_msg_count => x_msg_count
1357 ,x_msg_data => x_msg_data
1358 );
1359 IF (x_return_status <> g_ret_sts_success) THEN
1360 IF (l_debug = 1) THEN
1361 print_debug('ERROR: insert_rti Fail');
1362 END IF;
1363 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
1364 fnd_msg_pub.ADD;
1365 RAISE fnd_api.g_exc_error;
1366 END IF;
1367
1368 l_progress := '2.1.2';
1369
1370 IF (l_debug = 1) THEN
1371 print_debug('PACK_UNPACK_SPLIT: Calling insert_wlpni');
1372 print_debug(' (p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1373 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1374 print_debug(' p_license_plate_number => ' );
1375 print_debug(' p_lpn_group_id => ' || l_group_id);
1376 print_debug(' p_parent_lpn_id => ' || l_tmp_mmtt_rec.transfer_lpn_id);
1377 END IF;
1378
1379 inv_rcv_integration_apis.insert_wlpni
1380 (p_api_version => 1.0
1381 ,x_return_status => x_return_status
1382 ,x_msg_count => x_msg_count
1383 ,x_msg_data => x_msg_data
1384 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1385 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1386 ,p_license_plate_number => NULL
1387 ,p_lpn_group_id => l_group_id
1388 ,p_parent_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1389 );
1390
1391 IF (x_return_status <> g_ret_sts_success) THEN
1392 IF (l_debug = 1) THEN
1393 print_debug('PACK_UNPACK_SPLIT: ERROR - Insert WLPN Fail');
1394 END IF;
1395 RAISE fnd_api.g_exc_error;
1396 END IF;
1397
1398 l_progress := '2.1.3';
1399
1400 -- Set flag to call to TM
1401 l_call_rm := TRUE;
1402
1403 IF (l_debug = 1) THEN
1404 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNI inserted successfully');
1405 END IF;
1406
1407 ELSE
1408 l_progress := '2.2.0';
1409 IF (l_debug = 1) THEN
1410 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') NO Sub/Loc changed');
1411 print_debug(' Calling packunpack_container(');
1412 print_debug(' p_content_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1413 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.transfer_lpn_id);
1414 print_debug(' p_operation => '||1);
1415 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1416 END IF;
1417
1418 -- Modify LPN Status
1419 wms_container_pvt.modify_lpn_wrapper
1420 ( p_api_version => 1.0
1421 ,x_return_status => x_return_status
1422 ,x_msg_count => x_msg_count
1423 ,x_msg_data => x_msg_data
1424 ,p_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1425 ,p_subinventory => l_tmp_mmtt_rec.transfer_subinventory
1426 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
1427 ,p_lpn_context => 3 --RCV
1428 );
1429 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1430 IF (l_debug = 1) THEN
1431 print_debug('PACK_UNPACK_SPLIT: modify_lpn failed');
1432 END IF;
1433 RAISE fnd_api.g_exc_error;
1434 END IF;
1435
1436 wms_container_pvt.packunpack_container
1437 (p_api_version => 1.0
1438 ,p_content_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1439 ,p_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1440 ,p_operation => 1 /* Pack */
1441 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1442 ,x_return_status => x_return_status
1443 ,x_msg_count => x_msg_count
1444 ,x_msg_data => x_msg_data
1445 );
1446 IF (x_return_status <> g_ret_sts_success) THEN
1447 IF (l_debug = 1) THEN
1448 print_debug('PACK_UNPACK_SPLIT: ERROR - packunpack_container Fail');
1449 END IF;
1450 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_PACK_UPDATE_ERR');
1451 fnd_msg_pub.ADD;
1452 RAISE fnd_api.g_exc_error;
1453 END IF;
1454
1455
1456 l_progress := '2.2.1';
1457 IF (l_debug = 1) THEN
1458 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') packunpack_container successful');
1459 END IF;
1460
1461 END IF;
1462 ELSIF (l_tmp_mmtt_rec.txn_type = 'LPN_UNPACK') THEN
1463 l_progress := '3.0';
1464 IF (l_debug = 1) THEN
1465 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') LPN UNPACK');
1466 END IF;
1467
1468 IF (l_tmp_mmtt_rec.sub_loc_changed = 1) THEN
1469 l_progress := '3.1.0';
1470 IF (l_debug = 1) THEN
1471 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
1472 END IF;
1473
1474 abort_mmtts
1475 (p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1476 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1477 ,x_return_status => x_return_status
1478 ,x_msg_count => x_msg_count
1479 ,x_msg_data => x_msg_data
1480 );
1481 IF (x_return_status <> g_ret_sts_success) THEN
1482 IF (l_debug = 1) THEN
1483 print_debug('PACK_UNPACK_SPLIT: ERROR - abort_mmtts Fail');
1484 END IF;
1485 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1486 fnd_msg_pub.ADD;
1487 RAISE fnd_api.g_exc_error;
1488 END IF;
1489
1490 l_progress := '3.1.1';
1491 IF (l_debug = 1) THEN
1492 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') MMTTs aborted successfully');
1493 END IF;
1494
1495 IF (l_first_time <> 1) THEN
1496 l_first_time := 1;
1497 END IF;
1498 l_group_id :=
1499 wms_putaway_utils.insert_rti
1500 (p_from_org => l_tmp_mmtt_rec.organization_id
1501 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1502 ,p_to_org => l_tmp_mmtt_rec.organization_id
1503 ,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
1504 ,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
1505 ,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1506 ,p_first_time => l_first_time
1507 ,p_mobile_txn => 'Y'
1508 ,p_txn_mode_code => p_txn_mode_code
1509 ,x_return_status => x_return_status
1510 ,x_msg_count => x_msg_count
1511 ,x_msg_data => x_msg_data
1512 );
1513 IF (x_return_status <> g_ret_sts_success) THEN
1514 IF (l_debug = 1) THEN
1515 print_debug('ERROR: insert_rti Fail');
1516 END IF;
1517 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
1518 fnd_msg_pub.ADD;
1519 RAISE fnd_api.g_exc_error;
1520 END IF;
1521
1522 l_progress := '3.1.2';
1523
1524 IF (l_debug = 1) THEN
1525 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') RTI inserted successfully');
1526 END IF;
1527
1528 IF (l_debug = 1) THEN
1529 print_debug('PACK_UNPACK_SPLIT: Calling insert_wlpni');
1530 print_debug(' (p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1531 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1532 print_debug(' p_license_plate_number => ' );
1533 print_debug(' p_lpn_group_id => ' || l_group_id);
1534 print_debug(' p_parent_lpn_id => )' );
1535 END IF;
1536
1537 inv_rcv_integration_apis.insert_wlpni
1538 (p_api_version => 1.0
1539 ,x_return_status => x_return_status
1540 ,x_msg_count => x_msg_count
1541 ,x_msg_data => x_msg_data
1542 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1543 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1544 ,p_license_plate_number => NULL
1545 ,p_lpn_group_id => l_group_id
1546 ,p_parent_lpn_id => NULL
1547 );
1548 IF (x_return_status <> g_ret_sts_success) THEN
1549 IF (l_debug = 1) THEN
1550 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_wlpni Fail');
1551 END IF;
1552 RAISE fnd_api.g_exc_error;
1553 END IF;
1554
1555 l_progress := '3.1.3';
1556
1557 IF (l_debug = 1) THEN
1558 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNI inserted successfully');
1559 END IF;
1560
1561 -- Set flag to call to TM
1562 l_call_rm := TRUE;
1563 ELSE
1564 l_progress := '3.2.0';
1565
1566 IF (l_debug = 1) THEN
1567 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') No Sub/Loc changed');
1568 print_debug(' Calling packunpack_container(');
1569 print_debug(' p_content_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1570 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
1571 print_debug(' p_operation => '||2);
1572 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1573 END IF;
1574
1575 wms_container_pvt.packunpack_container
1576 (p_api_version => 1.0
1577 ,p_content_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1578 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
1579 ,p_operation => 2 /* Unpack */
1580 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1581 ,x_return_status => x_return_status
1582 ,x_msg_count => x_msg_count
1583 ,x_msg_data => x_msg_data
1584 );
1585 IF (x_return_status <> g_ret_sts_success) THEN
1586 IF (l_debug = 1) THEN
1587 print_debug('PACK_UNPACK_SPLIT: packunpack_container Fail');
1588 END IF;
1589 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_UNPACK_UPDATE_ERR');
1590 fnd_msg_pub.ADD;
1591 RAISE fnd_api.g_exc_error;
1592 END IF;
1593
1594 l_progress := '3.2.1';
1595 IF (l_debug = 1) THEN
1596 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') packunpack_container sucessful');
1597 END IF;
1598
1599 END IF;
1600 ELSIF (l_tmp_mmtt_rec.txn_type = 'LPN_SPLIT') THEN
1601 l_progress := '4.0';
1602 IF (l_debug = 1) THEN
1603 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') LPN SPLIT');
1604 END IF;
1605
1606 IF (l_tmp_mmtt_rec.sub_loc_changed = 1) THEN
1607 l_progress := '4.1.0';
1608 IF (l_debug = 1) THEN
1609 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
1610 END IF;
1611
1612 abort_mmtts(p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1613 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1614 ,x_return_status => x_return_status
1615 ,x_msg_count => x_msg_count
1616 ,x_msg_data => x_msg_data
1617 );
1618 IF (x_return_status <> g_ret_sts_success) THEN
1619 print_debug('PACK_UNPACK_SPLIT: ERROR - abort_mmtts Fail');
1620 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1621 fnd_msg_pub.ADD;
1622 RAISE fnd_api.g_exc_error;
1623 END IF;
1624
1625 l_progress := '4.1.1';
1626 IF (l_debug = 1) THEN
1627 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') MMTTs aborted successfully');
1628 END IF;
1629
1630 IF (l_first_time <> 1) THEN
1631 l_first_time := 1;
1632 END IF;
1633 l_group_id :=
1634 wms_putaway_utils.insert_rti
1635 (p_from_org => l_tmp_mmtt_rec.organization_id
1636 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1637 ,p_to_org => l_tmp_mmtt_rec.organization_id
1638 ,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
1639 ,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
1640 ,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1641 ,p_first_time => l_first_time
1642 ,p_mobile_txn => 'Y'
1643 ,p_txn_mode_code => p_txn_mode_code
1644 ,x_return_status => x_return_status
1645 ,x_msg_count => x_msg_count
1646 ,x_msg_data => x_msg_data
1647 );
1648 IF (x_return_status <> g_ret_sts_success) THEN
1649 IF (l_debug = 1 ) THEN
1650 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_rti FAIL');
1651 END IF;
1652 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
1653 fnd_msg_pub.ADD;
1654 RAISE fnd_api.g_exc_error;
1655 END IF;
1656
1657 l_progress := '4.1.2';
1658 IF (l_debug = 1) THEN
1659 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
1660 ') RTIs inserted successfully');
1661 END IF;
1662
1663 IF (l_debug = 1) THEN
1664 print_debug('PACK_UNPACK_SPLIT: Calling insert_wlpni');
1665 print_debug(' (p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1666 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1667 print_debug(' p_license_plate_number => ' );
1668 print_debug(' p_lpn_group_id => ' || l_group_id);
1669 print_debug(' p_parent_lpn_id => ' || l_tmp_mmtt_rec.transfer_lpn_id);
1670 END IF;
1671
1672 inv_rcv_integration_apis.insert_wlpni
1673 (p_api_version => 1.0
1674 ,x_return_status => x_return_status
1675 ,x_msg_count => x_msg_count
1676 ,x_msg_data => x_msg_data
1677 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1678 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1679 ,p_license_plate_number => NULL
1680 ,p_lpn_group_id => l_group_id
1681 ,p_parent_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1682 );
1683
1684 IF (x_return_status <> g_ret_sts_success) THEN
1685 IF (l_debug = 1) THEN
1686 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_wlpni FAIL');
1687 END IF;
1688 RAISE fnd_api.g_exc_error;
1689 END IF;
1690
1691 l_progress := '4.1.3';
1692 IF (l_debug = 1) THEN
1693 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNIs inserted successfully');
1694 END IF;
1695
1696 -- Set flag to call to TM
1697 l_call_rm := TRUE;
1698 ELSE
1699 l_progress := '4.2.0';
1700 IF (l_debug = 1) THEN
1701 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') NO Sub/Loc changed');
1702 print_debug(' Calling packunpack_container(');
1703 print_debug(' p_content_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1704 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
1705 print_debug(' p_operation => '||2);
1706 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1707 END IF;
1708
1709 wms_container_pvt.packunpack_container
1710 (p_api_version => 1.0
1711 ,p_content_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1712 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
1713 ,p_operation => 2 /* Unpack */
1714 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1715 ,x_return_status => x_return_status
1716 ,x_msg_count => x_msg_count
1717 ,x_msg_data => x_msg_data
1718 );
1719 IF (x_return_status <> g_ret_sts_success) THEN
1720 IF (l_debug = 1) THEN
1721 print_debug('PACK_UNPACK_SPLIT: ERROR - packunpack_container Fail');
1722 END IF;
1723 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_UNPACK_UPDATE_ERR');
1724 fnd_msg_pub.ADD;
1725 RAISE fnd_api.g_exc_error;
1726 END IF;
1727
1728 l_progress := '4.2.1';
1729 IF (l_debug = 1) THEN
1730 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') packunpack_container UNPACK succesful');
1731 print_debug(' Calling packunpack_container(');
1732 print_debug(' p_content_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1733 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.transfer_lpn_id);
1734 print_debug(' p_operation => '||1);
1735 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1736 END IF;
1737
1738 -- Modify LPN Status
1739 wms_container_pvt.modify_lpn_wrapper
1740 ( p_api_version => 1.0
1741 ,x_return_status => x_return_status
1742 ,x_msg_count => x_msg_count
1743 ,x_msg_data => x_msg_data
1744 ,p_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1745 ,p_subinventory => l_tmp_mmtt_rec.transfer_subinventory
1746 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
1747 ,p_lpn_context => 3 --RCV
1748 );
1749 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1750 IF (l_debug = 1) THEN
1751 print_debug('PACK_UNPACK_SPLIT: modify_lpn failed');
1752 END IF;
1753 RAISE fnd_api.g_exc_error;
1754 END IF;
1755
1756 wms_container_pvt.packunpack_container
1757 (p_api_version => '1.0' --??
1758 ,p_content_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1759 ,p_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1760 ,p_operation => 1 /* pack */
1761 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1762 ,x_return_status => x_return_status
1763 ,x_msg_count => x_msg_count
1764 ,x_msg_data => x_msg_data
1765 );
1766 IF (x_return_status <> g_ret_sts_success) THEN
1767 IF (l_debug = 1) THEN
1768 print_debug('PACK_UNPACK_SPLIT: ERROR: packunpack_container Fail', 9);
1769 END IF;
1770 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_PACK_UPDATE_ERR');
1771 fnd_msg_pub.ADD;
1772 RAISE fnd_api.g_exc_error;
1773 END IF;
1774
1775 l_progress := '2.2.1';
1776 IF (l_debug = 1) THEN
1777 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') packunpack_container successful');
1778 END IF;
1779
1780
1781
1782 END IF;
1783 ELSIF (l_tmp_mmtt_rec.txn_type = 'ITEM_PACK') THEN
1784 l_progress := '5.0';
1785 IF (l_debug = 1) THEN
1786 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Item Pack');
1787 END IF;
1788
1789 abort_mmtts(p_move_order_line_id=>l_tmp_mmtt_rec.move_order_line_id
1790 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1791 ,x_return_status => x_return_status
1792 ,x_msg_count => x_msg_count
1793 ,x_msg_data => x_msg_data
1794 );
1795 IF (x_return_status <> g_ret_sts_success) THEN
1796 IF (l_debug = 1) THEN
1797 print_debug('PACK_UNPACK_SPLIT: ERROR: abort_mmtts FAIL', 9);
1798 END IF;
1799 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1800 fnd_msg_pub.ADD;
1801 RAISE fnd_api.g_exc_error;
1802 END IF;
1803
1804 l_mo_splt_tb(1).prim_qty := l_tmp_mmtt_rec.primary_quantity;
1805
1806 IF (l_debug = 1) THEN
1807 print_debug('PACK_UNPACK_SPLIT: MMTTs aborted');
1808 print_debug('PACK_UNPACK_SPLIT: Calling split_mo(');
1809 print_debug(' p_orig_mol_id => ' || l_tmp_mmtt_rec.move_order_line_id);
1810 print_debug(' p_mo_splt_tb(1).prim_qty => ' || l_mo_splt_tb(1).prim_qty);
1811 END IF;
1812
1813 inv_rcv_integration_apis.split_mo
1814 (p_orig_mol_id => l_tmp_mmtt_rec.move_order_line_id
1815 ,p_mo_splt_tb => l_mo_splt_tb
1816 ,x_return_status => x_return_status
1817 ,x_msg_count => x_msg_count
1818 ,x_msg_data => x_msg_data
1819 );
1820 IF (x_return_status <> g_ret_sts_success) THEN
1821 IF (l_debug = 1) THEN
1822 print_debug('PACK_UNPACK_SPLIT: ERROR - split_mo FAIL', 9);
1823 END IF;
1824 fnd_message.set_name('INV', 'INV_SPLIT_MO_ERR');
1825 fnd_msg_pub.ADD;
1826 RAISE fnd_api.g_exc_error;
1827 END IF;
1828
1829 IF (l_tmp_mmtt_rec.move_order_line_id <> l_mo_splt_tb(1).line_id) THEN
1830 IF (l_debug = 1) THEN
1831 print_debug('PACK_UNPACK_SPLIT: Unmarking wms_process_flag FOR line: '
1832 ||l_tmp_mmtt_rec.move_order_line_id);
1833 END IF;
1834
1835 --Update the MOL with unused qty to 1, because the TM will only
1836 --update wms_process_flag for the marked lines and not all
1837 --lines in a LPN as done in 11.5.10
1838 UPDATE mtl_txn_request_lines
1839 SET wms_process_flag = 1
1840 WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
1841 END IF;
1842
1843 l_progress := '5.1';
1844 IF (l_debug = 1) THEN
1845 print_debug('PACK_UNPACK_SPLIT: Calling insert_lot_serial(');
1846 print_debug(' p_transaction_temp_id => '||l_tmp_mmtt_rec.transaction_temp_id);
1847 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1848 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
1849 END IF;
1850
1851 l_old_intf_id :=
1852 insert_lot_serial
1853 (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
1854 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1855 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
1856 ,x_return_status => x_return_status
1857 ,x_msg_count => x_msg_count
1858 ,x_msg_data => x_msg_data
1859 );
1860
1861 IF (x_return_status <> g_ret_sts_success) THEN
1862 IF (l_debug = 1) THEN
1863 print_debug('PACK_UNPACK_SPLIT: ERROR: insert_lot_serial FAIL',
1864 9);
1865 END IF;
1866 RAISE fnd_api.g_exc_error;
1867 END IF;
1868
1869 l_progress := '5.2';
1870
1871 IF (l_debug = 1) THEN
1872 print_debug('PACK_UNPACK_SPLIT: insert_lot_serial returns successfully WITH interface id = ' || l_old_intf_id);
1873 END IF;
1874
1875 /* Retrieve MOL */
1876
1877 OPEN mol_cur(l_mo_splt_tb(1).line_id);
1878 FETCH mol_cur INTO l_mol_rec;
1879 CLOSE mol_cur;
1880
1881 l_progress := '5.3';
1882
1883 IF (l_debug = 1) THEN
1884 print_debug('PACK_UNPACK_SPLIT: Calling Match_transfer_rcvtxn_rec(');
1885 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1886 print_debug(' p_parent_txn_id => '||l_mol_rec.txn_source_id);
1887 print_debug(' p_reference_id => '||l_mol_rec.reference_id);
1888 print_debug(' p_reference => '||l_mol_rec.reference);
1889 print_debug(' p_reference_type_code => '||l_mol_rec.reference_type_code);
1890 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
1891 print_debug(' p_revision => '||l_mol_rec.revision);
1892 print_debug(' p_subinventory_code => '||l_tmp_mmtt_rec.transfer_subinventory);
1893 print_debug(' p_locator_id => '||l_tmp_mmtt_rec.transfer_to_location);
1894 print_debug(' p_transfer_quantity => '||l_tmp_mmtt_rec.transaction_quantity);
1895 print_debug(' p_transfer_uom_code => '||l_tmp_mmtt_rec.transaction_uom);
1896 print_debug(' p_lot_control_code => '||l_tmp_mmtt_rec.lot_control_code);
1897 print_debug(' p_serial_control_code => '||l_tmp_mmtt_rec.serial_control_code);
1898 print_debug(' p_original_rti_id => '||l_old_intf_id);
1899 print_debug(' p_original_temp_id => ');
1900 print_debug(' p_lot_number => '||l_mol_rec.lot_number);
1901 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
1902 print_debug(' p_transfer_lpn_id => '||l_tmp_mmtt_rec.transfer_lpn_id);
1903 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
1904
1905 -- OPM Covergance
1906 print_debug(' p_sec_transfer_quantity => '||l_tmp_mmtt_rec.secondary_transaction_quantity);
1907 print_debug(' p_sec_transfer_uom_code => '||l_tmp_mmtt_rec.secondary_uom_code);
1908 -- OPM Covergance
1909 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
1910 print_debug(' p_from_sub => '||l_tmp_mmtt_rec.subinventory_code);
1911 print_debug(' p_from_loc => '||l_tmp_mmtt_rec.locator_id);
1912 END IF;
1913
1914 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
1915 ( x_return_status => x_return_status
1916 ,x_msg_count => x_msg_count
1917 ,x_msg_data => x_msg_data
1918 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1919 ,p_parent_txn_id => l_mol_rec.txn_source_id
1920 ,p_reference_id => l_mol_rec.reference_id
1921 ,p_reference => l_mol_rec.reference
1922 ,p_reference_type_code => l_mol_rec.reference_type_code
1923 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
1924 ,p_revision => l_mol_rec.revision --??
1925 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
1926 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
1927 ,p_transfer_quantity => l_tmp_mmtt_rec.transaction_quantity
1928 ,p_transfer_uom_code => l_tmp_mmtt_rec.transaction_uom
1929 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
1930 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
1931 ,p_original_rti_id => l_old_intf_id
1932 ,p_original_temp_id => NULL
1933 ,p_lot_number => l_mol_rec.lot_number
1934 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
1935 ,p_transfer_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1936 -- OPM Convergance
1937 ,p_sec_transfer_quantity => l_tmp_mmtt_rec.secondary_transaction_quantity
1938 ,p_sec_transfer_uom_code => l_tmp_mmtt_rec.secondary_uom_code
1939 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
1940 -- OPM Convergance
1941 ,p_inspection_status => l_mol_rec.inspection_status
1942 ,p_from_sub => l_tmp_mmtt_rec.subinventory_code
1943 ,p_from_loc => l_tmp_mmtt_rec.locator_id
1944 ,p_shipment_header_id => l_shipment_header_id -- added for bug 14269755
1945 );
1946 IF (x_return_status <> g_ret_sts_success) THEN
1947 IF (l_debug = 1) THEN
1948 print_debug('PACK_UNPACK_SPLIT: ERROR - Match_transfer_rcvtxn_rec FAIL', 9);
1949 END IF;
1950 RAISE fnd_api.g_exc_error;
1951 END IF;
1952
1953 l_progress := '5.4';
1954
1955 -- Set flag to call to TM
1956 l_call_rm := TRUE;
1957
1958 ELSIF (l_tmp_mmtt_rec.txn_type = 'ITEM_UNPACK') THEN
1959 l_progress := '6.0';
1960 IF (l_debug = 1) THEN
1961 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Item Unpack');
1962 END IF;
1963
1964 IF (l_tmp_mmtt_rec.sub_loc_changed = 1) THEN
1965 l_progress := '6.1.0';
1966 IF (l_debug = 1) THEN
1967 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
1968 END IF;
1969
1970 l_mo_splt_tb(1).prim_qty := l_tmp_mmtt_rec.primary_quantity;
1971
1972 IF (l_debug = 1) THEN
1973 print_debug('PACK_UNPACK_SPLIT: Calling split_mo(');
1974 print_debug(' p_orig_mol_id => ' || l_tmp_mmtt_rec.move_order_line_id);
1975 print_debug(' p_mo_splt_tb(1).prim_qty => ' || l_mo_splt_tb(1).prim_qty);
1976 END IF;
1977
1978 inv_rcv_integration_apis.split_mo
1979 (p_orig_mol_id => l_tmp_mmtt_rec.move_order_line_id
1980 ,p_mo_splt_tb => l_mo_splt_tb
1981 ,x_return_status => x_return_status
1982 ,x_msg_count => x_msg_count
1983 ,x_msg_data => x_msg_data
1984 );
1985 IF (x_return_status <> g_ret_sts_success) THEN
1986 IF (l_debug = 1) THEN
1987 print_debug('PACK_UNPACK_SPLIT: ERROR - split_mo FAIL', 9);
1988 END IF;
1989 fnd_message.set_name('INV', 'INV_SPLIT_MO_ERR');
1990 fnd_msg_pub.ADD;
1991 RAISE fnd_api.g_exc_error;
1992 END IF;
1993
1994 IF (l_tmp_mmtt_rec.move_order_line_id <> l_mo_splt_tb(1).line_id) THEN
1995 IF (l_debug = 1) THEN
1996 print_debug('PACK_UNPACK_SPLIT: Unmarking wms_process_flag FOR line: '
1997 ||l_tmp_mmtt_rec.move_order_line_id);
1998 END IF;
1999
2000 --Update the MOL with unused qty to 1, because the TM will only
2001 --update wms_process_flag for the marked lines and not all
2002 --lines in a LPN as done in 11.5.10
2003 UPDATE mtl_txn_request_lines
2004 SET wms_process_flag = 1
2005 WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
2006 END IF;
2007
2008 l_progress := '6.1.1';
2009
2010 FOR i IN 1 .. l_mo_splt_tb.COUNT LOOP
2011 l_out_mo_splt_tb(l_out_mo_splt_tb.COUNT+i).line_id
2012 := l_mo_splt_tb(i).line_id;
2013 END LOOP;
2014
2015 l_progress := '6.1.2';
2016
2017 IF (l_debug = 1) THEN
2018 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Calling abort_mmtts');
2019 END IF;
2020
2021 abort_mmtts(p_move_order_line_id => l_mo_splt_tb(1).line_id
2022 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2023 ,x_return_status => x_return_status
2024 ,x_msg_count => x_msg_count
2025 ,x_msg_data => x_msg_data
2026 );
2027 IF (x_return_status <> g_ret_sts_success) THEN
2028 IF (l_debug = 1) THEN
2029 print_debug('PACK_UNPACK_SPLIT: ERROR - abort_mmtts FAIL', 9);
2030 END IF;
2031 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
2032 fnd_msg_pub.ADD;
2033 RAISE fnd_api.g_exc_error;
2034 END IF;
2035
2036 l_progress := '6.1.3';
2037
2038 IF (l_debug = 1) THEN
2039 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Calling insert_lot_serial');
2040 print_debug(' p_transaction_temp_id => '||l_tmp_mmtt_rec.transaction_temp_id);
2041 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
2042 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
2043 END IF;
2044
2045 l_old_intf_id :=
2046 insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
2047 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2048 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2049 ,x_return_status => x_return_status
2050 ,x_msg_count => x_msg_count
2051 ,x_msg_data => x_msg_data
2052 );
2053 IF (x_return_status <> g_ret_sts_success) THEN
2054 IF (l_debug = 1) THEN
2055 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_lot_serial FAIL', 9);
2056 END IF;
2057 RAISE fnd_api.g_exc_error;
2058 END IF;
2059
2060 l_progress := '6.1.4';
2061
2062 OPEN mol_cur(l_tmp_mmtt_rec.move_order_line_id);
2063 FETCH mol_cur INTO l_mol_rec;
2064 CLOSE mol_cur;
2065
2066 l_progress := '6.1.5';
2067
2068 IF (l_debug = 1) THEN
2069 print_debug('PACK_UNPACK_SPLIT: Calling Match_transfer_rcvtxn_rec(');
2070 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
2071 print_debug(' p_parent_txn_id => '||l_mol_rec.txn_source_id);
2072 print_debug(' p_reference_id => '||l_mol_rec.reference_id);
2073 print_debug(' p_reference => '||l_mol_rec.reference);
2074 print_debug(' p_reference_type_code => '||l_mol_rec.reference_type_code);
2075 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
2076 print_debug(' p_revision => '||l_mol_rec.revision);
2077 print_debug(' p_subinventory_code => '||l_tmp_mmtt_rec.transfer_subinventory);
2078 print_debug(' p_locator_id => '||l_tmp_mmtt_rec.transfer_to_location);
2079 print_debug(' p_transfer_quantity => '||l_tmp_mmtt_rec.transaction_quantity);
2080 print_debug(' p_transfer_uom_code => '||l_tmp_mmtt_rec.transaction_uom);
2081 print_debug(' p_lot_control_code => '||l_tmp_mmtt_rec.lot_control_code);
2082 print_debug(' p_serial_control_code => '||l_tmp_mmtt_rec.serial_control_code);
2083 print_debug(' p_original_rti_id => '||l_old_intf_id);
2084 print_debug(' p_original_temp_id => ');
2085 print_debug(' p_lot_number => '||l_mol_rec.lot_number);
2086 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
2087 print_debug(' p_transfer_lpn_id => ');
2088 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
2089
2090 -- OPM Convergance
2091 print_debug(' p_sec_transfer_quantity => '||l_tmp_mmtt_rec.secondary_transaction_quantity);
2092 print_debug(' p_sec_transfer_uom_code => '||l_tmp_mmtt_rec.secondary_uom_code);
2093 -- OPM Convergance
2094 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
2095 print_debug(' p_from_sub => '||l_tmp_mmtt_rec.subinventory_code);
2096 print_debug(' p_from_loc => '||l_tmp_mmtt_rec.locator_id);
2097 END IF;
2098
2099 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2100 ( x_return_status => x_return_status
2101 ,x_msg_count => x_msg_count
2102 ,x_msg_data => x_msg_data
2103 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2104 ,p_parent_txn_id => l_mol_rec.txn_source_id
2105 ,p_reference_id => l_mol_rec.reference_id
2106 ,p_reference => l_mol_rec.reference
2107 ,p_reference_type_code => l_mol_rec.reference_type_code
2108 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2109 ,p_revision => l_mol_rec.revision --??
2110 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2111 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2112 ,p_transfer_quantity => l_tmp_mmtt_rec.transaction_quantity
2113 ,p_transfer_uom_code => l_tmp_mmtt_rec.transaction_uom
2114 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2115 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2116 ,p_original_rti_id => l_old_intf_id
2117 ,p_original_temp_id => NULL
2118 ,p_lot_number => l_mol_rec.lot_number
2119 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
2120 ,p_transfer_lpn_id => NULL
2121 -- OPM Convergance
2122 ,p_sec_transfer_quantity => l_tmp_mmtt_rec.secondary_transaction_quantity
2123 ,p_sec_transfer_uom_code => l_tmp_mmtt_rec.secondary_uom_code
2124 -- OPM Convergance
2125 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2126 ,p_inspection_status => l_mol_rec.inspection_status
2127 ,p_from_sub => l_tmp_mmtt_rec.subinventory_code
2128 ,p_from_loc => l_tmp_mmtt_rec.locator_id
2129 ,p_shipment_header_id => l_shipment_header_id -- added for bug 14269755
2130 );
2131 IF (x_return_status <> g_ret_sts_success) THEN
2132 IF (l_debug = 1) THEN
2133 print_debug('ERROR: Match_transfer_rcvtxn_rec FAIL', 9);
2134 END IF;
2135 RAISE fnd_api.g_exc_error;
2136 END IF;
2137
2138 -- Set flag to call to TM
2139 l_call_rm := TRUE;
2140
2141 l_progress := '6.1.6';
2142
2143 IF (l_debug = 1) THEN
2144 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Xfer api sucessfully returns');
2145 END IF;
2146 ELSE -- no sub/loc changed
2147 l_progress := '6.2.0';
2148 IF (l_debug = 1) THEN
2149 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2150 ') No Sub/Loc changed');
2151 END IF;
2152
2153 l_mo_splt_tb(1).prim_qty := l_tmp_mmtt_rec.primary_quantity;
2154
2155 IF (l_debug = 1) THEN
2156 print_debug('PACK_UNPACK_SPLIT: Calling split_mo(');
2157 print_debug(' p_orig_mol_id => ' || l_tmp_mmtt_rec.move_order_line_id);
2158 print_debug(' p_mo_splt_tb(1).prim_qty => ' || l_mo_splt_tb(1).prim_qty);
2159 END IF;
2160
2161 inv_rcv_integration_apis.split_mo
2162 (p_orig_mol_id => l_tmp_mmtt_rec.move_order_line_id
2163 ,p_mo_splt_tb => l_mo_splt_tb
2164 ,x_return_status => x_return_status
2165 ,x_msg_count => x_msg_count
2166 ,x_msg_data => x_msg_data
2167 );
2168 IF (x_return_status <> g_ret_sts_success) THEN
2169 IF (l_debug = 1) THEN
2170 print_debug('PACK_UNPACK_SPLIT: ERROR - split_mo FAIL', 9);
2171 END IF;
2172 fnd_message.set_name('INV', 'INV_SPLIT_MO_ERR');
2173 fnd_msg_pub.ADD;
2174 RAISE fnd_api.g_exc_error;
2175 END IF;
2176
2177 IF (l_tmp_mmtt_rec.move_order_line_id <> l_mo_splt_tb(1).line_id) THEN
2178 IF (l_debug = 1) THEN
2179 print_debug('PACK_UNPACK_SPLIT: Unmarking wms_process_flag FOR line: '
2180 ||l_tmp_mmtt_rec.move_order_line_id);
2181 END IF;
2182
2183 --Update the MOL with unused qty to 1, because the TM will only
2184 --update wms_process_flag for the marked lines and not all
2185 --lines in a LPN as done in 11.5.10
2186 UPDATE mtl_txn_request_lines
2187 SET wms_process_flag = 1
2188 WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
2189 END IF;
2190
2191 l_progress := '6.2.1';
2192
2193 FOR i IN 1 .. l_mo_splt_tb.COUNT LOOP
2194 IF (l_debug = 1) THEN
2195 print_debug('PACK_UNPACK_SPLIT: split_mo created MOL: '
2196 ||l_mo_splt_tb(i).line_id);
2197 END IF;
2198 l_out_mo_splt_tb(l_out_mo_splt_tb.COUNT+i).line_id
2199 := l_mo_splt_tb(i).line_id;
2200 END LOOP;
2201
2202 l_progress := '6.2.2';
2203
2204 IF (l_debug = 1) THEN
2205 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Calling abort_mmtts');
2206 END IF;
2207
2208 --Even when there is no sub/loc changed, we need to abort
2209 --the operation plan because they are LPN based.
2210 abort_mmtts(p_move_order_line_id => l_mo_splt_tb(1).line_id
2211 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2212 ,x_return_status => x_return_status
2213 ,x_msg_count => x_msg_count
2214 ,x_msg_data => x_msg_data
2215 );
2216 IF (x_return_status <> g_ret_sts_success) THEN
2217 IF (l_debug = 1) THEN
2218 print_debug('PACK_UNPACK_SPLIT: ERROR - abort_mmtts FAIL', 9);
2219 END IF;
2220 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
2221 fnd_msg_pub.ADD;
2222 RAISE fnd_api.g_exc_error;
2223 END IF;
2224
2225 l_progress := '6.2.2.5';
2226
2227
2228 IF (l_debug = 1) THEN
2229 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') split_mo sucessfully returns. Calling insert_lot_serial');
2230 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Calling insert_lot_serial');
2231 print_debug(' p_transaction_temp_id => '||l_tmp_mmtt_rec.transaction_temp_id);
2232 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
2233 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
2234
2235 END IF;
2236
2237 l_old_intf_id :=
2238 insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
2239 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2240 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2241 ,x_return_status => x_return_status
2242 ,x_msg_count => x_msg_count
2243 ,x_msg_data => x_msg_data
2244 );
2245 IF (x_return_status <> g_ret_sts_success) THEN
2246 IF (l_debug = 1) THEN
2247 print_debug('ERROR: insert_lot_serial FAIL', 9);
2248 END IF;
2249 RAISE fnd_api.g_exc_error;
2250 END IF;
2251
2252 l_progress := '6.2.3';
2253
2254 OPEN mol_cur(l_tmp_mmtt_rec.move_order_line_id);
2255 FETCH mol_cur INTO l_mol_rec;
2256 CLOSE mol_cur;
2257
2258 l_progress := '6.2.4';
2259
2260 IF (l_debug = 1) THEN
2261 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') insert_lot_serail sucessfully returns. Starts TO LOOP mmtts');
2262 END IF;
2263
2264 -- Look at each new mmtt, and insert rti for each of them
2265 -- Bug 5231114: Added the condition on transaction_source_type_id and
2266 -- transaction_action_id for the following combinations:13/12 and 4/27
2267 FOR l_mmtt_rec IN
2268 ( SELECT
2269 transaction_temp_id
2270 ,transaction_quantity
2271 ,primary_quantity
2272 ,transaction_uom
2273 ,secondary_transaction_quantity
2274 ,secondary_uom_code
2275 FROM
2276 mtl_material_transactions_temp
2277 WHERE
2278 ( move_order_line_id = l_mo_splt_tb(1).line_id AND
2279 ( ( transaction_source_type_id = 1 AND
2280 transaction_action_id = 27) OR
2281 ( transaction_source_type_id = 7 AND
2282 transaction_action_id = 12) OR
2283 ( transaction_source_type_id = 12 AND
2284 transaction_action_id = 27) OR
2285 ( transaction_source_type_id = 13 AND
2286 transaction_action_id = 12) OR
2287 ( transaction_source_type_id = 4 AND
2288 transaction_action_id = 27))) )
2289 LOOP
2290 -- keep track of remaining quantity
2291 l_progress := '6.2.5';
2292
2293 IF (l_debug = 1) THEN
2294 print_debug('PACK_UNPACK_SPLIT: Looking at:');
2295 print_debug(' l_mmtt_rec.transaction_temp_id => ' || l_mmtt_rec.transaction_temp_id);
2296 print_debug(' l_mmtt_rec.transaction_quantity => ' || l_mmtt_rec.transaction_quantity);
2297 print_debug(' l_mmtt_rec.primary_quantity => ' || l_mmtt_rec.primary_quantity);
2298 print_debug(' l_mmtt_rec.transaction_uom => ' || l_mmtt_rec.primary_quantity);
2299 END IF;
2300
2301 l_tmp_mmtt_rec.primary_quantity :=
2302 l_tmp_mmtt_rec.primary_quantity -
2303 l_mmtt_rec.primary_quantity;
2304
2305 l_progress := '6.2.6';
2306
2307 /* Create a dummy RT ID */
2308 SELECT rcv_transactions_interface_s.NEXTVAL
2309 INTO l_new_intf_id
2310 FROM dual;
2311
2312 l_progress := '6.2.7';
2313
2314 /* Split lot serial */
2315 l_rti_tb.DELETE;
2316
2317 l_rti_tb(1).orig_interface_trx_id := l_old_intf_id;
2318 l_rti_tb(1).new_interface_trx_id := l_new_intf_id;
2319 l_rti_tb(1).quantity := l_mmtt_rec.transaction_quantity;
2320 l_rti_tb(1).to_organization_id := l_tmp_mmtt_rec.organization_id;
2321 l_rti_tb(1).item_id := l_tmp_mmtt_rec.inventory_item_id;
2322 l_rti_tb(1).uom_code := l_tmp_mmtt_rec.transaction_uom;
2323 IF (l_debug = 1) THEN
2324 print_debug('PACK_UNPACK_SPLIT: Calling split_lot_serial(');
2325 print_debug(' p_rti_tb(1).orig_interface_trx_id => '
2326 || l_old_intf_id);
2327 print_debug(' p_rti_tb(1).new_interface_trx_id => '
2328 || l_new_intf_id);
2329 print_debug(' p_rti_tb(1).quantity => '
2330 || l_mmtt_rec.transaction_quantity);
2331 END IF;
2332
2333 IF (l_tmp_mmtt_rec.primary_quantity > 0) THEN
2334 l_rti_tb(2).orig_interface_trx_id := l_old_intf_id;
2335 l_rti_tb(2).new_interface_trx_id := l_old_intf_id;
2336
2337 IF (l_tmp_mmtt_rec.primary_uom_code <> l_mmtt_rec.transaction_uom) THEN
2338 l_rti_tb(2).quantity := inv_rcv_cache.convert_qty
2339 (l_tmp_mmtt_rec.inventory_item_id
2340 ,l_tmp_mmtt_rec.primary_quantity
2341 ,l_tmp_mmtt_rec.primary_uom_code
2342 ,l_mmtt_rec.transaction_uom);
2343 ELSE
2344 l_rti_tb(2).quantity := l_tmp_mmtt_rec.primary_quantity;
2345 END IF;
2346
2347 l_rti_tb(2).to_organization_id := l_tmp_mmtt_rec.organization_id;
2348 l_rti_tb(2).item_id := l_tmp_mmtt_rec.inventory_item_id;
2349 l_rti_tb(2).uom_code := l_tmp_mmtt_rec.transaction_uom;
2350 END IF;
2351
2352 inv_rcv_integration_pvt.split_lot_serial
2353 (p_rti_tb => l_rti_tb
2354 ,x_return_status => x_return_status
2355 ,x_msg_count => x_msg_count
2356 ,x_msg_data => x_msg_data
2357 );
2358 IF (x_return_status <> g_ret_sts_success) THEN
2359 IF (l_debug = 1) THEN
2360 print_debug('ERROR: split_lot_serial FAIL', 9);
2361 END IF;
2362 RAISE fnd_api.g_exc_error;
2363 END IF;
2364
2365 l_progress := '6.2.8';
2366
2367 IF (l_debug = 1) THEN
2368 print_debug('PACK_UNPACK_SPLIT: Calling Match_transfer_rcvtxn_rec(');
2369 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
2370 print_debug(' p_parent_txn_id => '||l_mol_rec.txn_source_id);
2371 print_debug(' p_reference_id => '||l_mol_rec.reference_id);
2372 print_debug(' p_reference => '||l_mol_rec.reference);
2373 print_debug(' p_reference_type_code => '||l_mol_rec.reference_type_code);
2374 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
2375 print_debug(' p_revision => '||l_mol_rec.revision);
2376 print_debug(' p_subinventory_code => '||l_tmp_mmtt_rec.transfer_subinventory);
2377 print_debug(' p_locator_id => '||l_tmp_mmtt_rec.transfer_to_location);
2378 print_debug(' p_transfer_quantity => '||l_tmp_mmtt_rec.transaction_quantity);
2379 print_debug(' p_transfer_uom_code => '||l_tmp_mmtt_rec.transaction_uom);
2380 print_debug(' p_lot_control_code => '||l_tmp_mmtt_rec.lot_control_code);
2381 print_debug(' p_serial_control_code => '||l_tmp_mmtt_rec.serial_control_code);
2382 print_debug(' p_original_rti_id => '||l_new_intf_id);
2383 print_debug(' p_original_temp_id => '||l_mmtt_rec.transaction_temp_id);
2384 print_debug(' p_lot_number => '||l_mol_rec.lot_number);
2385 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
2386 print_debug(' p_transfer_lpn_id => )');
2387 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
2388 -- OPM Convergance
2389 print_debug(' p_sec_transfer_quantity => '||l_tmp_mmtt_rec.secondary_transaction_quantity);
2390 print_debug(' p_sec_transfer_uom_code => '||l_tmp_mmtt_rec.secondary_uom_code);
2391 -- OPM Convergance
2392 END IF;
2393
2394 /* Call transfer API with the new RT ID */
2395 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2396 ( x_return_status => x_return_status
2397 ,x_msg_count => x_msg_count
2398 ,x_msg_data => x_msg_data
2399 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2400 ,p_parent_txn_id => l_mol_rec.txn_source_id
2401 ,p_reference_id => l_mol_rec.reference_id
2402 ,p_reference => l_mol_rec.reference
2403 ,p_reference_type_code => l_mol_rec.reference_type_code
2404 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2405 ,p_revision => l_mol_rec.revision
2406 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2407 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2408 ,p_transfer_quantity => l_mmtt_rec.transaction_quantity
2409 ,p_transfer_uom_code => l_mmtt_rec.transaction_uom
2410 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2411 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2412 ,p_original_rti_id => l_new_intf_id
2413 ,p_original_temp_id => l_mmtt_rec.transaction_temp_id
2414 ,p_lot_number => l_mol_rec.lot_number
2415 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
2416 ,p_transfer_lpn_id => NULL
2417 -- OPM Convergance
2418 ,p_sec_transfer_quantity => l_mmtt_rec.secondary_transaction_quantity
2419 ,p_sec_transfer_uom_code => l_mmtt_rec.secondary_uom_code
2420 -- OPM Convergance
2421 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2422 ,p_inspection_status => l_mol_rec.inspection_status
2423 ,p_shipment_header_id => l_shipment_header_id -- added for bug 14269755
2424 );
2425 IF (x_return_status <> g_ret_sts_success) THEN
2426 IF (l_debug = 1) THEN
2427 print_debug('PACK_UNPACK_SPLIT : ERROR - Match_transfer_rcvtxn_rec FAIL',
2428 9);
2429 END IF;
2430 RAISE fnd_api.g_exc_error;
2431 END IF;
2432
2433 l_progress := '6.2.9';
2434
2435 IF (l_debug = 1) THEN
2436 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2437 ') xfer api returns sucessfully');
2438 END IF;
2439
2440 END LOOP;
2441
2442 l_progress := '6.2.10';
2443 IF (l_debug = 1) THEN
2444 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Loop exited. Looking at remaining quantities');
2445 END IF;
2446
2447 IF (l_tmp_mmtt_rec.primary_quantity > 0) THEN
2448 -- Call transfer API for the remaining qty (not detailed)
2449 -- with the old RT ID
2450 l_progress := '6.2.11';
2451 IF (l_debug = 1) THEN
2452 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2453 ') There are remaining quantities. Calling xfer api');
2454 END IF;
2455
2456 IF (l_debug = 1) THEN
2457 print_debug('PACK_UNPACK_SPLIT: Calling Match_transfer_rcvtxn_rec(');
2458 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
2459 print_debug(' p_parent_txn_id => '||l_mol_rec.txn_source_id);
2460 print_debug(' p_reference_id => '||l_mol_rec.reference_id);
2461 print_debug(' p_reference => '||l_mol_rec.reference);
2462 print_debug(' p_reference_type_code => '||l_mol_rec.reference_type_code);
2463 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
2464 print_debug(' p_revision => '||l_mol_rec.revision);
2465 print_debug(' p_subinventory_code => '||l_tmp_mmtt_rec.transfer_subinventory);
2466 print_debug(' p_locator_id => '||l_tmp_mmtt_rec.transfer_to_location);
2467 print_debug(' p_transfer_quantity => '||l_tmp_mmtt_rec.transaction_quantity);
2468 print_debug(' p_transfer_uom_code => '||l_tmp_mmtt_rec.transaction_uom);
2469 print_debug(' p_lot_control_code => '||l_tmp_mmtt_rec.lot_control_code);
2470 print_debug(' p_serial_control_code => '||l_tmp_mmtt_rec.serial_control_code);
2471 print_debug(' p_original_rti_id => '||l_new_intf_id);
2472 print_debug(' p_original_temp_id => ');
2473 print_debug(' p_lot_number => '||l_mol_rec.lot_number);
2474 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
2475 print_debug(' p_transfer_lpn_id => )');
2476 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
2477
2478 -- OPM Convergance
2479 print_debug(' p_sec_transfer_quantity => '||l_tmp_mmtt_rec.secondary_transaction_quantity);
2480 print_debug(' p_sec_transfer_uom_code => '||l_tmp_mmtt_rec.secondary_uom_code);
2481 -- OPM Convergance
2482 END IF;
2483 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2484 ( x_return_status => x_return_status
2485 ,x_msg_count => x_msg_count
2486 ,x_msg_data => x_msg_data
2487 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2488 ,p_parent_txn_id => l_mol_rec.txn_source_id
2489 ,p_reference_id => l_mol_rec.reference_id
2490 ,p_reference => l_mol_rec.reference
2491 ,p_reference_type_code => l_mol_rec.reference_type_code
2492 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2493 ,p_revision => l_mol_rec.revision
2494 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2495 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2496 ,p_transfer_quantity => l_tmp_mmtt_rec.transaction_quantity
2497 ,p_transfer_uom_code => l_tmp_mmtt_rec.transaction_uom
2498 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2499 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2500 ,p_original_rti_id => l_old_intf_id
2501 ,p_original_temp_id => NULL
2502 ,p_lot_number => l_mol_rec.lot_number
2503 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
2504 ,p_transfer_lpn_id => NULL
2505 -- OPM Convergance
2506 ,p_sec_transfer_quantity => l_tmp_mmtt_rec.secondary_transaction_quantity
2507 ,p_sec_transfer_uom_code => l_tmp_mmtt_rec.secondary_uom_code
2508 -- OPM Convergance
2509 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2510 ,p_inspection_status => l_mol_rec.inspection_status
2511 ,p_shipment_header_id => l_shipment_header_id -- added for bug 14269755
2512 );
2513 IF (x_return_status <> g_ret_sts_success) THEN
2514 IF (l_debug = 1) THEN
2515 print_debug('PACK_UNPACK_SPLIT: ERROR - Match_transfer_rcvtxn_rec FAIL',
2516 9);
2517 END IF;
2518 RAISE fnd_api.g_exc_error;
2519 END IF;
2520
2521 END IF; -- End remaining qty check
2522
2523 l_progress := '6.2.12';
2524
2525 -- Set flag to call to TM
2526 l_call_rm := TRUE;
2527
2528 IF (l_debug = 1) THEN
2529 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') xfer api sucessfully returns');
2530 END IF;
2531 END IF;
2532 ELSIF (l_tmp_mmtt_rec.txn_type = 'ITEM_SPLIT') THEN
2533 l_progress := '7.0';
2534
2535 IF (l_debug = 1) THEN
2536 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Item Split');
2537 END IF;
2538
2539 BEGIN
2540 SELECT 1
2541 INTO l_xfer_lpn_loaded
2542 FROM dual
2543 WHERE EXISTS (SELECT /*+ INDEX (wdt, WMS_DISPATCHED_TASKS_U2) */ 'LOADED' --Bug#8555935 Changed index WMS_DISPATCHED_TASKS_N2 to WMS_DISPATCHED_TASKS_U2
2544 FROM mtl_material_transactions_temp mmtt,
2545 wms_dispatched_tasks wdt
2546 WHERE mmtt.organization_id = l_tmp_mmtt_rec.organization_id
2547 AND mmtt.transaction_temp_id = wdt.transaction_temp_id
2548 AND wdt.organization_id = l_tmp_mmtt_rec.organization_id
2549 AND wdt.task_type = 2
2550 AND wdt.status = 4
2551 AND mmtt.lpn_id IN (SELECT lpn_id
2552 FROM wms_license_plate_numbers
2553 START WITH lpn_id = l_tmp_mmtt_rec.transfer_lpn_id
2554 CONNECT BY PRIOR lpn_id = parent_lpn_id
2555 )
2556 );
2557 EXCEPTION
2558 WHEN OTHERS THEN
2559 l_xfer_lpn_loaded := 0;
2560 END;
2561
2562 IF (l_debug = 1) THEN
2563 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Item Split. ');
2564 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') LOADED? ' ||
2565 l_xfer_lpn_loaded);
2566 END IF;
2567
2568 -- Only need to abort the MMTT if the transfer LPN is not
2569 -- loaded. Since if it is loaded, then it means that it
2570 -- is being moved around. This is made for Item Load scenarios.
2571 -- As for the packing workbench, the transfer LPN should never
2572 -- be loaded. So it should always abort the MMTTs
2573 -- So, the conclusion is, if the LPN is loaded, then treat it
2574 -- as having no sub/loc changed
2575 IF (l_tmp_mmtt_rec.sub_loc_changed = 1 AND l_xfer_lpn_loaded = 0) THEN
2576 l_progress := '7.1.0';
2577 IF (l_debug = 1) THEN
2578 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
2579 END IF;
2580
2581 l_mo_splt_tb(1).prim_qty := l_tmp_mmtt_rec.primary_quantity;
2582
2583 IF (l_debug = 1) THEN
2584 print_debug('PACK_UNPACK_SPLIT: Calling split_mo(');
2585 print_debug(' p_orig_mol_id => ' || l_tmp_mmtt_rec.move_order_line_id);
2586 print_debug(' p_mo_splt_tb(1).prim_qty => ' || l_mo_splt_tb(1).prim_qty);
2587 END IF;
2588
2589 inv_rcv_integration_apis.split_mo
2590 (p_orig_mol_id => l_tmp_mmtt_rec.move_order_line_id
2591 ,p_mo_splt_tb => l_mo_splt_tb
2592 ,x_return_status => x_return_status
2593 ,x_msg_count => x_msg_count
2594 ,x_msg_data => x_msg_data
2595 );
2596 IF (x_return_status <> g_ret_sts_success) THEN
2597 IF (l_debug = 1) THEN
2598 print_debug('PACK_UNPACK_SPLIT: ERROR - split_mo FAIL');
2599 END IF;
2600 fnd_message.set_name('INV', 'INV_SPLIT_MO_ERR');
2601 fnd_msg_pub.ADD;
2602 RAISE fnd_api.g_exc_error;
2603 END IF;
2604
2605 IF (l_tmp_mmtt_rec.move_order_line_id <> l_mo_splt_tb(1).line_id) THEN
2606 IF (l_debug = 1) THEN
2607 print_debug('PACK_UNPACK_SPLIT: Unmarking wms_process_flag FOR line: '
2608 ||l_tmp_mmtt_rec.move_order_line_id);
2609 END IF;
2610
2611 --Update the MOL with unused qty to 1, because the TM will only
2612 --update wms_process_flag for the marked lines and not all
2613 --lines in a LPN as done in 11.5.10
2614 UPDATE mtl_txn_request_lines
2615 SET wms_process_flag = 1
2616 WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
2617 END IF;
2618
2619 l_progress := '7.1.1';
2620
2621 FOR i IN 1 .. l_mo_splt_tb.COUNT LOOP
2622 IF (l_debug = 1) THEN
2623 print_debug('PACK_UNPACK_SPLIT: split_mo created mo:'||l_mo_splt_tb(i).line_id);
2624 END IF;
2625 l_out_mo_splt_tb(l_out_mo_splt_tb.COUNT+i).line_id
2626 := l_mo_splt_tb(i).line_id;
2627 END LOOP;
2628
2629 l_progress := '7.1.2';
2630
2631 IF (l_debug = 1) THEN
2632 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') split_mo returns sucessfully. Calling abort_mmtts');
2633 END IF;
2634
2635
2636 abort_mmtts(p_move_order_line_id => l_mo_splt_tb(1).line_id
2637 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2638 ,x_return_status => x_return_status
2639 ,x_msg_count => x_msg_count
2640 ,x_msg_data => x_msg_data
2641 );
2642 IF (x_return_status <> g_ret_sts_success) THEN
2643 IF (l_debug = 1) THEN
2644 print_debug('PACK_UNPACK_SPLIT: ERROR - abort_mmtts FAIL');
2645 END IF;
2646 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
2647 fnd_msg_pub.ADD;
2648 RAISE fnd_api.g_exc_error;
2649 END IF;
2650
2651 l_progress := '7.1.3';
2652
2653 IF (l_debug = 1) THEN
2654 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') abort_mmtts returns sucessfully. Calling insert_lot_serial');
2655 END IF;
2656
2657 l_old_intf_id :=
2658 insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
2659 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2660 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2661 ,x_return_status => x_return_status
2662 ,x_msg_count => x_msg_count
2663 ,x_msg_data => x_msg_data
2664 );
2665 IF (x_return_status <> g_ret_sts_success) THEN
2666 IF (l_debug = 1) THEN
2667 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_lot_serial FAIL', 9);
2668 END IF;
2669 RAISE fnd_api.g_exc_error;
2670 END IF;
2671
2672 l_progress := '7.1.4';
2673 IF (l_debug = 1) THEN
2674 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') insert_lot_serial returns sucessfully.');
2675 END IF;
2676
2677 /* Retrieve MOL */
2678
2679 OPEN mol_cur(l_tmp_mmtt_rec.move_order_line_id);
2680 FETCH mol_cur INTO l_mol_rec;
2681 CLOSE mol_cur;
2682
2683 l_progress := '7.1.5';
2684 IF (l_debug = 1) THEN
2685 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2686 ') MOL_CUR fetched sucessfully. Calling xfer api');
2687 END IF;
2688
2689 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2690 (x_return_status => x_return_status
2691 ,x_msg_count => x_msg_count
2692 ,x_msg_data => x_msg_data
2693 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2694 ,p_parent_txn_id => l_mol_rec.txn_source_id
2695 ,p_reference_id => l_mol_rec.reference_id
2696 ,p_reference => l_mol_rec.reference
2697 ,p_reference_type_code => l_mol_rec.reference_type_code
2698 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2699 ,p_revision => l_mol_rec.revision
2700 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2701 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2702 ,p_transfer_quantity => l_tmp_mmtt_rec.transaction_quantity
2703 ,p_transfer_uom_code => l_tmp_mmtt_rec.transaction_uom
2704 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2705 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2706 ,p_original_rti_id => l_old_intf_id
2707 ,p_original_temp_id => NULL
2708 ,p_lot_number => l_mol_rec.lot_number
2709 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
2710 ,p_transfer_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
2711 -- OPM Convergance
2712 ,p_sec_transfer_quantity => l_tmp_mmtt_rec.secondary_transaction_quantity
2713 ,p_sec_transfer_uom_code => l_tmp_mmtt_rec.secondary_uom_code
2714 -- OPM Convergance
2715 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2716 ,p_inspection_status => l_mol_rec.inspection_status
2717 ,p_shipment_header_id => l_shipment_header_id -- added for bug 14269755
2718 );
2719 IF (x_return_status <> g_ret_sts_success) THEN
2720 IF (l_debug = 1) THEN
2721 print_debug('PACK_UNPACK_SPLIT: ERROR - Match_transfer_rcvtxn_rec FAIL');
2722 END IF;
2723 RAISE fnd_api.g_exc_error;
2724 END IF;
2725
2726 l_progress := '7.1.6';
2727
2728 -- Set flag to call to TM
2729 l_call_rm := TRUE;
2730
2731 IF (l_debug = 1) THEN
2732 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2733 ') xfer_api returns sucessfully. ');
2734 END IF;
2735
2736 ELSE -- no sub/loc changed or LPN is loaded
2737 l_progress := '7.2.0';
2738 IF (l_debug = 1) THEN
2739 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2740 ') Sub/Loc not changed OR LPN IS LOADED');
2741 END IF;
2742
2743 l_mo_splt_tb(1).prim_qty := l_tmp_mmtt_rec.primary_quantity;
2744
2745 l_progress := '7.2.1';
2746 IF (l_debug = 1) THEN
2747 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2748 ') Calling split_mo');
2749 END IF;
2750
2751 inv_rcv_integration_apis.split_mo
2752 (p_orig_mol_id => l_tmp_mmtt_rec.move_order_line_id
2753 ,p_mo_splt_tb => l_mo_splt_tb
2754 ,x_return_status => x_return_status
2755 ,x_msg_count => x_msg_count
2756 ,x_msg_data => x_msg_data
2757 );
2758 IF (x_return_status <> g_ret_sts_success) THEN
2759 IF (l_debug = 1) THEN
2760 print_debug('PACK_UNPACK_SPLIT: ERROR - split_mo FAIL');
2761 END IF;
2762 fnd_message.set_name('INV', 'INV_SPLIT_MO_ERR');
2763 fnd_msg_pub.ADD;
2764 RAISE fnd_api.g_exc_error;
2765 END IF;
2766
2767 IF (l_tmp_mmtt_rec.move_order_line_id <> l_mo_splt_tb(1).line_id) THEN
2768 IF (l_debug = 1) THEN
2769 print_debug('PACK_UNPACK_SPLIT: Unmarking wms_process_flag FOR line: '
2770 ||l_tmp_mmtt_rec.move_order_line_id);
2771 END IF;
2772
2773 --Update the MOL with unused qty to 1, because the TM will only
2774 --update wms_process_flag for the marked lines and not all
2775 --lines in a LPN as done in 11.5.10
2776 UPDATE mtl_txn_request_lines
2777 SET wms_process_flag = 1
2778 WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
2779 END IF;
2780
2781 l_progress := '7.2.2';
2782 FOR i IN 1 .. l_mo_splt_tb.COUNT LOOP
2783 l_out_mo_splt_tb(l_out_mo_splt_tb.COUNT+i).line_id
2784 := l_mo_splt_tb(i).line_id;
2785 END LOOP;
2786
2787 l_progress := '7.2.3';
2788 IF (l_debug = 1) THEN
2789 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2790 ') Calling insert_lot_serial');
2791 END IF;
2792
2793 l_old_intf_id :=
2794 insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
2795 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2796 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2797 ,x_return_status => x_return_status
2798 ,x_msg_count => x_msg_count
2799 ,x_msg_data => x_msg_data
2800 );
2801 IF (x_return_status <> g_ret_sts_success) THEN
2802 IF (l_debug = 1) THEN
2803 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_lot_serail FAIL');
2804 END IF;
2805 RAISE fnd_api.g_exc_error;
2806 END IF;
2807
2808 l_progress := '7.2.4';
2809 OPEN mol_cur(l_tmp_mmtt_rec.move_order_line_id);
2810 FETCH mol_cur INTO l_mol_rec;
2811 CLOSE mol_cur;
2812
2813 l_progress := '7.2.5';
2814 IF (l_debug = 1) THEN
2815 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2816 ') Start to loop mmtt' );
2817 print_debug(' line_id = ' || l_mol_rec.line_id);
2818 print_debug(' reference_id = ' ||l_mol_rec.reference_id);
2819 print_debug(' reference = '||l_mol_rec.reference);
2820 print_debug(' reference_type_code = '||l_mol_rec.reference_type_code);
2821
2822 END IF;
2823
2824
2825 -- Look at each new mmtt, and insert rti for each of them
2826 -- Bug 5231114: Added the condition on transaction_source_type_id and
2827 -- transaction_action_id for the following combinations:13/12 and 4/27
2828 FOR l_mmtt_rec IN
2829 ( SELECT
2830 transaction_temp_id
2831 ,primary_quantity
2832 ,transaction_quantity
2833 ,transaction_uom
2834 ,secondary_transaction_quantity
2835 ,secondary_uom_code
2836 FROM
2837 mtl_material_transactions_temp
2838 WHERE
2839 ( move_order_line_id = l_mo_splt_tb(1).line_id AND
2840 ( ( transaction_source_type_id = 1 AND
2841 transaction_action_id = 27) OR
2842 ( transaction_source_type_id = 7 AND
2843 transaction_action_id = 12) OR
2844 ( transaction_source_type_id = 12 AND
2845 transaction_action_id = 27) OR
2846 ( transaction_source_type_id = 13 AND
2847 transaction_action_id = 12) OR
2848 ( transaction_source_type_id = 4 AND
2849 transaction_action_id = 27))) )
2850 LOOP
2851 l_progress := '7.2.6';
2852 IF (l_debug = 1) THEN
2853 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2854 ') Looking at MMTT:'
2855 ||l_mmtt_rec.transaction_temp_id);
2856 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2857 ') l_tmp_mmtt_rec.primary_quantity: '||
2858 l_tmp_mmtt_rec.primary_quantity);
2859 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2860 ') l_mmtt_rec.primary_quantity: '||
2861 l_mmtt_rec.primary_quantity);
2862 END IF;
2863
2864 -- keep track of remaining quantity
2865 l_tmp_mmtt_rec.primary_quantity :=
2866 l_tmp_mmtt_rec.primary_quantity -
2867 l_mmtt_rec.primary_quantity;
2868
2869
2870 /* Create a dummy RT ID */
2871 SELECT rcv_transactions_interface_s.NEXTVAL
2872 INTO l_new_intf_id
2873 FROM dual;
2874 l_progress := '7.2.7';
2875
2876 /* Split lot serial */
2877 l_rti_tb.DELETE;
2878
2879 l_rti_tb(1).orig_interface_trx_id := l_old_intf_id;
2880 l_rti_tb(1).new_interface_trx_id := l_new_intf_id;
2881 IF (l_tmp_mmtt_rec.transaction_uom <> l_mmtt_rec.transaction_uom) THEN
2882 l_rti_tb(1).quantity := inv_rcv_cache.convert_qty
2883 (p_inventory_item_id => l_tmp_mmtt_rec.inventory_item_id
2884 ,p_from_qty => l_mmtt_rec.transaction_quantity
2885 ,p_from_uom_code => l_mmtt_rec.transaction_uom
2886 ,p_to_uom_code => l_tmp_mmtt_rec.transaction_uom);
2887 ELSE
2888 l_rti_tb(1).quantity := l_mmtt_rec.transaction_quantity;
2889 END IF;
2890
2891 l_rti_tb(1).to_organization_id := l_tmp_mmtt_rec.organization_id;
2892 l_rti_tb(1).item_id := l_tmp_mmtt_rec.inventory_item_id;
2893 l_rti_tb(1).uom_code := l_tmp_mmtt_rec.transaction_uom;
2894
2895 IF (l_tmp_mmtt_rec.primary_quantity > 0) THEN
2896 l_rti_tb(2).orig_interface_trx_id := l_old_intf_id;
2897 l_rti_tb(2).new_interface_trx_id := l_old_intf_id;
2898 l_rti_tb(2).quantity := l_tmp_mmtt_rec.transaction_quantity;
2899 l_rti_tb(2).to_organization_id := l_tmp_mmtt_rec.organization_id;
2900 l_rti_tb(2).item_id := l_tmp_mmtt_rec.inventory_item_id;
2901 l_rti_tb(2).uom_code := l_tmp_mmtt_rec.transaction_uom;
2902 END IF;
2903
2904 inv_rcv_integration_pvt.split_lot_serial
2905 (p_rti_tb => l_rti_tb
2906 ,x_return_status => x_return_status
2907 ,x_msg_count => x_msg_count
2908 ,x_msg_data => x_msg_data
2909 );
2910 IF (x_return_status <> g_ret_sts_success) THEN
2911 IF (l_debug = 1) THEN
2912 print_debug('PACK_UNPACK_SPLIT: ERROR - split_lot_serial FAIL', 9);
2913 END IF;
2914 RAISE fnd_api.g_exc_error;
2915 END IF;
2916 l_progress := '7.2.8';
2917
2918 IF l_mmtt_rec.transaction_uom <> l_tmp_mmtt_rec.transaction_uom THEN
2919 l_qty_to_insert := inv_rcv_cache.convert_qty
2920 (p_inventory_item_id => l_tmp_mmtt_rec.inventory_item_id
2921 ,p_from_qty => l_mmtt_rec.transaction_quantity
2922 ,p_from_uom_code => l_mmtt_rec.transaction_uom
2923 ,p_to_uom_code => l_tmp_mmtt_rec.transaction_uom);
2924 l_uom_to_insert := l_tmp_mmtt_rec.transaction_uom ;
2925 ELSE
2926 l_uom_to_insert := l_mmtt_rec.transaction_uom;
2927 l_qty_to_insert := l_mmtt_rec.transaction_quantity;
2928 END IF;
2929
2930 IF (l_debug = 1) THEN
2931 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2932 ') split_lot_serial returns sucessfully. calling xfer api');
2933 END IF;
2934
2935 /* Call transfer API with the new RT ID */
2936 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2937 ( x_return_status => x_return_status
2938 ,x_msg_count => x_msg_count
2939 ,x_msg_data => x_msg_data
2940 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2941 ,p_parent_txn_id => l_mol_rec.txn_source_id
2942 ,p_reference_id => l_mol_rec.reference_id
2943 ,p_reference => l_mol_rec.reference
2944 ,p_reference_type_code => l_mol_rec.reference_type_code
2945 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2946 ,p_revision => l_mol_rec.revision
2947 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2948 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2949 ,p_transfer_quantity => l_qty_to_insert
2950 ,p_transfer_uom_code => l_uom_to_insert
2951 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2952 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2953 ,p_original_rti_id => l_new_intf_id
2954 ,p_original_temp_id => l_mmtt_rec.transaction_temp_id
2955 ,p_lot_number => l_mol_rec.lot_number
2956 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
2957 ,p_transfer_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
2958 -- OPM Convergance
2959 ,p_sec_transfer_quantity => l_mmtt_rec.secondary_transaction_quantity
2960 ,p_sec_transfer_uom_code => l_mmtt_rec.secondary_uom_code
2961 -- OPM Convergance
2962 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2963 ,p_inspection_status => l_mol_rec.inspection_status
2964 ,p_shipment_header_id => l_shipment_header_id -- added for bug 14269755
2965 );
2966 IF (x_return_status <> g_ret_sts_success) THEN
2967 IF (l_debug = 1) THEN
2968 print_debug('PACK_UNPACK_SPLIT: ERROR - Match_transfer_rcvtxn_rec FAIL',
2969 9);
2970 END IF;
2971 RAISE fnd_api.g_exc_error;
2972 END IF;
2973 l_progress := '7.2.8';
2974
2975 END LOOP;
2976
2977 l_progress := '7.2.9';
2978 IF (l_debug = 1) THEN
2979 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2980 ') Loop exited. Looking at remaining quantities');
2981 END IF;
2982
2983 IF (l_tmp_mmtt_rec.primary_quantity > 0) THEN
2984 -- Call transfer API for the remaining qty (not detailed)
2985 -- with the old RT ID */
2986 l_progress := '7.2.10';
2987 IF (l_debug = 1) THEN
2988 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2989 ') There are remaining quantities. Calling xfer api');
2990 END IF;
2991
2992 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2993 ( x_return_status => x_return_status
2994 ,x_msg_count => x_msg_count
2995 ,x_msg_data => x_msg_data
2996 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2997 ,p_parent_txn_id => l_mol_rec.txn_source_id
2998 ,p_reference_id => l_mol_rec.reference_id
2999 ,p_reference => l_mol_rec.reference
3000 ,p_reference_type_code => l_mol_rec.reference_type_code
3001 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
3002 ,p_revision => l_mol_rec.revision
3003 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
3004 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
3005 ,p_transfer_quantity => l_tmp_mmtt_rec.transaction_quantity
3006 ,p_transfer_uom_code => l_tmp_mmtt_rec.transaction_uom
3007 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
3008 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
3009 ,p_original_rti_id => l_old_intf_id
3010 ,p_original_temp_id => NULL
3011 ,p_lot_number => l_mol_rec.lot_number --??
3012 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id --??
3013 ,p_transfer_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
3014 -- OPM Convergance
3015 ,p_sec_transfer_quantity => l_tmp_mmtt_rec.secondary_transaction_quantity
3016 ,p_sec_transfer_uom_code => l_tmp_mmtt_rec.secondary_uom_code
3017 -- OPM Convergance
3018 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
3019 ,p_inspection_status => l_mol_rec.inspection_status
3020 ,p_shipment_header_id => l_shipment_header_id -- added for bug 14269755
3021 );
3022 IF (x_return_status <> g_ret_sts_success) THEN
3023 IF (l_debug = 1) THEN
3024 print_debug('PAC_UNPACK_SPLIT: ERROR - 1 Match_transfer_rcvtxn_rec FAIL');
3025 END IF;
3026 RAISE fnd_api.g_exc_error;
3027 END IF;
3028 END IF;
3029
3030 l_progress := '7.2.11';
3031 -- Set flag to call to TM
3032 l_call_rm := TRUE;
3033
3034 IF (l_debug = 1) THEN
3035 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
3036 ') xfer_api sucessfully returns');
3037 END IF;
3038 END IF; -- End sub/loc test
3039 ELSE
3040 fnd_message.set_name('INV', 'INV_INVALID_TXN_TYPE');
3041 fnd_msg_pub.ADD;
3042 RAISE fnd_api.g_exc_error;
3043 END IF; -- End txn_type test
3044
3045
3046 END LOOP; -- End tmp_mmtt_cur loop
3047
3048 IF tmp_mmtt_cur_by_hdr_id%isopen THEN
3049 CLOSE tmp_mmtt_cur_by_hdr_id;
3050 END IF;
3051
3052 IF tmp_mmtt_cur_by_txn_id%isopen THEN
3053 CLOSE tmp_mmtt_cur_by_txn_id;
3054 END IF;
3055
3056 -- Delete temp MMTTs
3057 IF (l_debug = 1) THEN
3058 print_debug('Deleting temp MMTTs');
3059 END IF;
3060
3061 BEGIN
3062 forall i IN 1 .. l_mmtts_count
3063 DELETE FROM mtl_serial_numbers_temp
3064 WHERE transaction_temp_id = l_mmtt_ids(i)
3065 OR transaction_temp_id IN
3066 (SELECT mtlt.serial_transaction_temp_id
3067 FROM mtl_transaction_lots_temp mtlt
3068 WHERE mtlt.transaction_temp_id = l_mmtt_ids(i));
3069 IF (l_debug = 1) THEN
3070 print_debug('# OF MSNT DELETED: ' || SQL%rowcount);
3071 END IF;
3072 EXCEPTION
3073 WHEN OTHERS THEN
3074 IF (l_debug = 1) THEN
3075 print_debug('Error deleting temp MSNTs');
3076 END IF;
3077 RAISE fnd_api.g_exc_error;
3078 END;
3079
3080 BEGIN
3081 forall i IN 1 .. l_mmtts_count
3082 DELETE FROM mtl_transaction_lots_temp
3083 WHERE transaction_temp_id = l_mmtt_ids(i);
3084 IF (l_debug = 1) THEN
3085 print_debug('# OF MTLT DELETED: ' || SQL%rowcount);
3086 END IF;
3087 EXCEPTION
3088 WHEN OTHERS THEN
3089 IF (l_debug = 1) THEN
3090 print_debug('Error deleting temp MTLTs');
3091 END IF;
3092 RAISE fnd_api.g_exc_error;
3093 END;
3094
3095 BEGIN
3096 forall i IN 1 .. l_mmtts_count
3097 DELETE FROM mtl_material_transactions_temp
3098 WHERE transaction_temp_id = l_mmtt_ids(i);
3099 IF (l_debug = 1) THEN
3100 print_debug('# OF MMTT DELETED: ' || SQL%rowcount);
3101 END IF;
3102 EXCEPTION
3103 WHEN OTHERS THEN
3104 IF (l_debug =1 )THEN
3105 print_debug('Error deleting temp MMTTs');
3106 END IF;
3107 RAISE fnd_api.g_exc_error;
3108 END;
3109
3110 --Added for bug 14269755
3111 BEGIN
3112 DELETE FROM mtl_transaction_lots_interface
3113 WHERE product_transaction_id=l_old_intf_id;
3114
3115 DELETE FROM mtl_serial_numbers_interface
3116 WHERE product_transaction_id=l_old_intf_id;
3117
3118 EXCEPTION
3119 WHEN No_Data_Found THEN
3120 IF (l_debug =1 )THEN
3121 print_debug('Error deleting temp MTLTs');
3122 END IF;
3123 END;
3124
3125 COMMIT; --Need to commit the delete
3126
3127 IF (l_call_rm = TRUE AND p_call_rcv_tm = fnd_api.g_true) THEN
3128 IF (l_debug = 1) THEN
3129 print_debug('Calling Receiving TM');
3130 END IF;
3131
3132 l_txn_mode_code := inv_rcv_common_apis.g_po_startup_value.transaction_mode;
3133
3134 inv_rcv_common_apis.g_po_startup_value.transaction_mode
3135 := p_txn_mode_code;
3136
3137 -- Need to pass in group ID?
3138 inv_rcv_mobile_process_txn.rcv_process_receive_txn
3139 (x_return_status => x_return_status
3140 ,x_msg_data => x_msg_data
3141 );
3142 IF (x_return_status <> g_ret_sts_success) THEN
3143 IF (l_debug = 1) THEN
3144 print_debug('PACK_UNPACK_SPLIT: Error - Rcv TM Failed');
3145 END IF;
3146
3147 inv_rcv_common_apis.g_po_startup_value.transaction_mode
3148 := l_txn_mode_code;
3149 fnd_message.set_name('WMS', 'WMS_TD_TXNMGR_ERROR');
3150 fnd_msg_pub.ADD;
3151 x_return_status := g_ret_sts_unexp_err ;
3152 fnd_msg_pub.count_and_get
3153 ( p_count => x_msg_count
3154 , p_data => x_msg_data
3155 );
3156 RETURN;
3157 END IF;
3158
3159 inv_rcv_common_apis.g_po_startup_value.transaction_mode
3160 := l_txn_mode_code;
3161 -- Need to clear in exception handling too?
3162 inv_rcv_common_apis.rcv_clear_global;
3163 END IF;
3164
3165 x_mo_lines_tb := l_out_mo_splt_tb;
3166
3167 EXCEPTION
3168 WHEN fnd_api.g_exc_unexpected_error THEN
3169 IF (l_debug = 1) THEN
3170 print_debug('PACK_UNPACK_SPLIT: Unexpected Exception occured after progress: '
3171 || l_progress);
3172 END IF;
3173
3174 IF tmp_mmtt_cur_by_hdr_id%isopen THEN
3175 CLOSE tmp_mmtt_cur_by_hdr_id;
3176 END IF;
3177
3178 IF tmp_mmtt_cur_by_txn_id%isopen THEN
3179 CLOSE tmp_mmtt_cur_by_txn_id;
3180 END IF;
3181
3182 x_return_status := g_ret_sts_unexp_err ;
3183 fnd_msg_pub.count_and_get
3184 ( p_count => x_msg_count
3185 , p_data => x_msg_data
3186 );
3187 ROLLBACK TO pack_unpack_split_pub;
3188 WHEN OTHERS THEN
3189 IF (l_debug = 1) THEN
3190 print_debug('PACK_UNPACK_SPLIT: Other Exception occured after progress: '
3191 || l_progress|| SQLERRM );
3192 END IF;
3193
3194 IF tmp_mmtt_cur_by_hdr_id%isopen THEN
3195 CLOSE tmp_mmtt_cur_by_hdr_id;
3196 END IF;
3197
3198 IF tmp_mmtt_cur_by_txn_id%isopen THEN
3199 CLOSE tmp_mmtt_cur_by_txn_id;
3200 END IF;
3201
3202 x_return_status := g_ret_sts_unexp_err;
3203 fnd_msg_pub.count_and_get
3204 ( p_count => x_msg_count
3205 , p_data => x_msg_data
3206 );
3207 ROLLBACK TO pack_unpack_split_pub;
3208 END pack_unpack_split;
3209
3210
3211 END wms_rcv_pup_pvt;
3212