[Home] [Help]
PACKAGE BODY: APPS.WMS_RCV_PUP_PVT
Source
1 PACKAGE BODY wms_rcv_pup_pvt AS
2 /* $Header: WMSRCVPB.pls 120.7 2006/07/27 06:43:54 anviswan 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.7 $)',
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_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1095 -- x_msg_count NUMBER;
1096 -- x_msg_data VARCHAR2(2400);
1097
1098 BEGIN
1099
1100 SAVEPOINT pack_unpack_split_pub;
1101
1102 IF (l_debug = 1) THEN
1103 print_debug('PACK_UNPACK_SPLIT: Entering...');
1104 print_debug(' p_transaction_temp_id => '||p_transaction_temp_id);
1105 print_debug(' p_header_id => '||p_header_id);
1106 print_debug(' p_call_rcv_tm => '||p_call_rcv_tm);
1107 END IF;
1108
1109 x_return_status := g_ret_sts_success;
1110
1111 IF (p_transaction_temp_id IS NOT NULL) THEN
1112 OPEN tmp_mmtt_cur_by_txn_id(p_transaction_temp_id);
1113 ELSIF (p_header_id IS NOT NULL) THEN
1114 OPEN tmp_mmtt_cur_by_hdr_id(p_header_id);
1115 ELSE
1116 l_progress := '0.0.1';
1117 print_debug('PACK_UNPACK_SPLIT: Invalid parameter passed to API!');
1118 RAISE fnd_api.g_exc_error;
1119 END IF;
1120
1121 LOOP
1122 IF (p_transaction_temp_id IS NOT NULL) THEN
1123 FETCH tmp_mmtt_cur_by_txn_id INTO l_tmp_mmtt_rec;
1124 EXIT WHEN tmp_mmtt_cur_by_txn_id%notfound;
1125 ELSE
1126 FETCH tmp_mmtt_cur_by_hdr_id INTO l_tmp_mmtt_rec;
1127 EXIT WHEN tmp_mmtt_cur_by_hdr_id%notfound;
1128 END IF;
1129
1130 l_progress := '0.1';
1131 IF (l_debug = 1) THEN
1132 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Temp MMTT FETCHED');
1133 print_debug(' l_tmp_mmtt_rec.transaction_temp_id: '||l_tmp_mmtt_rec.transaction_temp_id);
1134 print_debug(' l_tmp_mmtt_rec.organization_id : '||l_tmp_mmtt_rec.organization_id);
1135 print_debug(' l_tmp_mmtt_rec.transfer_organization_id:: '||l_tmp_mmtt_rec.transfer_organization);
1136 print_debug(' l_tmp_mmtt_rec.inventory_item_id : '||l_tmp_mmtt_rec.inventory_item_id);
1137 print_debug(' l_tmp_mmtt_rec.lpn_id : '||l_tmp_mmtt_rec.lpn_id);
1138 print_debug(' l_tmp_mmtt_rec.content_lpn_id : '||l_tmp_mmtt_rec.content_lpn_id);
1139 print_debug(' l_tmp_mmtt_rec.transfer_lpn_id : '||l_tmp_mmtt_rec.transfer_lpn_id);
1140 print_debug(' l_tmp_mmtt_rec.subinventory_code : '||l_tmp_mmtt_rec.subinventory_code);
1141 print_debug(' l_tmp_mmtt_rec.transfer_subinventory : '||l_tmp_mmtt_rec.transfer_subinventory);
1142 print_debug(' l_tmp_mmtt_rec.locator_id : '||l_tmp_mmtt_rec.locator_id);
1143 print_debug(' l_tmp_mmtt_rec.transfer_to_loc_id : '||l_tmp_mmtt_rec.transfer_to_location);
1144 print_debug(' l_tmp_mmtt_rec.move_order_line_id : '||l_tmp_mmtt_rec.move_order_line_id);
1145 print_debug(' l_tmp_mmtt_rec.transaction_quantity: '||l_tmp_mmtt_rec.transaction_quantity);
1146 print_debug(' l_tmp_mmtt_rec.primary_quantity : '||l_tmp_mmtt_rec.primary_quantity);
1147 print_debug(' l_tmp_mmtt_rec.txn_type : '||l_tmp_mmtt_rec.txn_type);
1148 print_debug(' l_tmp_mmtt_rec.sub_loc_changed : '||l_tmp_mmtt_rec.sub_loc_changed);
1149 -- OPM Convergance
1150 print_debug(' l_tmp_mmtt_rec.secondary_transaction_quantity: '||l_tmp_mmtt_rec.secondary_transaction_quantity);
1151 print_debug(' l_tmp_mmtt_rec.secondary_uom_code: '||l_tmp_mmtt_rec.secondary_uom_code);
1152 -- OPM Convergance
1153 END IF;
1154
1155 -- Initialize loop variables
1156 l_mo_splt_tb.DELETE;
1157
1158 -- Set up array for bulk delete later after the loop
1159 l_mmtts_count := l_mmtts_count + 1;
1160 l_mmtt_ids(l_mmtts_count) := l_tmp_mmtt_rec.transaction_temp_id;
1161
1162 l_progress := '0.2';
1163
1164 IF (l_tmp_mmtt_rec.txn_type = 'LPN_MOVE') THEN
1165 l_progress := '1.0';
1166 IF (l_debug = 1) THEN
1167 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') LPN MOVE');
1168 END IF;
1169
1170 IF (l_tmp_mmtt_rec.sub_loc_changed = 1) THEN
1171 l_progress := '1.1.0';
1172 IF (l_debug = 1) THEN
1173 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
1174 END IF;
1175
1176 abort_mmtts(p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1177 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1178 ,x_return_status => x_return_status
1179 ,x_msg_count => x_msg_count
1180 ,x_msg_data => x_msg_data
1181 );
1182 IF (x_return_status <> g_ret_sts_success) THEN
1183 IF (l_debug = 1) THEN
1184 print_debug('PACK_UNPACK_SPLIT: abort_mmtts failed');
1185 END IF;
1186 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1187 fnd_msg_pub.ADD;
1188 RAISE fnd_api.g_exc_error;
1189 END IF;
1190 l_progress := '1.1.1';
1191
1192 IF (l_debug = 1) THEN
1193 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') MMTTs successfully aborted');
1194 END IF;
1195
1196 IF (l_first_time <> 1) THEN
1197 l_first_time := 1;
1198 END IF;
1199
1200 l_group_id :=
1201 wms_putaway_utils.insert_rti
1202 (p_from_org => l_tmp_mmtt_rec.organization_id
1203 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1204 ,p_to_org => l_tmp_mmtt_rec.organization_id
1205 ,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
1206 ,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
1207 ,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1208 ,p_first_time => l_first_time
1209 ,p_mobile_txn => 'Y'
1210 ,p_txn_mode_code => p_txn_mode_code
1211 ,x_return_status => x_return_status
1212 ,x_msg_count => x_msg_count
1213 ,x_msg_data => x_msg_data
1214 );
1215 IF (x_return_status <> g_ret_sts_success) THEN
1216 IF (l_debug = 1) THEN
1217 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_rti Fail');
1218 END IF;
1219 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
1220 fnd_msg_pub.ADD;
1221 RAISE fnd_api.g_exc_error;
1222 END IF;
1223
1224 l_progress := '1.1.2';
1225
1226 IF (l_debug = 1) THEN
1227 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') RTI inserted');
1228 print_debug(' Calling insert_wlpni(');
1229 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1230 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1231 print_debug(' p_license_plate_number => ');
1232 print_debug(' p_lpn_group_id => '|| l_group_id ||')');
1233 END IF;
1234
1235 inv_rcv_integration_apis.insert_wlpni
1236 (p_api_version => 1.0
1237 ,x_return_status => x_return_status
1238 ,x_msg_count => x_msg_count
1239 ,x_msg_data => x_msg_data
1240 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1241 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1242 ,p_license_plate_number => NULL
1243 ,p_lpn_group_id => l_group_id
1244 ,p_parent_lpn_id => NULL
1245 );
1246 IF (x_return_status <> g_ret_sts_success) THEN
1247 IF (l_debug = 1) THEN
1248 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_wlpni Fail');
1249 END IF;
1250 RAISE fnd_api.g_exc_error;
1251 END IF;
1252
1253 l_progress := '1.1.3';
1254 IF (l_debug = 1) THEN
1255 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNI successfully inserted');
1256 END IF;
1257
1258 -- Signal call to TM
1259 l_call_rm := TRUE;
1260 ELSE
1261 l_progress := '1.2.0';
1262 IF (l_debug = 1) THEN
1263 print_debug('PACK_UNPACK_SPLIT: LPN Move and No location changed.' ||
1264 ' No action required, return success');
1265 END IF;
1266 RETURN;
1267 END IF;
1268 ELSIF (l_tmp_mmtt_rec.txn_type = 'LPN_PACK') THEN
1269 l_progress := '2.0';
1270 IF (l_debug = 1) THEN
1271 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') LPN PACK');
1272 END IF;
1273
1274 IF (l_tmp_mmtt_rec.sub_loc_changed = 1) THEN
1275 l_progress := '2.1.0';
1276 IF (l_debug = 1) THEN
1277 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
1278 END IF;
1279
1280 abort_mmtts
1281 (p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1282 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1283 ,x_return_status => x_return_status
1284 ,x_msg_count => x_msg_count
1285 ,x_msg_data => x_msg_data
1286 );
1287 IF (x_return_status <> g_ret_sts_success) THEN
1288 IF (l_debug = 1) THEN
1289 print_debug('PACK_UNPACK_SPLIT: ERROR - Abort MMTTs Fail');
1290 END IF;
1291 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1292 fnd_msg_pub.ADD;
1293 RAISE fnd_api.g_exc_error;
1294 END IF;
1295
1296 l_progress := '2.1.1';
1297 IF (l_debug = 1) THEN
1298 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') MMTTs aborted sucessfully');
1299 END IF;
1300
1301 IF (l_first_time <> 1) THEN
1302 l_first_time := 1;
1303 END IF;
1304 l_group_id :=
1305 wms_putaway_utils.insert_rti
1306 (p_from_org => l_tmp_mmtt_rec.organization_id
1307 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1308 ,p_to_org => l_tmp_mmtt_rec.organization_id
1309 ,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
1310 ,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
1311 ,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1312 ,p_first_time => l_first_time
1313 ,p_mobile_txn => 'Y'
1314 ,p_txn_mode_code => p_txn_mode_code
1315 ,x_return_status => x_return_status
1316 ,x_msg_count => x_msg_count
1317 ,x_msg_data => x_msg_data
1318 );
1319 IF (x_return_status <> g_ret_sts_success) THEN
1320 IF (l_debug = 1) THEN
1321 print_debug('ERROR: insert_rti Fail');
1322 END IF;
1323 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
1324 fnd_msg_pub.ADD;
1325 RAISE fnd_api.g_exc_error;
1326 END IF;
1327
1328 l_progress := '2.1.2';
1329
1330 IF (l_debug = 1) THEN
1331 print_debug('PACK_UNPACK_SPLIT: Calling insert_wlpni');
1332 print_debug(' (p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1333 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1334 print_debug(' p_license_plate_number => ' );
1335 print_debug(' p_lpn_group_id => ' || l_group_id);
1336 print_debug(' p_parent_lpn_id => ' || l_tmp_mmtt_rec.transfer_lpn_id);
1337 END IF;
1338
1339 inv_rcv_integration_apis.insert_wlpni
1340 (p_api_version => 1.0
1341 ,x_return_status => x_return_status
1342 ,x_msg_count => x_msg_count
1343 ,x_msg_data => x_msg_data
1344 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1345 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1346 ,p_license_plate_number => NULL
1347 ,p_lpn_group_id => l_group_id
1348 ,p_parent_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1349 );
1350
1351 IF (x_return_status <> g_ret_sts_success) THEN
1352 IF (l_debug = 1) THEN
1353 print_debug('PACK_UNPACK_SPLIT: ERROR - Insert WLPN Fail');
1354 END IF;
1355 RAISE fnd_api.g_exc_error;
1356 END IF;
1357
1358 l_progress := '2.1.3';
1359
1360 -- Set flag to call to TM
1361 l_call_rm := TRUE;
1362
1363 IF (l_debug = 1) THEN
1364 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNI inserted successfully');
1365 END IF;
1366
1367 ELSE
1368 l_progress := '2.2.0';
1369 IF (l_debug = 1) THEN
1370 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') NO Sub/Loc changed');
1371 print_debug(' Calling packunpack_container(');
1372 print_debug(' p_content_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1373 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.transfer_lpn_id);
1374 print_debug(' p_operation => '||1);
1375 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1376 END IF;
1377
1378 -- Modify LPN Status
1379 wms_container_pvt.modify_lpn_wrapper
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_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1385 ,p_subinventory => l_tmp_mmtt_rec.transfer_subinventory
1386 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
1387 ,p_lpn_context => 3 --RCV
1388 );
1389 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1390 IF (l_debug = 1) THEN
1391 print_debug('PACK_UNPACK_SPLIT: modify_lpn failed');
1392 END IF;
1393 RAISE fnd_api.g_exc_error;
1394 END IF;
1395
1396 wms_container_pvt.packunpack_container
1397 (p_api_version => 1.0
1398 ,p_content_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1399 ,p_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1400 ,p_operation => 1 /* Pack */
1401 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1402 ,x_return_status => x_return_status
1403 ,x_msg_count => x_msg_count
1404 ,x_msg_data => x_msg_data
1405 );
1406 IF (x_return_status <> g_ret_sts_success) THEN
1407 IF (l_debug = 1) THEN
1408 print_debug('PACK_UNPACK_SPLIT: ERROR - packunpack_container Fail');
1409 END IF;
1410 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_PACK_UPDATE_ERR');
1411 fnd_msg_pub.ADD;
1412 RAISE fnd_api.g_exc_error;
1413 END IF;
1414
1415
1416 l_progress := '2.2.1';
1417 IF (l_debug = 1) THEN
1418 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') packunpack_container successful');
1419 END IF;
1420
1421 END IF;
1422 ELSIF (l_tmp_mmtt_rec.txn_type = 'LPN_UNPACK') THEN
1423 l_progress := '3.0';
1424 IF (l_debug = 1) THEN
1425 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') LPN UNPACK');
1426 END IF;
1427
1428 IF (l_tmp_mmtt_rec.sub_loc_changed = 1) THEN
1429 l_progress := '3.1.0';
1430 IF (l_debug = 1) THEN
1431 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
1432 END IF;
1433
1434 abort_mmtts
1435 (p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1436 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1437 ,x_return_status => x_return_status
1438 ,x_msg_count => x_msg_count
1439 ,x_msg_data => x_msg_data
1440 );
1441 IF (x_return_status <> g_ret_sts_success) THEN
1442 IF (l_debug = 1) THEN
1443 print_debug('PACK_UNPACK_SPLIT: ERROR - abort_mmtts Fail');
1444 END IF;
1445 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1446 fnd_msg_pub.ADD;
1447 RAISE fnd_api.g_exc_error;
1448 END IF;
1449
1450 l_progress := '3.1.1';
1451 IF (l_debug = 1) THEN
1452 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') MMTTs aborted successfully');
1453 END IF;
1454
1455 IF (l_first_time <> 1) THEN
1456 l_first_time := 1;
1457 END IF;
1458 l_group_id :=
1459 wms_putaway_utils.insert_rti
1460 (p_from_org => l_tmp_mmtt_rec.organization_id
1461 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1462 ,p_to_org => l_tmp_mmtt_rec.organization_id
1463 ,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
1464 ,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
1465 ,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1466 ,p_first_time => l_first_time
1467 ,p_mobile_txn => 'Y'
1468 ,p_txn_mode_code => p_txn_mode_code
1469 ,x_return_status => x_return_status
1470 ,x_msg_count => x_msg_count
1471 ,x_msg_data => x_msg_data
1472 );
1473 IF (x_return_status <> g_ret_sts_success) THEN
1474 IF (l_debug = 1) THEN
1475 print_debug('ERROR: insert_rti Fail');
1476 END IF;
1477 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
1478 fnd_msg_pub.ADD;
1479 RAISE fnd_api.g_exc_error;
1480 END IF;
1481
1482 l_progress := '3.1.2';
1483
1484 IF (l_debug = 1) THEN
1485 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') RTI inserted successfully');
1486 END IF;
1487
1488 IF (l_debug = 1) THEN
1489 print_debug('PACK_UNPACK_SPLIT: Calling insert_wlpni');
1490 print_debug(' (p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1491 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1492 print_debug(' p_license_plate_number => ' );
1493 print_debug(' p_lpn_group_id => ' || l_group_id);
1494 print_debug(' p_parent_lpn_id => )' );
1495 END IF;
1496
1497 inv_rcv_integration_apis.insert_wlpni
1498 (p_api_version => 1.0
1499 ,x_return_status => x_return_status
1500 ,x_msg_count => x_msg_count
1501 ,x_msg_data => x_msg_data
1502 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1503 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1504 ,p_license_plate_number => NULL
1505 ,p_lpn_group_id => l_group_id
1506 ,p_parent_lpn_id => NULL
1507 );
1508 IF (x_return_status <> g_ret_sts_success) THEN
1509 IF (l_debug = 1) THEN
1510 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_wlpni Fail');
1511 END IF;
1512 RAISE fnd_api.g_exc_error;
1513 END IF;
1514
1515 l_progress := '3.1.3';
1516
1517 IF (l_debug = 1) THEN
1518 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNI inserted successfully');
1519 END IF;
1520
1521 -- Set flag to call to TM
1522 l_call_rm := TRUE;
1523 ELSE
1524 l_progress := '3.2.0';
1525
1526 IF (l_debug = 1) THEN
1527 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') No Sub/Loc changed');
1528 print_debug(' Calling packunpack_container(');
1529 print_debug(' p_content_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1530 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
1531 print_debug(' p_operation => '||2);
1532 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1533 END IF;
1534
1535 wms_container_pvt.packunpack_container
1536 (p_api_version => 1.0
1537 ,p_content_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1538 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
1539 ,p_operation => 2 /* Unpack */
1540 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1541 ,x_return_status => x_return_status
1542 ,x_msg_count => x_msg_count
1543 ,x_msg_data => x_msg_data
1544 );
1545 IF (x_return_status <> g_ret_sts_success) THEN
1546 IF (l_debug = 1) THEN
1547 print_debug('PACK_UNPACK_SPLIT: packunpack_container Fail');
1548 END IF;
1549 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_UNPACK_UPDATE_ERR');
1550 fnd_msg_pub.ADD;
1551 RAISE fnd_api.g_exc_error;
1552 END IF;
1553
1554 l_progress := '3.2.1';
1555 IF (l_debug = 1) THEN
1556 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') packunpack_container sucessful');
1557 END IF;
1558
1559 END IF;
1560 ELSIF (l_tmp_mmtt_rec.txn_type = 'LPN_SPLIT') THEN
1561 l_progress := '4.0';
1562 IF (l_debug = 1) THEN
1563 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') LPN SPLIT');
1564 END IF;
1565
1566 IF (l_tmp_mmtt_rec.sub_loc_changed = 1) THEN
1567 l_progress := '4.1.0';
1568 IF (l_debug = 1) THEN
1569 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
1570 END IF;
1571
1572 abort_mmtts(p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1573 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1574 ,x_return_status => x_return_status
1575 ,x_msg_count => x_msg_count
1576 ,x_msg_data => x_msg_data
1577 );
1578 IF (x_return_status <> g_ret_sts_success) THEN
1579 print_debug('PACK_UNPACK_SPLIT: ERROR - abort_mmtts Fail');
1580 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1581 fnd_msg_pub.ADD;
1582 RAISE fnd_api.g_exc_error;
1583 END IF;
1584
1585 l_progress := '4.1.1';
1586 IF (l_debug = 1) THEN
1587 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') MMTTs aborted successfully');
1588 END IF;
1589
1590 IF (l_first_time <> 1) THEN
1591 l_first_time := 1;
1592 END IF;
1593 l_group_id :=
1594 wms_putaway_utils.insert_rti
1595 (p_from_org => l_tmp_mmtt_rec.organization_id
1596 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1597 ,p_to_org => l_tmp_mmtt_rec.organization_id
1598 ,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
1599 ,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
1600 ,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1601 ,p_first_time => l_first_time
1602 ,p_mobile_txn => 'Y'
1603 ,p_txn_mode_code => p_txn_mode_code
1604 ,x_return_status => x_return_status
1605 ,x_msg_count => x_msg_count
1606 ,x_msg_data => x_msg_data
1607 );
1608 IF (x_return_status <> g_ret_sts_success) THEN
1609 IF (l_debug = 1 ) THEN
1610 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_rti FAIL');
1611 END IF;
1612 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
1613 fnd_msg_pub.ADD;
1614 RAISE fnd_api.g_exc_error;
1615 END IF;
1616
1617 l_progress := '4.1.2';
1618 IF (l_debug = 1) THEN
1619 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
1620 ') RTIs inserted successfully');
1621 END IF;
1622
1623 IF (l_debug = 1) THEN
1624 print_debug('PACK_UNPACK_SPLIT: Calling insert_wlpni');
1625 print_debug(' (p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1626 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1627 print_debug(' p_license_plate_number => ' );
1628 print_debug(' p_lpn_group_id => ' || l_group_id);
1629 print_debug(' p_parent_lpn_id => ' || l_tmp_mmtt_rec.transfer_lpn_id);
1630 END IF;
1631
1632 inv_rcv_integration_apis.insert_wlpni
1633 (p_api_version => 1.0
1634 ,x_return_status => x_return_status
1635 ,x_msg_count => x_msg_count
1636 ,x_msg_data => x_msg_data
1637 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1638 ,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1639 ,p_license_plate_number => NULL
1640 ,p_lpn_group_id => l_group_id
1641 ,p_parent_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1642 );
1643
1644 IF (x_return_status <> g_ret_sts_success) THEN
1645 IF (l_debug = 1) THEN
1646 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_wlpni FAIL');
1647 END IF;
1648 RAISE fnd_api.g_exc_error;
1649 END IF;
1650
1651 l_progress := '4.1.3';
1652 IF (l_debug = 1) THEN
1653 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNIs inserted successfully');
1654 END IF;
1655
1656 -- Set flag to call to TM
1657 l_call_rm := TRUE;
1658 ELSE
1659 l_progress := '4.2.0';
1660 IF (l_debug = 1) THEN
1661 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') NO Sub/Loc changed');
1662 print_debug(' Calling packunpack_container(');
1663 print_debug(' p_content_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1664 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
1665 print_debug(' p_operation => '||2);
1666 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1667 END IF;
1668
1669 wms_container_pvt.packunpack_container
1670 (p_api_version => 1.0
1671 ,p_content_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1672 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
1673 ,p_operation => 2 /* Unpack */
1674 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1675 ,x_return_status => x_return_status
1676 ,x_msg_count => x_msg_count
1677 ,x_msg_data => x_msg_data
1678 );
1679 IF (x_return_status <> g_ret_sts_success) THEN
1680 IF (l_debug = 1) THEN
1681 print_debug('PACK_UNPACK_SPLIT: ERROR - packunpack_container Fail');
1682 END IF;
1683 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_UNPACK_UPDATE_ERR');
1684 fnd_msg_pub.ADD;
1685 RAISE fnd_api.g_exc_error;
1686 END IF;
1687
1688 l_progress := '4.2.1';
1689 IF (l_debug = 1) THEN
1690 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') packunpack_container UNPACK succesful');
1691 print_debug(' Calling packunpack_container(');
1692 print_debug(' p_content_lpn_id => '||l_tmp_mmtt_rec.content_lpn_id);
1693 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.transfer_lpn_id);
1694 print_debug(' p_operation => '||1);
1695 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1696 END IF;
1697
1698 -- Modify LPN Status
1699 wms_container_pvt.modify_lpn_wrapper
1700 ( p_api_version => 1.0
1701 ,x_return_status => x_return_status
1702 ,x_msg_count => x_msg_count
1703 ,x_msg_data => x_msg_data
1704 ,p_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1705 ,p_subinventory => l_tmp_mmtt_rec.transfer_subinventory
1706 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
1707 ,p_lpn_context => 3 --RCV
1708 );
1709 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1710 IF (l_debug = 1) THEN
1711 print_debug('PACK_UNPACK_SPLIT: modify_lpn failed');
1712 END IF;
1713 RAISE fnd_api.g_exc_error;
1714 END IF;
1715
1716 wms_container_pvt.packunpack_container
1717 (p_api_version => '1.0' --??
1718 ,p_content_lpn_id => l_tmp_mmtt_rec.content_lpn_id
1719 ,p_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1720 ,p_operation => 1 /* pack */
1721 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1722 ,x_return_status => x_return_status
1723 ,x_msg_count => x_msg_count
1724 ,x_msg_data => x_msg_data
1725 );
1726 IF (x_return_status <> g_ret_sts_success) THEN
1727 IF (l_debug = 1) THEN
1728 print_debug('PACK_UNPACK_SPLIT: ERROR: packunpack_container Fail', 9);
1729 END IF;
1730 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_PACK_UPDATE_ERR');
1731 fnd_msg_pub.ADD;
1732 RAISE fnd_api.g_exc_error;
1733 END IF;
1734
1735 l_progress := '2.2.1';
1736 IF (l_debug = 1) THEN
1737 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') packunpack_container successful');
1738 END IF;
1739
1740
1741
1742 END IF;
1743 ELSIF (l_tmp_mmtt_rec.txn_type = 'ITEM_PACK') THEN
1744 l_progress := '5.0';
1745 IF (l_debug = 1) THEN
1746 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Item Pack');
1747 END IF;
1748
1749 abort_mmtts(p_move_order_line_id=>l_tmp_mmtt_rec.move_order_line_id
1750 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1751 ,x_return_status => x_return_status
1752 ,x_msg_count => x_msg_count
1753 ,x_msg_data => x_msg_data
1754 );
1755 IF (x_return_status <> g_ret_sts_success) THEN
1756 IF (l_debug = 1) THEN
1757 print_debug('PACK_UNPACK_SPLIT: ERROR: abort_mmtts FAIL', 9);
1758 END IF;
1759 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1760 fnd_msg_pub.ADD;
1761 RAISE fnd_api.g_exc_error;
1762 END IF;
1763
1764 l_mo_splt_tb(1).prim_qty := l_tmp_mmtt_rec.primary_quantity;
1765
1766 IF (l_debug = 1) THEN
1767 print_debug('PACK_UNPACK_SPLIT: MMTTs aborted');
1768 print_debug('PACK_UNPACK_SPLIT: Calling split_mo(');
1769 print_debug(' p_orig_mol_id => ' || l_tmp_mmtt_rec.move_order_line_id);
1770 print_debug(' p_mo_splt_tb(1).prim_qty => ' || l_mo_splt_tb(1).prim_qty);
1771 END IF;
1772
1773 inv_rcv_integration_apis.split_mo
1774 (p_orig_mol_id => l_tmp_mmtt_rec.move_order_line_id
1775 ,p_mo_splt_tb => l_mo_splt_tb
1776 ,x_return_status => x_return_status
1777 ,x_msg_count => x_msg_count
1778 ,x_msg_data => x_msg_data
1779 );
1780 IF (x_return_status <> g_ret_sts_success) THEN
1781 IF (l_debug = 1) THEN
1782 print_debug('PACK_UNPACK_SPLIT: ERROR - split_mo FAIL', 9);
1783 END IF;
1784 fnd_message.set_name('INV', 'INV_SPLIT_MO_ERR');
1785 fnd_msg_pub.ADD;
1786 RAISE fnd_api.g_exc_error;
1787 END IF;
1788
1789 IF (l_tmp_mmtt_rec.move_order_line_id <> l_mo_splt_tb(1).line_id) THEN
1790 IF (l_debug = 1) THEN
1791 print_debug('PACK_UNPACK_SPLIT: Unmarking wms_process_flag FOR line: '
1792 ||l_tmp_mmtt_rec.move_order_line_id);
1793 END IF;
1794
1795 --Update the MOL with unused qty to 1, because the TM will only
1796 --update wms_process_flag for the marked lines and not all
1797 --lines in a LPN as done in 11.5.10
1798 UPDATE mtl_txn_request_lines
1799 SET wms_process_flag = 1
1800 WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
1801 END IF;
1802
1803 l_progress := '5.1';
1804 IF (l_debug = 1) THEN
1805 print_debug('PACK_UNPACK_SPLIT: Calling insert_lot_serial(');
1806 print_debug(' p_transaction_temp_id => '||l_tmp_mmtt_rec.transaction_temp_id);
1807 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1808 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
1809 END IF;
1810
1811 l_old_intf_id :=
1812 insert_lot_serial
1813 (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
1814 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1815 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
1816 ,x_return_status => x_return_status
1817 ,x_msg_count => x_msg_count
1818 ,x_msg_data => x_msg_data
1819 );
1820
1821 IF (x_return_status <> g_ret_sts_success) THEN
1822 IF (l_debug = 1) THEN
1823 print_debug('PACK_UNPACK_SPLIT: ERROR: insert_lot_serial FAIL',
1824 9);
1825 END IF;
1826 RAISE fnd_api.g_exc_error;
1827 END IF;
1828
1829 l_progress := '5.2';
1830
1831 IF (l_debug = 1) THEN
1832 print_debug('PACK_UNPACK_SPLIT: insert_lot_serial returns successfully WITH interface id = ' || l_old_intf_id);
1833 END IF;
1834
1835 /* Retrieve MOL */
1836
1837 OPEN mol_cur(l_mo_splt_tb(1).line_id);
1838 FETCH mol_cur INTO l_mol_rec;
1839 CLOSE mol_cur;
1840
1841 l_progress := '5.3';
1842
1843 IF (l_debug = 1) THEN
1844 print_debug('PACK_UNPACK_SPLIT: Calling Match_transfer_rcvtxn_rec(');
1845 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
1846 print_debug(' p_parent_txn_id => '||l_mol_rec.txn_source_id);
1847 print_debug(' p_reference_id => '||l_mol_rec.reference_id);
1848 print_debug(' p_reference => '||l_mol_rec.reference);
1849 print_debug(' p_reference_type_code => '||l_mol_rec.reference_type_code);
1850 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
1851 print_debug(' p_revision => '||l_mol_rec.revision);
1852 print_debug(' p_subinventory_code => '||l_tmp_mmtt_rec.transfer_subinventory);
1853 print_debug(' p_locator_id => '||l_tmp_mmtt_rec.transfer_to_location);
1854 print_debug(' p_transfer_quantity => '||l_tmp_mmtt_rec.transaction_quantity);
1855 print_debug(' p_transfer_uom_code => '||l_tmp_mmtt_rec.transaction_uom);
1856 print_debug(' p_lot_control_code => '||l_tmp_mmtt_rec.lot_control_code);
1857 print_debug(' p_serial_control_code => '||l_tmp_mmtt_rec.serial_control_code);
1858 print_debug(' p_original_rti_id => '||l_old_intf_id);
1859 print_debug(' p_original_temp_id => ');
1860 print_debug(' p_lot_number => '||l_mol_rec.lot_number);
1861 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
1862 print_debug(' p_transfer_lpn_id => '||l_tmp_mmtt_rec.transfer_lpn_id);
1863 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
1864
1865 -- OPM Covergance
1866 print_debug(' p_sec_transfer_quantity => '||l_tmp_mmtt_rec.secondary_transaction_quantity);
1867 print_debug(' p_sec_transfer_uom_code => '||l_tmp_mmtt_rec.secondary_uom_code);
1868 -- OPM Covergance
1869 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
1870 print_debug(' p_from_sub => '||l_tmp_mmtt_rec.subinventory_code);
1871 print_debug(' p_from_loc => '||l_tmp_mmtt_rec.locator_id);
1872 END IF;
1873
1874 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
1875 ( x_return_status => x_return_status
1876 ,x_msg_count => x_msg_count
1877 ,x_msg_data => x_msg_data
1878 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1879 ,p_parent_txn_id => l_mol_rec.txn_source_id
1880 ,p_reference_id => l_mol_rec.reference_id
1881 ,p_reference => l_mol_rec.reference
1882 ,p_reference_type_code => l_mol_rec.reference_type_code
1883 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
1884 ,p_revision => l_mol_rec.revision --??
1885 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
1886 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
1887 ,p_transfer_quantity => l_tmp_mmtt_rec.transaction_quantity
1888 ,p_transfer_uom_code => l_tmp_mmtt_rec.transaction_uom
1889 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
1890 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
1891 ,p_original_rti_id => l_old_intf_id
1892 ,p_original_temp_id => NULL
1893 ,p_lot_number => l_mol_rec.lot_number
1894 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
1895 ,p_transfer_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
1896 -- OPM Convergance
1897 ,p_sec_transfer_quantity => l_tmp_mmtt_rec.secondary_transaction_quantity
1898 ,p_sec_transfer_uom_code => l_tmp_mmtt_rec.secondary_uom_code
1899 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
1900 -- OPM Convergance
1901 ,p_inspection_status => l_mol_rec.inspection_status
1902 ,p_from_sub => l_tmp_mmtt_rec.subinventory_code
1903 ,p_from_loc => l_tmp_mmtt_rec.locator_id
1904 );
1905 IF (x_return_status <> g_ret_sts_success) THEN
1906 IF (l_debug = 1) THEN
1907 print_debug('PACK_UNPACK_SPLIT: ERROR - Match_transfer_rcvtxn_rec FAIL', 9);
1908 END IF;
1909 RAISE fnd_api.g_exc_error;
1910 END IF;
1911
1912 l_progress := '5.4';
1913
1914 -- Set flag to call to TM
1915 l_call_rm := TRUE;
1916
1917 ELSIF (l_tmp_mmtt_rec.txn_type = 'ITEM_UNPACK') THEN
1918 l_progress := '6.0';
1919 IF (l_debug = 1) THEN
1920 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Item Unpack');
1921 END IF;
1922
1923 IF (l_tmp_mmtt_rec.sub_loc_changed = 1) THEN
1924 l_progress := '6.1.0';
1925 IF (l_debug = 1) THEN
1926 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
1927 END IF;
1928
1929 l_mo_splt_tb(1).prim_qty := l_tmp_mmtt_rec.primary_quantity;
1930
1931 IF (l_debug = 1) THEN
1932 print_debug('PACK_UNPACK_SPLIT: Calling split_mo(');
1933 print_debug(' p_orig_mol_id => ' || l_tmp_mmtt_rec.move_order_line_id);
1934 print_debug(' p_mo_splt_tb(1).prim_qty => ' || l_mo_splt_tb(1).prim_qty);
1935 END IF;
1936
1937 inv_rcv_integration_apis.split_mo
1938 (p_orig_mol_id => l_tmp_mmtt_rec.move_order_line_id
1939 ,p_mo_splt_tb => l_mo_splt_tb
1940 ,x_return_status => x_return_status
1941 ,x_msg_count => x_msg_count
1942 ,x_msg_data => x_msg_data
1943 );
1944 IF (x_return_status <> g_ret_sts_success) THEN
1945 IF (l_debug = 1) THEN
1946 print_debug('PACK_UNPACK_SPLIT: ERROR - split_mo FAIL', 9);
1947 END IF;
1948 fnd_message.set_name('INV', 'INV_SPLIT_MO_ERR');
1949 fnd_msg_pub.ADD;
1950 RAISE fnd_api.g_exc_error;
1951 END IF;
1952
1953 IF (l_tmp_mmtt_rec.move_order_line_id <> l_mo_splt_tb(1).line_id) THEN
1954 IF (l_debug = 1) THEN
1955 print_debug('PACK_UNPACK_SPLIT: Unmarking wms_process_flag FOR line: '
1956 ||l_tmp_mmtt_rec.move_order_line_id);
1957 END IF;
1958
1959 --Update the MOL with unused qty to 1, because the TM will only
1960 --update wms_process_flag for the marked lines and not all
1961 --lines in a LPN as done in 11.5.10
1962 UPDATE mtl_txn_request_lines
1963 SET wms_process_flag = 1
1964 WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
1965 END IF;
1966
1967 l_progress := '6.1.1';
1968
1969 FOR i IN 1 .. l_mo_splt_tb.COUNT LOOP
1970 l_out_mo_splt_tb(l_out_mo_splt_tb.COUNT+i).line_id
1971 := l_mo_splt_tb(i).line_id;
1972 END LOOP;
1973
1974 l_progress := '6.1.2';
1975
1976 IF (l_debug = 1) THEN
1977 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Calling abort_mmtts');
1978 END IF;
1979
1980 abort_mmtts(p_move_order_line_id => l_mo_splt_tb(1).line_id
1981 ,p_organization_id => l_tmp_mmtt_rec.organization_id
1982 ,x_return_status => x_return_status
1983 ,x_msg_count => x_msg_count
1984 ,x_msg_data => x_msg_data
1985 );
1986 IF (x_return_status <> g_ret_sts_success) THEN
1987 IF (l_debug = 1) THEN
1988 print_debug('PACK_UNPACK_SPLIT: ERROR - abort_mmtts FAIL', 9);
1989 END IF;
1990 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
1991 fnd_msg_pub.ADD;
1992 RAISE fnd_api.g_exc_error;
1993 END IF;
1994
1995 l_progress := '6.1.3';
1996
1997 IF (l_debug = 1) THEN
1998 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Calling insert_lot_serial');
1999 print_debug(' p_transaction_temp_id => '||l_tmp_mmtt_rec.transaction_temp_id);
2000 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
2001 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
2002 END IF;
2003
2004 l_old_intf_id :=
2005 insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
2006 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2007 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2008 ,x_return_status => x_return_status
2009 ,x_msg_count => x_msg_count
2010 ,x_msg_data => x_msg_data
2011 );
2012 IF (x_return_status <> g_ret_sts_success) THEN
2013 IF (l_debug = 1) THEN
2014 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_lot_serial FAIL', 9);
2015 END IF;
2016 RAISE fnd_api.g_exc_error;
2017 END IF;
2018
2019 l_progress := '6.1.4';
2020
2021 OPEN mol_cur(l_tmp_mmtt_rec.move_order_line_id);
2022 FETCH mol_cur INTO l_mol_rec;
2023 CLOSE mol_cur;
2024
2025 l_progress := '6.1.5';
2026
2027 IF (l_debug = 1) THEN
2028 print_debug('PACK_UNPACK_SPLIT: Calling Match_transfer_rcvtxn_rec(');
2029 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
2030 print_debug(' p_parent_txn_id => '||l_mol_rec.txn_source_id);
2031 print_debug(' p_reference_id => '||l_mol_rec.reference_id);
2032 print_debug(' p_reference => '||l_mol_rec.reference);
2033 print_debug(' p_reference_type_code => '||l_mol_rec.reference_type_code);
2034 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
2035 print_debug(' p_revision => '||l_mol_rec.revision);
2036 print_debug(' p_subinventory_code => '||l_tmp_mmtt_rec.transfer_subinventory);
2037 print_debug(' p_locator_id => '||l_tmp_mmtt_rec.transfer_to_location);
2038 print_debug(' p_transfer_quantity => '||l_tmp_mmtt_rec.transaction_quantity);
2039 print_debug(' p_transfer_uom_code => '||l_tmp_mmtt_rec.transaction_uom);
2040 print_debug(' p_lot_control_code => '||l_tmp_mmtt_rec.lot_control_code);
2041 print_debug(' p_serial_control_code => '||l_tmp_mmtt_rec.serial_control_code);
2042 print_debug(' p_original_rti_id => '||l_old_intf_id);
2043 print_debug(' p_original_temp_id => ');
2044 print_debug(' p_lot_number => '||l_mol_rec.lot_number);
2045 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
2046 print_debug(' p_transfer_lpn_id => ');
2047 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
2048
2049 -- OPM Convergance
2050 print_debug(' p_sec_transfer_quantity => '||l_tmp_mmtt_rec.secondary_transaction_quantity);
2051 print_debug(' p_sec_transfer_uom_code => '||l_tmp_mmtt_rec.secondary_uom_code);
2052 -- OPM Convergance
2053 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
2054 print_debug(' p_from_sub => '||l_tmp_mmtt_rec.subinventory_code);
2055 print_debug(' p_from_loc => '||l_tmp_mmtt_rec.locator_id);
2056 END IF;
2057
2058 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2059 ( x_return_status => x_return_status
2060 ,x_msg_count => x_msg_count
2061 ,x_msg_data => x_msg_data
2062 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2063 ,p_parent_txn_id => l_mol_rec.txn_source_id
2064 ,p_reference_id => l_mol_rec.reference_id
2065 ,p_reference => l_mol_rec.reference
2066 ,p_reference_type_code => l_mol_rec.reference_type_code
2067 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2068 ,p_revision => l_mol_rec.revision --??
2069 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2070 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2071 ,p_transfer_quantity => l_tmp_mmtt_rec.transaction_quantity
2072 ,p_transfer_uom_code => l_tmp_mmtt_rec.transaction_uom
2073 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2074 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2075 ,p_original_rti_id => l_old_intf_id
2076 ,p_original_temp_id => NULL
2077 ,p_lot_number => l_mol_rec.lot_number
2078 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
2079 ,p_transfer_lpn_id => NULL
2080 -- OPM Convergance
2081 ,p_sec_transfer_quantity => l_tmp_mmtt_rec.secondary_transaction_quantity
2082 ,p_sec_transfer_uom_code => l_tmp_mmtt_rec.secondary_uom_code
2083 -- OPM Convergance
2084 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2085 ,p_inspection_status => l_mol_rec.inspection_status
2086 ,p_from_sub => l_tmp_mmtt_rec.subinventory_code
2087 ,p_from_loc => l_tmp_mmtt_rec.locator_id
2088 );
2089 IF (x_return_status <> g_ret_sts_success) THEN
2090 IF (l_debug = 1) THEN
2091 print_debug('ERROR: Match_transfer_rcvtxn_rec FAIL', 9);
2092 END IF;
2093 RAISE fnd_api.g_exc_error;
2094 END IF;
2095
2096 -- Set flag to call to TM
2097 l_call_rm := TRUE;
2098
2099 l_progress := '6.1.6';
2100
2101 IF (l_debug = 1) THEN
2102 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Xfer api sucessfully returns');
2103 END IF;
2104 ELSE -- no sub/loc changed
2105 l_progress := '6.2.0';
2106 IF (l_debug = 1) THEN
2107 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2108 ') No Sub/Loc changed');
2109 END IF;
2110
2111 l_mo_splt_tb(1).prim_qty := l_tmp_mmtt_rec.primary_quantity;
2112
2113 IF (l_debug = 1) THEN
2114 print_debug('PACK_UNPACK_SPLIT: Calling split_mo(');
2115 print_debug(' p_orig_mol_id => ' || l_tmp_mmtt_rec.move_order_line_id);
2116 print_debug(' p_mo_splt_tb(1).prim_qty => ' || l_mo_splt_tb(1).prim_qty);
2117 END IF;
2118
2119 inv_rcv_integration_apis.split_mo
2120 (p_orig_mol_id => l_tmp_mmtt_rec.move_order_line_id
2121 ,p_mo_splt_tb => l_mo_splt_tb
2122 ,x_return_status => x_return_status
2123 ,x_msg_count => x_msg_count
2124 ,x_msg_data => x_msg_data
2125 );
2126 IF (x_return_status <> g_ret_sts_success) THEN
2127 IF (l_debug = 1) THEN
2128 print_debug('PACK_UNPACK_SPLIT: ERROR - split_mo FAIL', 9);
2129 END IF;
2130 fnd_message.set_name('INV', 'INV_SPLIT_MO_ERR');
2131 fnd_msg_pub.ADD;
2132 RAISE fnd_api.g_exc_error;
2133 END IF;
2134
2135 IF (l_tmp_mmtt_rec.move_order_line_id <> l_mo_splt_tb(1).line_id) THEN
2136 IF (l_debug = 1) THEN
2137 print_debug('PACK_UNPACK_SPLIT: Unmarking wms_process_flag FOR line: '
2138 ||l_tmp_mmtt_rec.move_order_line_id);
2139 END IF;
2140
2141 --Update the MOL with unused qty to 1, because the TM will only
2142 --update wms_process_flag for the marked lines and not all
2143 --lines in a LPN as done in 11.5.10
2144 UPDATE mtl_txn_request_lines
2145 SET wms_process_flag = 1
2146 WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
2147 END IF;
2148
2149 l_progress := '6.2.1';
2150
2151 FOR i IN 1 .. l_mo_splt_tb.COUNT LOOP
2152 IF (l_debug = 1) THEN
2153 print_debug('PACK_UNPACK_SPLIT: split_mo created MOL: '
2154 ||l_mo_splt_tb(i).line_id);
2155 END IF;
2156 l_out_mo_splt_tb(l_out_mo_splt_tb.COUNT+i).line_id
2157 := l_mo_splt_tb(i).line_id;
2158 END LOOP;
2159
2160 l_progress := '6.2.2';
2161
2162 IF (l_debug = 1) THEN
2163 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Calling abort_mmtts');
2164 END IF;
2165
2166 --Even when there is no sub/loc changed, we need to abort
2167 --the operation plan because they are LPN based.
2168 abort_mmtts(p_move_order_line_id => l_mo_splt_tb(1).line_id
2169 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2170 ,x_return_status => x_return_status
2171 ,x_msg_count => x_msg_count
2172 ,x_msg_data => x_msg_data
2173 );
2174 IF (x_return_status <> g_ret_sts_success) THEN
2175 IF (l_debug = 1) THEN
2176 print_debug('PACK_UNPACK_SPLIT: ERROR - abort_mmtts FAIL', 9);
2177 END IF;
2178 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
2179 fnd_msg_pub.ADD;
2180 RAISE fnd_api.g_exc_error;
2181 END IF;
2182
2183 l_progress := '6.2.2.5';
2184
2185
2186 IF (l_debug = 1) THEN
2187 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') split_mo sucessfully returns. Calling insert_lot_serial');
2188 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Calling insert_lot_serial');
2189 print_debug(' p_transaction_temp_id => '||l_tmp_mmtt_rec.transaction_temp_id);
2190 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
2191 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
2192
2193 END IF;
2194
2195 l_old_intf_id :=
2196 insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
2197 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2198 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2199 ,x_return_status => x_return_status
2200 ,x_msg_count => x_msg_count
2201 ,x_msg_data => x_msg_data
2202 );
2203 IF (x_return_status <> g_ret_sts_success) THEN
2204 IF (l_debug = 1) THEN
2205 print_debug('ERROR: insert_lot_serial FAIL', 9);
2206 END IF;
2207 RAISE fnd_api.g_exc_error;
2208 END IF;
2209
2210 l_progress := '6.2.3';
2211
2212 OPEN mol_cur(l_tmp_mmtt_rec.move_order_line_id);
2213 FETCH mol_cur INTO l_mol_rec;
2214 CLOSE mol_cur;
2215
2216 l_progress := '6.2.4';
2217
2218 IF (l_debug = 1) THEN
2219 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') insert_lot_serail sucessfully returns. Starts TO LOOP mmtts');
2220 END IF;
2221
2222 -- Look at each new mmtt, and insert rti for each of them
2223 -- Bug 5231114: Added the condition on transaction_source_type_id and
2224 -- transaction_action_id for the following combinations:13/12 and 4/27
2225 FOR l_mmtt_rec IN
2226 ( SELECT
2227 transaction_temp_id
2228 ,transaction_quantity
2229 ,primary_quantity
2230 ,transaction_uom
2231 ,secondary_transaction_quantity
2232 ,secondary_uom_code
2233 FROM
2234 mtl_material_transactions_temp
2235 WHERE
2236 ( move_order_line_id = l_mo_splt_tb(1).line_id AND
2237 ( ( transaction_source_type_id = 1 AND
2238 transaction_action_id = 27) OR
2239 ( transaction_source_type_id = 7 AND
2240 transaction_action_id = 12) OR
2241 ( transaction_source_type_id = 12 AND
2242 transaction_action_id = 27) OR
2243 ( transaction_source_type_id = 13 AND
2244 transaction_action_id = 12) OR
2245 ( transaction_source_type_id = 4 AND
2246 transaction_action_id = 27))) )
2247 LOOP
2248 -- keep track of remaining quantity
2249 l_progress := '6.2.5';
2250
2251 IF (l_debug = 1) THEN
2252 print_debug('PACK_UNPACK_SPLIT: Looking at:');
2253 print_debug(' l_mmtt_rec.transaction_temp_id => ' || l_mmtt_rec.transaction_temp_id);
2254 print_debug(' l_mmtt_rec.transaction_quantity => ' || l_mmtt_rec.transaction_quantity);
2255 print_debug(' l_mmtt_rec.primary_quantity => ' || l_mmtt_rec.primary_quantity);
2256 print_debug(' l_mmtt_rec.transaction_uom => ' || l_mmtt_rec.primary_quantity);
2257 END IF;
2258
2259 l_tmp_mmtt_rec.primary_quantity :=
2260 l_tmp_mmtt_rec.primary_quantity -
2261 l_mmtt_rec.primary_quantity;
2262
2263 l_progress := '6.2.6';
2264
2265 /* Create a dummy RT ID */
2266 SELECT rcv_transactions_interface_s.NEXTVAL
2267 INTO l_new_intf_id
2268 FROM dual;
2269
2270 l_progress := '6.2.7';
2271
2272 /* Split lot serial */
2273 l_rti_tb.DELETE;
2274
2275 l_rti_tb(1).orig_interface_trx_id := l_old_intf_id;
2276 l_rti_tb(1).new_interface_trx_id := l_new_intf_id;
2277 l_rti_tb(1).quantity := l_mmtt_rec.transaction_quantity;
2278 l_rti_tb(1).to_organization_id := l_tmp_mmtt_rec.organization_id;
2279 l_rti_tb(1).item_id := l_tmp_mmtt_rec.inventory_item_id;
2280 l_rti_tb(1).uom_code := l_tmp_mmtt_rec.transaction_uom;
2281 IF (l_debug = 1) THEN
2282 print_debug('PACK_UNPACK_SPLIT: Calling split_lot_serial(');
2283 print_debug(' p_rti_tb(1).orig_interface_trx_id => '
2284 || l_old_intf_id);
2285 print_debug(' p_rti_tb(1).new_interface_trx_id => '
2286 || l_new_intf_id);
2287 print_debug(' p_rti_tb(1).quantity => '
2288 || l_mmtt_rec.transaction_quantity);
2289 END IF;
2290
2291 IF (l_tmp_mmtt_rec.primary_quantity > 0) THEN
2292 l_rti_tb(2).orig_interface_trx_id := l_old_intf_id;
2293 l_rti_tb(2).new_interface_trx_id := l_old_intf_id;
2294
2295 IF (l_tmp_mmtt_rec.primary_uom_code <> l_mmtt_rec.transaction_uom) THEN
2296 l_rti_tb(2).quantity := inv_rcv_cache.convert_qty
2297 (l_tmp_mmtt_rec.inventory_item_id
2298 ,l_tmp_mmtt_rec.primary_quantity
2299 ,l_tmp_mmtt_rec.primary_uom_code
2300 ,l_mmtt_rec.transaction_uom);
2301 ELSE
2302 l_rti_tb(2).quantity := l_tmp_mmtt_rec.primary_quantity;
2303 END IF;
2304
2305 l_rti_tb(2).to_organization_id := l_tmp_mmtt_rec.organization_id;
2306 l_rti_tb(2).item_id := l_tmp_mmtt_rec.inventory_item_id;
2307 l_rti_tb(2).uom_code := l_tmp_mmtt_rec.transaction_uom;
2308 END IF;
2309
2310 inv_rcv_integration_pvt.split_lot_serial
2311 (p_rti_tb => l_rti_tb
2312 ,x_return_status => x_return_status
2313 ,x_msg_count => x_msg_count
2314 ,x_msg_data => x_msg_data
2315 );
2316 IF (x_return_status <> g_ret_sts_success) THEN
2317 IF (l_debug = 1) THEN
2318 print_debug('ERROR: split_lot_serial FAIL', 9);
2319 END IF;
2320 RAISE fnd_api.g_exc_error;
2321 END IF;
2322
2323 l_progress := '6.2.8';
2324
2325 IF (l_debug = 1) THEN
2326 print_debug('PACK_UNPACK_SPLIT: Calling Match_transfer_rcvtxn_rec(');
2327 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
2328 print_debug(' p_parent_txn_id => '||l_mol_rec.txn_source_id);
2329 print_debug(' p_reference_id => '||l_mol_rec.reference_id);
2330 print_debug(' p_reference => '||l_mol_rec.reference);
2331 print_debug(' p_reference_type_code => '||l_mol_rec.reference_type_code);
2332 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
2333 print_debug(' p_revision => '||l_mol_rec.revision);
2334 print_debug(' p_subinventory_code => '||l_tmp_mmtt_rec.transfer_subinventory);
2335 print_debug(' p_locator_id => '||l_tmp_mmtt_rec.transfer_to_location);
2336 print_debug(' p_transfer_quantity => '||l_tmp_mmtt_rec.transaction_quantity);
2337 print_debug(' p_transfer_uom_code => '||l_tmp_mmtt_rec.transaction_uom);
2338 print_debug(' p_lot_control_code => '||l_tmp_mmtt_rec.lot_control_code);
2339 print_debug(' p_serial_control_code => '||l_tmp_mmtt_rec.serial_control_code);
2340 print_debug(' p_original_rti_id => '||l_new_intf_id);
2341 print_debug(' p_original_temp_id => '||l_mmtt_rec.transaction_temp_id);
2342 print_debug(' p_lot_number => '||l_mol_rec.lot_number);
2343 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
2344 print_debug(' p_transfer_lpn_id => )');
2345 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
2346 -- OPM Convergance
2347 print_debug(' p_sec_transfer_quantity => '||l_tmp_mmtt_rec.secondary_transaction_quantity);
2348 print_debug(' p_sec_transfer_uom_code => '||l_tmp_mmtt_rec.secondary_uom_code);
2349 -- OPM Convergance
2350 END IF;
2351
2352 /* Call transfer API with the new RT ID */
2353 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2354 ( x_return_status => x_return_status
2355 ,x_msg_count => x_msg_count
2356 ,x_msg_data => x_msg_data
2357 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2358 ,p_parent_txn_id => l_mol_rec.txn_source_id
2359 ,p_reference_id => l_mol_rec.reference_id
2360 ,p_reference => l_mol_rec.reference
2361 ,p_reference_type_code => l_mol_rec.reference_type_code
2362 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2363 ,p_revision => l_mol_rec.revision
2364 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2365 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2366 ,p_transfer_quantity => l_mmtt_rec.transaction_quantity
2367 ,p_transfer_uom_code => l_mmtt_rec.transaction_uom
2368 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2369 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2370 ,p_original_rti_id => l_new_intf_id
2371 ,p_original_temp_id => l_mmtt_rec.transaction_temp_id
2372 ,p_lot_number => l_mol_rec.lot_number
2373 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
2374 ,p_transfer_lpn_id => NULL
2375 -- OPM Convergance
2376 ,p_sec_transfer_quantity => l_mmtt_rec.secondary_transaction_quantity
2377 ,p_sec_transfer_uom_code => l_mmtt_rec.secondary_uom_code
2378 -- OPM Convergance
2379 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2380 ,p_inspection_status => l_mol_rec.inspection_status
2381 );
2382 IF (x_return_status <> g_ret_sts_success) THEN
2383 IF (l_debug = 1) THEN
2384 print_debug('PACK_UNPACK_SPLIT : ERROR - Match_transfer_rcvtxn_rec FAIL',
2385 9);
2386 END IF;
2387 RAISE fnd_api.g_exc_error;
2388 END IF;
2389
2390 l_progress := '6.2.9';
2391
2392 IF (l_debug = 1) THEN
2393 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2394 ') xfer api returns sucessfully');
2395 END IF;
2396
2397 END LOOP;
2398
2399 l_progress := '6.2.10';
2400 IF (l_debug = 1) THEN
2401 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Loop exited. Looking at remaining quantities');
2402 END IF;
2403
2404 IF (l_tmp_mmtt_rec.primary_quantity > 0) THEN
2405 -- Call transfer API for the remaining qty (not detailed)
2406 -- with the old RT ID
2407 l_progress := '6.2.11';
2408 IF (l_debug = 1) THEN
2409 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2410 ') There are remaining quantities. Calling xfer api');
2411 END IF;
2412
2413 IF (l_debug = 1) THEN
2414 print_debug('PACK_UNPACK_SPLIT: Calling Match_transfer_rcvtxn_rec(');
2415 print_debug(' p_organization_id => '||l_tmp_mmtt_rec.organization_id);
2416 print_debug(' p_parent_txn_id => '||l_mol_rec.txn_source_id);
2417 print_debug(' p_reference_id => '||l_mol_rec.reference_id);
2418 print_debug(' p_reference => '||l_mol_rec.reference);
2419 print_debug(' p_reference_type_code => '||l_mol_rec.reference_type_code);
2420 print_debug(' p_item_id => '||l_tmp_mmtt_rec.inventory_item_id);
2421 print_debug(' p_revision => '||l_mol_rec.revision);
2422 print_debug(' p_subinventory_code => '||l_tmp_mmtt_rec.transfer_subinventory);
2423 print_debug(' p_locator_id => '||l_tmp_mmtt_rec.transfer_to_location);
2424 print_debug(' p_transfer_quantity => '||l_tmp_mmtt_rec.transaction_quantity);
2425 print_debug(' p_transfer_uom_code => '||l_tmp_mmtt_rec.transaction_uom);
2426 print_debug(' p_lot_control_code => '||l_tmp_mmtt_rec.lot_control_code);
2427 print_debug(' p_serial_control_code => '||l_tmp_mmtt_rec.serial_control_code);
2428 print_debug(' p_original_rti_id => '||l_new_intf_id);
2429 print_debug(' p_original_temp_id => ');
2430 print_debug(' p_lot_number => '||l_mol_rec.lot_number);
2431 print_debug(' p_lpn_id => '||l_tmp_mmtt_rec.lpn_id);
2432 print_debug(' p_transfer_lpn_id => )');
2433 print_debug(' p_inspection_status => '||l_mol_rec.inspection_status);
2434
2435 -- OPM Convergance
2436 print_debug(' p_sec_transfer_quantity => '||l_tmp_mmtt_rec.secondary_transaction_quantity);
2437 print_debug(' p_sec_transfer_uom_code => '||l_tmp_mmtt_rec.secondary_uom_code);
2438 -- OPM Convergance
2439 END IF;
2440 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2441 ( x_return_status => x_return_status
2442 ,x_msg_count => x_msg_count
2443 ,x_msg_data => x_msg_data
2444 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2445 ,p_parent_txn_id => l_mol_rec.txn_source_id
2446 ,p_reference_id => l_mol_rec.reference_id
2447 ,p_reference => l_mol_rec.reference
2448 ,p_reference_type_code => l_mol_rec.reference_type_code
2449 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2450 ,p_revision => l_mol_rec.revision
2451 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2452 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2453 ,p_transfer_quantity => l_tmp_mmtt_rec.transaction_quantity
2454 ,p_transfer_uom_code => l_tmp_mmtt_rec.transaction_uom
2455 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2456 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2457 ,p_original_rti_id => l_old_intf_id
2458 ,p_original_temp_id => NULL
2459 ,p_lot_number => l_mol_rec.lot_number
2460 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
2461 ,p_transfer_lpn_id => NULL
2462 -- OPM Convergance
2463 ,p_sec_transfer_quantity => l_tmp_mmtt_rec.secondary_transaction_quantity
2464 ,p_sec_transfer_uom_code => l_tmp_mmtt_rec.secondary_uom_code
2465 -- OPM Convergance
2466 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2467 ,p_inspection_status => l_mol_rec.inspection_status
2468 );
2469 IF (x_return_status <> g_ret_sts_success) THEN
2470 IF (l_debug = 1) THEN
2471 print_debug('PACK_UNPACK_SPLIT: ERROR - Match_transfer_rcvtxn_rec FAIL',
2472 9);
2473 END IF;
2474 RAISE fnd_api.g_exc_error;
2475 END IF;
2476
2477 END IF; -- End remaining qty check
2478
2479 l_progress := '6.2.12';
2480
2481 -- Set flag to call to TM
2482 l_call_rm := TRUE;
2483
2484 IF (l_debug = 1) THEN
2485 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') xfer api sucessfully returns');
2486 END IF;
2487 END IF;
2488 ELSIF (l_tmp_mmtt_rec.txn_type = 'ITEM_SPLIT') THEN
2489 l_progress := '7.0';
2490
2491 IF (l_debug = 1) THEN
2492 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Item Split');
2493 END IF;
2494
2495 BEGIN
2496 SELECT 1
2497 INTO l_xfer_lpn_loaded
2498 FROM dual
2499 WHERE EXISTS (SELECT /*+ INDEX (wdt, WMS_DISPATCHED_TASKS_N2) */ 'LOADED'
2500 FROM mtl_material_transactions_temp mmtt,
2501 wms_dispatched_tasks wdt
2502 WHERE mmtt.organization_id = l_tmp_mmtt_rec.organization_id
2503 AND mmtt.transaction_temp_id = wdt.transaction_temp_id
2504 AND wdt.organization_id = l_tmp_mmtt_rec.organization_id
2505 AND wdt.task_type = 2
2506 AND wdt.status = 4
2507 AND mmtt.lpn_id IN (SELECT lpn_id
2508 FROM wms_license_plate_numbers
2509 START WITH lpn_id = l_tmp_mmtt_rec.transfer_lpn_id
2510 CONNECT BY PRIOR lpn_id = parent_lpn_id
2511 )
2512 );
2513 EXCEPTION
2514 WHEN OTHERS THEN
2515 l_xfer_lpn_loaded := 0;
2516 END;
2517
2518 IF (l_debug = 1) THEN
2519 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Item Split. ');
2520 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') LOADED? ' ||
2521 l_xfer_lpn_loaded);
2522 END IF;
2523
2524 -- Only need to abort the MMTT if the transfer LPN is not
2525 -- loaded. Since if it is loaded, then it means that it
2526 -- is being moved around. This is made for Item Load scenarios.
2527 -- As for the packing workbench, the transfer LPN should never
2528 -- be loaded. So it should always abort the MMTTs
2529 -- So, the conclusion is, if the LPN is loaded, then treat it
2530 -- as having no sub/loc changed
2531 IF (l_tmp_mmtt_rec.sub_loc_changed = 1 AND l_xfer_lpn_loaded = 0) THEN
2532 l_progress := '7.1.0';
2533 IF (l_debug = 1) THEN
2534 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Sub/Loc changed');
2535 END IF;
2536
2537 l_mo_splt_tb(1).prim_qty := l_tmp_mmtt_rec.primary_quantity;
2538
2539 IF (l_debug = 1) THEN
2540 print_debug('PACK_UNPACK_SPLIT: Calling split_mo(');
2541 print_debug(' p_orig_mol_id => ' || l_tmp_mmtt_rec.move_order_line_id);
2542 print_debug(' p_mo_splt_tb(1).prim_qty => ' || l_mo_splt_tb(1).prim_qty);
2543 END IF;
2544
2545 inv_rcv_integration_apis.split_mo
2546 (p_orig_mol_id => l_tmp_mmtt_rec.move_order_line_id
2547 ,p_mo_splt_tb => l_mo_splt_tb
2548 ,x_return_status => x_return_status
2549 ,x_msg_count => x_msg_count
2550 ,x_msg_data => x_msg_data
2551 );
2552 IF (x_return_status <> g_ret_sts_success) THEN
2553 IF (l_debug = 1) THEN
2554 print_debug('PACK_UNPACK_SPLIT: ERROR - split_mo FAIL');
2555 END IF;
2556 fnd_message.set_name('INV', 'INV_SPLIT_MO_ERR');
2557 fnd_msg_pub.ADD;
2558 RAISE fnd_api.g_exc_error;
2559 END IF;
2560
2561 IF (l_tmp_mmtt_rec.move_order_line_id <> l_mo_splt_tb(1).line_id) THEN
2562 IF (l_debug = 1) THEN
2563 print_debug('PACK_UNPACK_SPLIT: Unmarking wms_process_flag FOR line: '
2564 ||l_tmp_mmtt_rec.move_order_line_id);
2565 END IF;
2566
2567 --Update the MOL with unused qty to 1, because the TM will only
2568 --update wms_process_flag for the marked lines and not all
2569 --lines in a LPN as done in 11.5.10
2570 UPDATE mtl_txn_request_lines
2571 SET wms_process_flag = 1
2572 WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
2573 END IF;
2574
2575 l_progress := '7.1.1';
2576
2577 FOR i IN 1 .. l_mo_splt_tb.COUNT LOOP
2578 IF (l_debug = 1) THEN
2579 print_debug('PACK_UNPACK_SPLIT: split_mo created mo:'||l_mo_splt_tb(i).line_id);
2580 END IF;
2581 l_out_mo_splt_tb(l_out_mo_splt_tb.COUNT+i).line_id
2582 := l_mo_splt_tb(i).line_id;
2583 END LOOP;
2584
2585 l_progress := '7.1.2';
2586
2587 IF (l_debug = 1) THEN
2588 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') split_mo returns sucessfully. Calling abort_mmtts');
2589 END IF;
2590
2591
2592 abort_mmtts(p_move_order_line_id => l_mo_splt_tb(1).line_id
2593 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2594 ,x_return_status => x_return_status
2595 ,x_msg_count => x_msg_count
2596 ,x_msg_data => x_msg_data
2597 );
2598 IF (x_return_status <> g_ret_sts_success) THEN
2599 IF (l_debug = 1) THEN
2600 print_debug('PACK_UNPACK_SPLIT: ERROR - abort_mmtts FAIL');
2601 END IF;
2602 FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
2603 fnd_msg_pub.ADD;
2604 RAISE fnd_api.g_exc_error;
2605 END IF;
2606
2607 l_progress := '7.1.3';
2608
2609 IF (l_debug = 1) THEN
2610 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') abort_mmtts returns sucessfully. Calling insert_lot_serial');
2611 END IF;
2612
2613 l_old_intf_id :=
2614 insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
2615 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2616 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2617 ,x_return_status => x_return_status
2618 ,x_msg_count => x_msg_count
2619 ,x_msg_data => x_msg_data
2620 );
2621 IF (x_return_status <> g_ret_sts_success) THEN
2622 IF (l_debug = 1) THEN
2623 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_lot_serial FAIL', 9);
2624 END IF;
2625 RAISE fnd_api.g_exc_error;
2626 END IF;
2627
2628 l_progress := '7.1.4';
2629 IF (l_debug = 1) THEN
2630 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') insert_lot_serial returns sucessfully.');
2631 END IF;
2632
2633 /* Retrieve MOL */
2634
2635 OPEN mol_cur(l_tmp_mmtt_rec.move_order_line_id);
2636 FETCH mol_cur INTO l_mol_rec;
2637 CLOSE mol_cur;
2638
2639 l_progress := '7.1.5';
2640 IF (l_debug = 1) THEN
2641 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2642 ') MOL_CUR fetched sucessfully. Calling xfer api');
2643 END IF;
2644
2645 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2646 (x_return_status => x_return_status
2647 ,x_msg_count => x_msg_count
2648 ,x_msg_data => x_msg_data
2649 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2650 ,p_parent_txn_id => l_mol_rec.txn_source_id
2651 ,p_reference_id => l_mol_rec.reference_id
2652 ,p_reference => l_mol_rec.reference
2653 ,p_reference_type_code => l_mol_rec.reference_type_code
2654 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2655 ,p_revision => l_mol_rec.revision
2656 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2657 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2658 ,p_transfer_quantity => l_tmp_mmtt_rec.transaction_quantity
2659 ,p_transfer_uom_code => l_tmp_mmtt_rec.transaction_uom
2660 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2661 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2662 ,p_original_rti_id => l_old_intf_id
2663 ,p_original_temp_id => NULL
2664 ,p_lot_number => l_mol_rec.lot_number
2665 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
2666 ,p_transfer_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
2667 -- OPM Convergance
2668 ,p_sec_transfer_quantity => l_tmp_mmtt_rec.secondary_transaction_quantity
2669 ,p_sec_transfer_uom_code => l_tmp_mmtt_rec.secondary_uom_code
2670 -- OPM Convergance
2671 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2672 ,p_inspection_status => l_mol_rec.inspection_status
2673 );
2674 IF (x_return_status <> g_ret_sts_success) THEN
2675 IF (l_debug = 1) THEN
2676 print_debug('PACK_UNPACK_SPLIT: ERROR - Match_transfer_rcvtxn_rec FAIL');
2677 END IF;
2678 RAISE fnd_api.g_exc_error;
2679 END IF;
2680
2681 l_progress := '7.1.6';
2682
2683 -- Set flag to call to TM
2684 l_call_rm := TRUE;
2685
2686 IF (l_debug = 1) THEN
2687 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2688 ') xfer_api returns sucessfully. ');
2689 END IF;
2690
2691 ELSE -- no sub/loc changed or LPN is loaded
2692 l_progress := '7.2.0';
2693 IF (l_debug = 1) THEN
2694 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2695 ') Sub/Loc not changed OR LPN IS LOADED');
2696 END IF;
2697
2698 l_mo_splt_tb(1).prim_qty := l_tmp_mmtt_rec.primary_quantity;
2699
2700 l_progress := '7.2.1';
2701 IF (l_debug = 1) THEN
2702 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2703 ') Calling split_mo');
2704 END IF;
2705
2706 inv_rcv_integration_apis.split_mo
2707 (p_orig_mol_id => l_tmp_mmtt_rec.move_order_line_id
2708 ,p_mo_splt_tb => l_mo_splt_tb
2709 ,x_return_status => x_return_status
2710 ,x_msg_count => x_msg_count
2711 ,x_msg_data => x_msg_data
2712 );
2713 IF (x_return_status <> g_ret_sts_success) THEN
2714 IF (l_debug = 1) THEN
2715 print_debug('PACK_UNPACK_SPLIT: ERROR - split_mo FAIL');
2716 END IF;
2717 fnd_message.set_name('INV', 'INV_SPLIT_MO_ERR');
2718 fnd_msg_pub.ADD;
2719 RAISE fnd_api.g_exc_error;
2720 END IF;
2721
2722 IF (l_tmp_mmtt_rec.move_order_line_id <> l_mo_splt_tb(1).line_id) THEN
2723 IF (l_debug = 1) THEN
2724 print_debug('PACK_UNPACK_SPLIT: Unmarking wms_process_flag FOR line: '
2725 ||l_tmp_mmtt_rec.move_order_line_id);
2726 END IF;
2727
2728 --Update the MOL with unused qty to 1, because the TM will only
2729 --update wms_process_flag for the marked lines and not all
2730 --lines in a LPN as done in 11.5.10
2731 UPDATE mtl_txn_request_lines
2732 SET wms_process_flag = 1
2733 WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
2734 END IF;
2735
2736 l_progress := '7.2.2';
2737 FOR i IN 1 .. l_mo_splt_tb.COUNT LOOP
2738 l_out_mo_splt_tb(l_out_mo_splt_tb.COUNT+i).line_id
2739 := l_mo_splt_tb(i).line_id;
2740 END LOOP;
2741
2742 l_progress := '7.2.3';
2743 IF (l_debug = 1) THEN
2744 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2745 ') Calling insert_lot_serial');
2746 END IF;
2747
2748 l_old_intf_id :=
2749 insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
2750 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2751 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2752 ,x_return_status => x_return_status
2753 ,x_msg_count => x_msg_count
2754 ,x_msg_data => x_msg_data
2755 );
2756 IF (x_return_status <> g_ret_sts_success) THEN
2757 IF (l_debug = 1) THEN
2758 print_debug('PACK_UNPACK_SPLIT: ERROR - insert_lot_serail FAIL');
2759 END IF;
2760 RAISE fnd_api.g_exc_error;
2761 END IF;
2762
2763 l_progress := '7.2.4';
2764 OPEN mol_cur(l_tmp_mmtt_rec.move_order_line_id);
2765 FETCH mol_cur INTO l_mol_rec;
2766 CLOSE mol_cur;
2767
2768 l_progress := '7.2.5';
2769 IF (l_debug = 1) THEN
2770 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2771 ') Start to loop mmtt' );
2772 print_debug(' line_id = ' || l_mol_rec.line_id);
2773 print_debug(' reference_id = ' ||l_mol_rec.reference_id);
2774 print_debug(' reference = '||l_mol_rec.reference);
2775 print_debug(' reference_type_code = '||l_mol_rec.reference_type_code);
2776
2777 END IF;
2778
2779
2780 -- Look at each new mmtt, and insert rti for each of them
2781 -- Bug 5231114: Added the condition on transaction_source_type_id and
2782 -- transaction_action_id for the following combinations:13/12 and 4/27
2783 FOR l_mmtt_rec IN
2784 ( SELECT
2785 transaction_temp_id
2786 ,primary_quantity
2787 ,transaction_quantity
2788 ,transaction_uom
2789 ,secondary_transaction_quantity
2790 ,secondary_uom_code
2791 FROM
2792 mtl_material_transactions_temp
2793 WHERE
2794 ( move_order_line_id = l_mo_splt_tb(1).line_id AND
2795 ( ( transaction_source_type_id = 1 AND
2796 transaction_action_id = 27) OR
2797 ( transaction_source_type_id = 7 AND
2798 transaction_action_id = 12) OR
2799 ( transaction_source_type_id = 12 AND
2800 transaction_action_id = 27) OR
2801 ( transaction_source_type_id = 13 AND
2802 transaction_action_id = 12) OR
2803 ( transaction_source_type_id = 4 AND
2804 transaction_action_id = 27))) )
2805 LOOP
2806 l_progress := '7.2.6';
2807 IF (l_debug = 1) THEN
2808 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2809 ') Looking at MMTT:'
2810 ||l_mmtt_rec.transaction_temp_id);
2811 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2812 ') l_tmp_mmtt_rec.primary_quantity: '||
2813 l_tmp_mmtt_rec.primary_quantity);
2814 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2815 ') l_mmtt_rec.primary_quantity: '||
2816 l_mmtt_rec.primary_quantity);
2817 END IF;
2818
2819 -- keep track of remaining quantity
2820 l_tmp_mmtt_rec.primary_quantity :=
2821 l_tmp_mmtt_rec.primary_quantity -
2822 l_mmtt_rec.primary_quantity;
2823
2824
2825 /* Create a dummy RT ID */
2826 SELECT rcv_transactions_interface_s.NEXTVAL
2827 INTO l_new_intf_id
2828 FROM dual;
2829 l_progress := '7.2.7';
2830
2831 /* Split lot serial */
2832 l_rti_tb.DELETE;
2833
2834 l_rti_tb(1).orig_interface_trx_id := l_old_intf_id;
2835 l_rti_tb(1).new_interface_trx_id := l_new_intf_id;
2836 IF (l_tmp_mmtt_rec.transaction_uom <> l_mmtt_rec.transaction_uom) THEN
2837 l_rti_tb(1).quantity := inv_rcv_cache.convert_qty
2838 (p_inventory_item_id => l_tmp_mmtt_rec.inventory_item_id
2839 ,p_from_qty => l_mmtt_rec.transaction_quantity
2840 ,p_from_uom_code => l_mmtt_rec.transaction_uom
2841 ,p_to_uom_code => l_tmp_mmtt_rec.transaction_uom);
2842 ELSE
2843 l_rti_tb(1).quantity := l_mmtt_rec.transaction_quantity;
2844 END IF;
2845
2846 l_rti_tb(1).to_organization_id := l_tmp_mmtt_rec.organization_id;
2847 l_rti_tb(1).item_id := l_tmp_mmtt_rec.inventory_item_id;
2848 l_rti_tb(1).uom_code := l_tmp_mmtt_rec.transaction_uom;
2849
2850 IF (l_tmp_mmtt_rec.primary_quantity > 0) THEN
2851 l_rti_tb(2).orig_interface_trx_id := l_old_intf_id;
2852 l_rti_tb(2).new_interface_trx_id := l_old_intf_id;
2853 l_rti_tb(2).quantity := l_tmp_mmtt_rec.transaction_quantity;
2854 l_rti_tb(2).to_organization_id := l_tmp_mmtt_rec.organization_id;
2855 l_rti_tb(2).item_id := l_tmp_mmtt_rec.inventory_item_id;
2856 l_rti_tb(2).uom_code := l_tmp_mmtt_rec.transaction_uom;
2857 END IF;
2858
2859 inv_rcv_integration_pvt.split_lot_serial
2860 (p_rti_tb => l_rti_tb
2861 ,x_return_status => x_return_status
2862 ,x_msg_count => x_msg_count
2863 ,x_msg_data => x_msg_data
2864 );
2865 IF (x_return_status <> g_ret_sts_success) THEN
2866 IF (l_debug = 1) THEN
2867 print_debug('PACK_UNPACK_SPLIT: ERROR - split_lot_serial FAIL', 9);
2868 END IF;
2869 RAISE fnd_api.g_exc_error;
2870 END IF;
2871 l_progress := '7.2.8';
2872
2873 IF l_mmtt_rec.transaction_uom <> l_tmp_mmtt_rec.transaction_uom THEN
2874 l_qty_to_insert := inv_rcv_cache.convert_qty
2875 (p_inventory_item_id => l_tmp_mmtt_rec.inventory_item_id
2876 ,p_from_qty => l_mmtt_rec.transaction_quantity
2877 ,p_from_uom_code => l_mmtt_rec.transaction_uom
2878 ,p_to_uom_code => l_tmp_mmtt_rec.transaction_uom);
2879 l_uom_to_insert := l_tmp_mmtt_rec.transaction_uom ;
2880 ELSE
2881 l_uom_to_insert := l_mmtt_rec.transaction_uom;
2882 l_qty_to_insert := l_mmtt_rec.transaction_quantity;
2883 END IF;
2884
2885 IF (l_debug = 1) THEN
2886 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2887 ') split_lot_serial returns sucessfully. calling xfer api');
2888 END IF;
2889
2890 /* Call transfer API with the new RT ID */
2891 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2892 ( x_return_status => x_return_status
2893 ,x_msg_count => x_msg_count
2894 ,x_msg_data => x_msg_data
2895 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2896 ,p_parent_txn_id => l_mol_rec.txn_source_id
2897 ,p_reference_id => l_mol_rec.reference_id
2898 ,p_reference => l_mol_rec.reference
2899 ,p_reference_type_code => l_mol_rec.reference_type_code
2900 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2901 ,p_revision => l_mol_rec.revision
2902 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2903 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2904 ,p_transfer_quantity => l_qty_to_insert
2905 ,p_transfer_uom_code => l_uom_to_insert
2906 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2907 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2908 ,p_original_rti_id => l_new_intf_id
2909 ,p_original_temp_id => l_mmtt_rec.transaction_temp_id
2910 ,p_lot_number => l_mol_rec.lot_number
2911 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id
2912 ,p_transfer_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
2913 -- OPM Convergance
2914 ,p_sec_transfer_quantity => l_mmtt_rec.secondary_transaction_quantity
2915 ,p_sec_transfer_uom_code => l_mmtt_rec.secondary_uom_code
2916 -- OPM Convergance
2917 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2918 ,p_inspection_status => l_mol_rec.inspection_status
2919 );
2920 IF (x_return_status <> g_ret_sts_success) THEN
2921 IF (l_debug = 1) THEN
2922 print_debug('PACK_UNPACK_SPLIT: ERROR - Match_transfer_rcvtxn_rec FAIL',
2923 9);
2924 END IF;
2925 RAISE fnd_api.g_exc_error;
2926 END IF;
2927 l_progress := '7.2.8';
2928
2929 END LOOP;
2930
2931 l_progress := '7.2.9';
2932 IF (l_debug = 1) THEN
2933 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2934 ') Loop exited. Looking at remaining quantities');
2935 END IF;
2936
2937 IF (l_tmp_mmtt_rec.primary_quantity > 0) THEN
2938 -- Call transfer API for the remaining qty (not detailed)
2939 -- with the old RT ID */
2940 l_progress := '7.2.10';
2941 IF (l_debug = 1) THEN
2942 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2943 ') There are remaining quantities. Calling xfer api');
2944 END IF;
2945
2946 inv_rcv_std_transfer_apis.Match_transfer_rcvtxn_rec
2947 ( x_return_status => x_return_status
2948 ,x_msg_count => x_msg_count
2949 ,x_msg_data => x_msg_data
2950 ,p_organization_id => l_tmp_mmtt_rec.organization_id
2951 ,p_parent_txn_id => l_mol_rec.txn_source_id
2952 ,p_reference_id => l_mol_rec.reference_id
2953 ,p_reference => l_mol_rec.reference
2954 ,p_reference_type_code => l_mol_rec.reference_type_code
2955 ,p_item_id => l_tmp_mmtt_rec.inventory_item_id
2956 ,p_revision => l_mol_rec.revision
2957 ,p_subinventory_code => l_tmp_mmtt_rec.transfer_subinventory
2958 ,p_locator_id => l_tmp_mmtt_rec.transfer_to_location
2959 ,p_transfer_quantity => l_tmp_mmtt_rec.transaction_quantity
2960 ,p_transfer_uom_code => l_tmp_mmtt_rec.transaction_uom
2961 ,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
2962 ,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
2963 ,p_original_rti_id => l_old_intf_id
2964 ,p_original_temp_id => NULL
2965 ,p_lot_number => l_mol_rec.lot_number --??
2966 ,p_lpn_id => l_tmp_mmtt_rec.lpn_id --??
2967 ,p_transfer_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
2968 -- OPM Convergance
2969 ,p_sec_transfer_quantity => l_tmp_mmtt_rec.secondary_transaction_quantity
2970 ,p_sec_transfer_uom_code => l_tmp_mmtt_rec.secondary_uom_code
2971 -- OPM Convergance
2972 ,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
2973 ,p_inspection_status => l_mol_rec.inspection_status
2974 );
2975 IF (x_return_status <> g_ret_sts_success) THEN
2976 IF (l_debug = 1) THEN
2977 print_debug('PAC_UNPACK_SPLIT: ERROR - 1 Match_transfer_rcvtxn_rec FAIL');
2978 END IF;
2979 RAISE fnd_api.g_exc_error;
2980 END IF;
2981 END IF;
2982
2983 l_progress := '7.2.11';
2984 -- Set flag to call to TM
2985 l_call_rm := TRUE;
2986
2987 IF (l_debug = 1) THEN
2988 print_debug('PACK_UNPACK_SPLIT: ('||l_progress||
2989 ') xfer_api sucessfully returns');
2990 END IF;
2991 END IF; -- End sub/loc test
2992 ELSE
2993 fnd_message.set_name('INV', 'INV_INVALID_TXN_TYPE');
2994 fnd_msg_pub.ADD;
2995 RAISE fnd_api.g_exc_error;
2996 END IF; -- End txn_type test
2997
2998
2999 END LOOP; -- End tmp_mmtt_cur loop
3000
3001 IF tmp_mmtt_cur_by_hdr_id%isopen THEN
3002 CLOSE tmp_mmtt_cur_by_hdr_id;
3003 END IF;
3004
3005 IF tmp_mmtt_cur_by_txn_id%isopen THEN
3006 CLOSE tmp_mmtt_cur_by_txn_id;
3007 END IF;
3008
3009 -- Delete temp MMTTs
3010 IF (l_debug = 1) THEN
3011 print_debug('Deleting temp MMTTs');
3012 END IF;
3013
3014 BEGIN
3015 forall i IN 1 .. l_mmtts_count
3016 DELETE FROM mtl_serial_numbers_temp
3017 WHERE transaction_temp_id = l_mmtt_ids(i)
3018 OR transaction_temp_id IN
3019 (SELECT mtlt.serial_transaction_temp_id
3020 FROM mtl_transaction_lots_temp mtlt
3021 WHERE mtlt.transaction_temp_id = l_mmtt_ids(i));
3022 IF (l_debug = 1) THEN
3023 print_debug('# OF MSNT DELETED: ' || SQL%rowcount);
3024 END IF;
3025 EXCEPTION
3026 WHEN OTHERS THEN
3027 IF (l_debug = 1) THEN
3028 print_debug('Error deleting temp MSNTs');
3029 END IF;
3030 RAISE fnd_api.g_exc_error;
3031 END;
3032
3033 BEGIN
3034 forall i IN 1 .. l_mmtts_count
3035 DELETE FROM mtl_transaction_lots_temp
3036 WHERE transaction_temp_id = l_mmtt_ids(i);
3037 IF (l_debug = 1) THEN
3038 print_debug('# OF MTLT DELETED: ' || SQL%rowcount);
3039 END IF;
3040 EXCEPTION
3041 WHEN OTHERS THEN
3042 IF (l_debug = 1) THEN
3043 print_debug('Error deleting temp MTLTs');
3044 END IF;
3045 RAISE fnd_api.g_exc_error;
3046 END;
3047
3048 BEGIN
3049 forall i IN 1 .. l_mmtts_count
3050 DELETE FROM mtl_material_transactions_temp
3051 WHERE transaction_temp_id = l_mmtt_ids(i);
3052 IF (l_debug = 1) THEN
3053 print_debug('# OF MMTT DELETED: ' || SQL%rowcount);
3054 END IF;
3055 EXCEPTION
3056 WHEN OTHERS THEN
3057 IF (l_debug =1 )THEN
3058 print_debug('Error deleting temp MMTTs');
3059 END IF;
3060 RAISE fnd_api.g_exc_error;
3061 END;
3062
3063 COMMIT; --Need to commit the delete
3064
3065 IF (l_call_rm = TRUE AND p_call_rcv_tm = fnd_api.g_true) THEN
3066 IF (l_debug = 1) THEN
3067 print_debug('Calling Receiving TM');
3068 END IF;
3069
3070 l_txn_mode_code := inv_rcv_common_apis.g_po_startup_value.transaction_mode;
3071
3072 inv_rcv_common_apis.g_po_startup_value.transaction_mode
3073 := p_txn_mode_code;
3074
3075 -- Need to pass in group ID?
3076 inv_rcv_mobile_process_txn.rcv_process_receive_txn
3077 (x_return_status => x_return_status
3078 ,x_msg_data => x_msg_data
3079 );
3080 IF (x_return_status <> g_ret_sts_success) THEN
3081 IF (l_debug = 1) THEN
3082 print_debug('PACK_UNPACK_SPLIT: Error - Rcv TM Failed');
3083 END IF;
3084
3085 inv_rcv_common_apis.g_po_startup_value.transaction_mode
3086 := l_txn_mode_code;
3087 fnd_message.set_name('WMS', 'WMS_TD_TXNMGR_ERROR');
3088 fnd_msg_pub.ADD;
3089 x_return_status := g_ret_sts_unexp_err ;
3090 fnd_msg_pub.count_and_get
3091 ( p_count => x_msg_count
3092 , p_data => x_msg_data
3093 );
3094 RETURN;
3095 END IF;
3096
3097 inv_rcv_common_apis.g_po_startup_value.transaction_mode
3098 := l_txn_mode_code;
3099 -- Need to clear in exception handling too?
3100 inv_rcv_common_apis.rcv_clear_global;
3101 END IF;
3102
3103 x_mo_lines_tb := l_out_mo_splt_tb;
3104
3105 EXCEPTION
3106 WHEN fnd_api.g_exc_unexpected_error THEN
3107 IF (l_debug = 1) THEN
3108 print_debug('PACK_UNPACK_SPLIT: Unexpected Exception occured after progress: '
3109 || l_progress);
3110 END IF;
3111
3112 IF tmp_mmtt_cur_by_hdr_id%isopen THEN
3113 CLOSE tmp_mmtt_cur_by_hdr_id;
3114 END IF;
3115
3116 IF tmp_mmtt_cur_by_txn_id%isopen THEN
3117 CLOSE tmp_mmtt_cur_by_txn_id;
3118 END IF;
3119
3120 x_return_status := g_ret_sts_unexp_err ;
3121 fnd_msg_pub.count_and_get
3122 ( p_count => x_msg_count
3123 , p_data => x_msg_data
3124 );
3125 ROLLBACK TO pack_unpack_split_pub;
3126 WHEN OTHERS THEN
3127 IF (l_debug = 1) THEN
3128 print_debug('PACK_UNPACK_SPLIT: Other Exception occured after progress: '
3129 || l_progress|| SQLERRM );
3130 END IF;
3131
3132 IF tmp_mmtt_cur_by_hdr_id%isopen THEN
3133 CLOSE tmp_mmtt_cur_by_hdr_id;
3134 END IF;
3135
3136 IF tmp_mmtt_cur_by_txn_id%isopen THEN
3137 CLOSE tmp_mmtt_cur_by_txn_id;
3138 END IF;
3139
3140 x_return_status := g_ret_sts_unexp_err;
3141 fnd_msg_pub.count_and_get
3142 ( p_count => x_msg_count
3143 , p_data => x_msg_data
3144 );
3145 ROLLBACK TO pack_unpack_split_pub;
3146 END pack_unpack_split;
3147
3148
3149 END wms_rcv_pup_pvt;
3150