[Home] [Help]
PACKAGE BODY: APPS.WMS_WIP_INTEGRATION
Source
1 PACKAGE BODY WMS_WIP_Integration AS
2 /* $Header: WMSWIPIB.pls 120.7 2011/08/02 21:53:57 sfulzele ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_WIP_Integration';
7
8
9 PROCEDURE Update_MO_Line
10 (p_lpn_id IN NUMBER,
11 p_wms_process_flag IN NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2)
15
16 IS
17 l_return_status VARCHAR2(1);
18
19 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
20 BEGIN
21 l_return_status:= FND_API.G_RET_STS_SUCCESS;
22
23 UPDATE mtl_txn_request_lines
24 SET wms_process_flag = p_wms_process_flag
25 WHERE lpn_id = p_lpn_id;
26
27 x_return_status:=l_return_status;
28
29 EXCEPTION
30
31 WHEN no_data_found THEN
32 x_return_status:=FND_API.G_RET_STS_ERROR;
33 fnd_msg_pub.count_and_get
34 ( p_count => x_msg_count
35 , p_data => x_msg_data
36 );
37
38 END Update_MO_Line;
39
40
41
42 PROCEDURE Backflush
43 (p_header_id IN NUMBER,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2)
47
48 IS
49 l_count NUMBER;
50 l_msg_data VARCHAR2(5000);
51 l_return_status VARCHAR2(1);
52 l_next_transaction_temp_id NUMBER;
53 l_next_transaction_header_id NUMBER;
54 l_next_ser_tran_temp_id NUMBER;
55 l_query_result NUMBER;
56
57 l_source_id NUMBER;
58 l_temp_source_id NUMBER;
59 l_temp_header_id NUMBER;
60 l_temp2_header_id NUMBER;
61 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
62 l_lot_number VARCHAR2(80);
63 l_txn_ret NUMBER;
64 l_fm_serial_number VARCHAR2(30);
65
66
67 CURSOR wip_lpn_completions_csr IS
68 SELECT
69 header_id,
70 source_id
71 FROM wip_lpn_completions
72 WHERE source_id = l_source_id
73 AND source_id <> header_id;
74
75 CURSOR wip_lpn_comp_serials_csr IS
76 SELECT
77 header_id,
78 lot_number,
79 fm_serial_number
80 FROM wip_lpn_completions_serials
81 WHERE header_id = l_temp_header_id
82 AND lot_number is not null;
83
84 CURSOR wip_lpn_comp_serials_csr2 IS
85 SELECT fm_serial_number
86 FROM wip_lpn_completions_serials
87 WHERE header_id = l_temp_header_id
88 AND lot_number IS NULL;
89
90 /* Bug: 2976160 : cursor defined to replace 'Select INTO ' to support
91 multiple lots in wip_lpn_completions_lots for the given header_id. */
92 CURSOR wip_lpn_comp_lots_csr IS
93 SELECT lot_number
94 FROM wip_lpn_completions_lots
95 WHERE header_id = l_temp_header_id;
96 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
97 BEGIN
98 l_return_status:= FND_API.G_RET_STS_SUCCESS;
99 l_query_result := 0;
100 l_txn_ret:=0;
101
102 BEGIN
103
104 SELECT
105 1,
106 source_id
107 INTO
108 l_query_result,
109 l_source_id
110 FROM wip_lpn_completions
111 WHERE header_id = p_header_id;
112
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 l_query_result := 0;
116 END;
117
118 IF l_query_result <> 0 THEN
119
120
121 --Get value from sequence for next transaction_header_id
122 SELECT mtl_material_transactions_s.NEXTVAL
123 INTO l_next_transaction_header_id
124 FROM dual ;
125
126 IF (l_debug = 1) THEN
127 mydebug('Backflush: header_id: ' || l_next_transaction_header_id);
128 END IF;
129
130 OPEN wip_lpn_completions_csr;
131 LOOP
132 FETCH wip_lpn_completions_csr
133 INTO l_temp_header_id,
134 l_temp_source_id;
135 EXIT when wip_lpn_completions_csr%NOTFOUND;
136
137 -- Insert into mtl_material_transactions_temp
138
139 --Get value from sequence for next transaction_temp_id
140 SELECT mtl_material_transactions_s.NEXTVAL
141 INTO l_next_transaction_temp_id
142 FROM dual ;
143
144 IF (l_debug = 1) THEN
145 mydebug('Backflush: temp_id '||l_next_transaction_temp_id);
146 END IF;
147
148 INSERT INTO mtl_material_transactions_temp
149 ( transaction_temp_id,
150 transaction_header_id,
151 source_code,
152 last_update_date,
153 last_updated_by,
154 creation_date,
155 created_by,
156 last_update_login,
157 program_application_id,
158 program_id,
159 program_update_date,
160 inventory_item_id,
161 organization_id,
162 primary_quantity,
163 transaction_quantity,
164 transaction_uom,
165 transaction_date,
166 transaction_action_id,
167 transaction_source_id,
168 transaction_source_type_id,
169 transaction_type_id,
170 transaction_mode,
171 acct_period_id,
172 subinventory_code,
173 locator_id,
174 wip_entity_type,
175 schedule_id,
176 repetitive_line_id,
177 operation_seq_num,
178 cost_group_id,
179 kanban_card_id,
180 qa_collection_id,
181 lpn_id,
182 reason_id,
183 lock_flag,
184 error_code,
185 final_completion_flag,
186 end_item_unit_number,
187 transaction_status,
188 process_flag,
189 completion_transaction_id,
190 flow_schedule,
191 source_line_id,
192 wip_supply_type,
193 revision,
194 source_project_id,
195 source_task_id
196 )
197 (SELECT l_next_transaction_temp_id,
198 l_next_transaction_header_id,
199 source_code,
200 last_update_date,
201 last_updated_by,
202 creation_date,
203 created_by,
204 last_update_login,
205 program_application_id,
206 program_id,
207 program_update_date,
208 inventory_item_id,
209 organization_id,
210 primary_quantity,
211 transaction_quantity,
212 transaction_uom,
213 transaction_date,
214 transaction_action_id,
215 transaction_source_id,
216 transaction_source_type_id,
217 transaction_type_id,
218 transaction_mode,
219 acct_period_id,
220 subinventory_code,
221 locator_id,
222 wip_entity_type,
223 schedule_id,
224 repetitive_line_id,
225 operation_seq_num,
226 cost_group_id,
227 kanban_card_id,
228 qa_collection_id,
229 lpn_id,
230 reason_id,
231 lock_flag,
232 error_code,
233 final_completion_flag,
234 end_item_unit_number,
235 3,
236 'Y',
237 completion_transaction_id,
238 decode(wip_entity_type, 4, 'Y', null),
239 source_id,
240 NULL,
241 bom_revision,
242 source_project_id,
243 source_task_id
244 FROM wip_lpn_completions
245 WHERE header_id = l_temp_header_id
246 AND source_id = l_temp_source_id);
247
248 IF (l_debug = 1) THEN
249 mydebug('Backflush: after insert into MMTT' || l_temp_header_id || ' ' || l_temp_source_id);
250 END IF;
251
252 -- Logic for finding which rows will be inserted
253 -- in the mtl_serial_numbers_temp table
254
255 IF (l_debug = 1) THEN
256 mydebug('Backflush: insert into mtl_serial_numbers_temp' );
257 END IF;
258
259 INSERT INTO mtl_serial_numbers_temp
260 ( transaction_temp_id,
261 last_update_date,
262 last_updated_by,
263 creation_date,
264 created_by,
265 last_update_login,
266 request_id,
267 program_application_id,
268 program_id,
269 program_update_date,
270 fm_serial_number,
271 to_serial_number,
272 serial_prefix,
273 parent_serial_number,
274 error_code,
275 -- transaction_quantity,
276 serial_attribute_category,
277 origination_date,
278 C_ATTRIBUTE1,
279 C_ATTRIBUTE2,
280 C_ATTRIBUTE3,
281 C_ATTRIBUTE4,
282 C_ATTRIBUTE5,
283 C_ATTRIBUTE6,
284 C_ATTRIBUTE7,
285 C_ATTRIBUTE8,
286 C_ATTRIBUTE9,
287 C_ATTRIBUTE10,
288 C_ATTRIBUTE11,
289 C_ATTRIBUTE12,
290 C_ATTRIBUTE13,
291 C_ATTRIBUTE14,
292 C_ATTRIBUTE15,
293 C_ATTRIBUTE16,
294 C_ATTRIBUTE17,
295 C_ATTRIBUTE18,
296 C_ATTRIBUTE19,
297 C_ATTRIBUTE20,
298 D_ATTRIBUTE1,
299 D_ATTRIBUTE2,
300 D_ATTRIBUTE3,
301 D_ATTRIBUTE4,
302 D_ATTRIBUTE5,
303 D_ATTRIBUTE6,
304 D_ATTRIBUTE7,
305 D_ATTRIBUTE8,
306 D_ATTRIBUTE9,
307 D_ATTRIBUTE10,
308 N_ATTRIBUTE1,
309 N_ATTRIBUTE2,
310 N_ATTRIBUTE3,
311 N_ATTRIBUTE4,
312 N_ATTRIBUTE5,
313 N_ATTRIBUTE6,
314 N_ATTRIBUTE7,
315 N_ATTRIBUTE8,
316 N_ATTRIBUTE9,
317 N_ATTRIBUTE10,
318 territory_code,
319 time_since_new,
320 cycles_since_new,
321 time_since_overhaul,
322 cycles_since_overhaul,
323 time_since_repair,
324 cycles_since_repair,
325 time_since_visit,
326 cycles_since_visit,
327 time_since_mark,
328 cycles_since_mark
329 )
330 (SELECT l_next_transaction_temp_id,
331 last_update_date,
332 last_updated_by,
333 creation_date,
334 created_by,
335 last_update_login,
336 request_id,
337 program_application_id,
338 program_id,
339 program_update_date,
340 fm_serial_number,
341 to_serial_number,
342 serial_prefix,
343 parent_serial_number,
344 error_code,
345 -- transaction_quantity,
346 serial_attribute_category,
347 origination_date,
348 C_ATTRIBUTE1,
349 C_ATTRIBUTE2,
350 C_ATTRIBUTE3,
351 C_ATTRIBUTE4,
352 C_ATTRIBUTE5,
353 C_ATTRIBUTE6,
354 C_ATTRIBUTE7,
355 C_ATTRIBUTE8,
356 C_ATTRIBUTE9,
357 C_ATTRIBUTE10,
358 C_ATTRIBUTE11,
359 C_ATTRIBUTE12,
360 C_ATTRIBUTE13,
361 C_ATTRIBUTE14,
362 C_ATTRIBUTE15,
363 C_ATTRIBUTE16,
364 C_ATTRIBUTE17,
365 C_ATTRIBUTE18,
366 C_ATTRIBUTE19,
367 C_ATTRIBUTE20,
368 D_ATTRIBUTE1,
369 D_ATTRIBUTE2,
370 D_ATTRIBUTE3,
371 D_ATTRIBUTE4,
372 D_ATTRIBUTE5,
373 D_ATTRIBUTE6,
374 D_ATTRIBUTE7,
375 D_ATTRIBUTE8,
376 D_ATTRIBUTE9,
377 D_ATTRIBUTE10,
378 N_ATTRIBUTE1,
379 N_ATTRIBUTE2,
380 N_ATTRIBUTE3,
381 N_ATTRIBUTE4,
382 N_ATTRIBUTE5,
383 N_ATTRIBUTE6,
384 N_ATTRIBUTE7,
385 N_ATTRIBUTE8,
386 N_ATTRIBUTE9,
387 N_ATTRIBUTE10,
388 territory_code,
389 time_since_new,
390 cycles_since_new,
391 time_since_overhaul,
392 cycles_since_overhaul,
393 time_since_repair,
394 cycles_since_repair,
395 time_since_visit,
396 cycles_since_visit,
397 time_since_mark,
398 cycles_since_mark
399 FROM wip_lpn_completions_serials
400 WHERE header_id = l_temp_header_id
401 AND lot_number is null);
402
403 IF (l_debug = 1) THEN
404 mydebug('Backflush: open wip_lpn_comp_serials_csr2 ' );
405 END IF;
406
407
408 OPEN wip_lpn_comp_serials_csr2;
409 LOOP
410 FETCH
411 wip_lpn_comp_serials_csr2
412 INTO
413 l_fm_serial_number;
414 EXIT when wip_lpn_comp_serials_csr2%NOTFOUND;
415
416 IF (l_debug = 1) THEN
417 mydebug('Backflush: serial item');
418 mydebug('Backflush: ' || l_temp_header_id || ' ' || l_fm_serial_number);
419 END IF;
420
421 wms_wip_integration.update_serial
422 (p_header_id => l_temp_header_id,
423 p_serial_number => l_fm_serial_number,
424 x_return_status => l_return_status,
425 x_msg_count => x_msg_count,
426 x_msg_data => x_msg_data);
427
428
429 END LOOP;
430 CLOSE wip_lpn_comp_serials_csr2;
431
432 -- Logic for finding which rows will be inserted
433 -- in the mtl_transaction_lots_temp table
434
435 IF (l_debug = 1) THEN
436 mydebug('Backflush: Insert into mtl_transaction_lots_temp ');
437 END IF;
438
439 INSERT INTO mtl_transaction_lots_temp
440 ( transaction_temp_id,
441 last_update_date,
442 last_updated_by,
443 creation_date,
444 created_by,
445 last_update_login,
446 request_id,
447 program_application_id,
448 program_id,
449 program_update_date,
450 transaction_quantity,
451 primary_quantity,
452 lot_number,
453 lot_expiration_date,
454 error_code,
455 lot_attribute_category,
456 C_ATTRIBUTE1,
457 C_ATTRIBUTE11,
458 C_ATTRIBUTE10,
459 C_ATTRIBUTE9,
460 C_ATTRIBUTE8,
461 C_ATTRIBUTE7,
462 C_ATTRIBUTE6,
463 C_ATTRIBUTE5,
464 C_ATTRIBUTE4,
465 C_ATTRIBUTE3,
466 C_ATTRIBUTE2,
467 description,
468 vendor_id,
469 grade_code,
470 origination_date,
471 date_code,
472 change_date,
473 age,
474 retest_date,
475 maturity_date,
476 item_size,
477 color,
478 volume,
479 volume_uom,
480 place_of_origin,
481 best_by_date,
482 length,
483 length_uom,
484 recycled_content,
485 thickness,
486 thickness_uom,
487 width,
488 width_uom,
489 curl_wrinkle_fold,
490 C_ATTRIBUTE12,
491 C_ATTRIBUTE13,
492 C_ATTRIBUTE14,
493 C_ATTRIBUTE15,
494 C_ATTRIBUTE16,
495 C_ATTRIBUTE17,
496 C_ATTRIBUTE18,
497 C_ATTRIBUTE19,
498 C_ATTRIBUTE20,
499 D_ATTRIBUTE2,
500 D_ATTRIBUTE3,
501 D_ATTRIBUTE4,
502 D_ATTRIBUTE5,
503 D_ATTRIBUTE6,
504 D_ATTRIBUTE7,
505 D_ATTRIBUTE8,
506 D_ATTRIBUTE9,
507 D_ATTRIBUTE1,
508 N_ATTRIBUTE1,
509 N_ATTRIBUTE2,
510 N_ATTRIBUTE3,
511 N_ATTRIBUTE4,
512 N_ATTRIBUTE5,
513 N_ATTRIBUTE6,
514 N_ATTRIBUTE7,
515 N_ATTRIBUTE8,
516 N_ATTRIBUTE9,
517 N_ATTRIBUTE10,
518 vendor_name,
519 supplier_lot_number,
520 territory_code
521 )
522 (SELECT l_next_transaction_temp_id,
523 last_update_date,
524 last_updated_by,
525 creation_date,
526 created_by,
527 last_update_login,
528 request_id,
529 program_application_id,
530 program_id,
531 program_update_date,
532 transaction_quantity,
533 primary_quantity,
534 lot_number,
535 lot_expiration_date,
536 error_code,
537 lot_attribute_category,
538 C_ATTRIBUTE1,
539 C_ATTRIBUTE11,
540 C_ATTRIBUTE10,
541 C_ATTRIBUTE9,
542 C_ATTRIBUTE8,
543 C_ATTRIBUTE7,
544 C_ATTRIBUTE6,
545 C_ATTRIBUTE5,
546 C_ATTRIBUTE4,
547 C_ATTRIBUTE3,
548 C_ATTRIBUTE2,
549 description,
550 vendor_id,
551 grade_code,
552 origination_date,
553 date_code,
554 change_date,
555 age,
556 retest_date,
557 maturity_date,
558 item_size,
559 color,
560 volume,
561 volume_uom,
562 place_of_origin,
563 best_by_date,
564 length,
565 length_uom,
566 recycled_content,
567 thickness,
568 thickness_uom,
569 width,
570 width_uom,
571 curl_wrinkle_fold,
572 C_ATTRIBUTE12,
573 C_ATTRIBUTE13,
574 C_ATTRIBUTE14,
575 C_ATTRIBUTE15,
576 C_ATTRIBUTE16,
577 C_ATTRIBUTE17,
578 C_ATTRIBUTE18,
579 C_ATTRIBUTE19,
580 C_ATTRIBUTE20,
581 D_ATTRIBUTE2,
582 D_ATTRIBUTE3,
583 D_ATTRIBUTE4,
584 D_ATTRIBUTE5,
585 D_ATTRIBUTE6,
586 D_ATTRIBUTE7,
587 D_ATTRIBUTE8,
588 D_ATTRIBUTE9,
589 D_ATTRIBUTE1,
590 N_ATTRIBUTE1,
591 N_ATTRIBUTE2,
592 N_ATTRIBUTE3,
593 N_ATTRIBUTE4,
594 N_ATTRIBUTE5,
595 N_ATTRIBUTE6,
596 N_ATTRIBUTE7,
597 N_ATTRIBUTE8,
598 N_ATTRIBUTE9,
599 N_ATTRIBUTE10,
600 vendor_name,
601 supplier_lot_number,
602 territory_code
603 FROM wip_lpn_completions_lots
604 WHERE header_id = l_temp_header_id);
605
606
607 IF (l_debug = 1) THEN
608 mydebug('Backflush: Loop thru wip_lpn_comp_lots_rec ');
609 END IF;
610
611 /* Bug: 2976160 : replaced Select INTO with cursor loop to call
612 wms_wip_integration.insert_lot for each lot in wip_lpn_completions_lots table,
613 for the given header_id. there can be more than one lot . */
614 FOR wip_lpn_comp_lots_rec in wip_lpn_comp_lots_csr
615 LOOP
616 IF (l_debug = 1) THEN
617 mydebug('Backflush: Loop wip_lpn_comp_lots_rec : call wms_wip_integration.insert_lot');
618 END IF;
619 wms_wip_integration.insert_lot
620 (p_header_id => l_temp_header_id,
621 p_lot_number => wip_lpn_comp_lots_rec.lot_number,
622 x_return_status => l_return_status,
623 x_msg_count => x_msg_count,
624 x_msg_data => x_msg_data);
625
626 END LOOP;
627
628 IF (l_debug = 1) THEN
629 mydebug('Backflush: Loop thru wip_lpn_comp_serials_csr ');
630 END IF;
631
632 OPEN wip_lpn_comp_serials_csr;
633 LOOP
634 FETCH
635 wip_lpn_comp_serials_csr
636 INTO
637 l_temp2_header_id,
638 l_lot_number,
639 l_fm_serial_number;
640 EXIT when wip_lpn_comp_serials_csr%NOTFOUND;
641
642 IF (l_debug = 1) THEN
643 mydebug('Backflush: lot and serial item');
644 mydebug('Backflush: ' || l_temp2_header_id || ' ' || l_lot_number || ' ' || l_fm_serial_number);
645 END IF;
646
647 wms_wip_integration.update_serial
648 (p_header_id => l_temp2_header_id,
649 p_serial_number => l_fm_serial_number,
650 x_return_status => l_return_status,
651 x_msg_count => x_msg_count,
652 x_msg_data => x_msg_data);
653
654
655 -- FOR LOT and SERIAL controlled items
656
657 --Get value from sequence for next serial_transaction_temp_id
658 SELECT mtl_material_transactions_s.NEXTVAL
659 INTO l_next_ser_tran_temp_id
660 FROM dual;
661
662 --Update MTLT line
663 UPDATE mtl_transaction_lots_temp
664 SET serial_transaction_temp_id = l_next_ser_tran_temp_id
665 WHERE lot_number = l_lot_number
666 AND lot_number is not null;
667
668 --Insert into MSNT table
669 INSERT INTO mtl_serial_numbers_temp
670 ( transaction_temp_id,
671 last_update_date,
672 last_updated_by,
673 creation_date,
674 created_by,
675 last_update_login,
676 request_id,
677 program_application_id,
678 program_id,
679 program_update_date,
680 fm_serial_number,
681 to_serial_number,
682 serial_prefix,
683 parent_serial_number,
684 error_code,
685 -- transaction_quantity,
686 serial_attribute_category,
687 origination_date,
688 C_ATTRIBUTE1,
689 C_ATTRIBUTE2,
690 C_ATTRIBUTE3,
691 C_ATTRIBUTE4,
692 C_ATTRIBUTE5,
693 C_ATTRIBUTE6,
694 C_ATTRIBUTE7,
695 C_ATTRIBUTE8,
696 C_ATTRIBUTE9,
697 C_ATTRIBUTE10,
698 C_ATTRIBUTE11,
699 C_ATTRIBUTE12,
700 C_ATTRIBUTE13,
701 C_ATTRIBUTE14,
702 C_ATTRIBUTE15,
703 C_ATTRIBUTE16,
704 C_ATTRIBUTE17,
705 C_ATTRIBUTE18,
706 C_ATTRIBUTE19,
707 C_ATTRIBUTE20,
708 D_ATTRIBUTE1,
709 D_ATTRIBUTE2,
710 D_ATTRIBUTE3,
711 D_ATTRIBUTE4,
712 D_ATTRIBUTE5,
713 D_ATTRIBUTE6,
714 D_ATTRIBUTE7,
715 D_ATTRIBUTE8,
716 D_ATTRIBUTE9,
717 D_ATTRIBUTE10,
718 N_ATTRIBUTE1,
719 N_ATTRIBUTE2,
720 N_ATTRIBUTE3,
721 N_ATTRIBUTE4,
722 N_ATTRIBUTE5,
723 N_ATTRIBUTE6,
724 N_ATTRIBUTE7,
725 N_ATTRIBUTE8,
726 N_ATTRIBUTE9,
727 N_ATTRIBUTE10,
728 territory_code,
729 time_since_new,
730 cycles_since_new,
731 time_since_overhaul,
732 cycles_since_overhaul,
733 time_since_repair,
734 cycles_since_repair,
735 time_since_visit,
736 cycles_since_visit,
737 time_since_mark,
738 cycles_since_mark
739 )
740 (SELECT l_next_ser_tran_temp_id,
741 last_update_date,
742 last_updated_by,
743 creation_date,
744 created_by,
745 last_update_login,
746 request_id,
747 program_application_id,
748 program_id,
749 program_update_date,
750 fm_serial_number,
751 to_serial_number,
752 serial_prefix,
753 parent_serial_number,
754 error_code,
755 -- transaction_quantity,
756 serial_attribute_category,
757 origination_date,
758 C_ATTRIBUTE1,
759 C_ATTRIBUTE2,
760 C_ATTRIBUTE3,
761 C_ATTRIBUTE4,
762 C_ATTRIBUTE5,
763 C_ATTRIBUTE6,
764 C_ATTRIBUTE7,
765 C_ATTRIBUTE8,
766 C_ATTRIBUTE9,
767 C_ATTRIBUTE10,
768 C_ATTRIBUTE11,
769 C_ATTRIBUTE12,
770 C_ATTRIBUTE13,
771 C_ATTRIBUTE14,
772 C_ATTRIBUTE15,
773 C_ATTRIBUTE16,
774 C_ATTRIBUTE17,
775 C_ATTRIBUTE18,
776 C_ATTRIBUTE19,
777 C_ATTRIBUTE20,
778 D_ATTRIBUTE1,
779 D_ATTRIBUTE2,
780 D_ATTRIBUTE3,
781 D_ATTRIBUTE4,
782 D_ATTRIBUTE5,
783 D_ATTRIBUTE6,
784 D_ATTRIBUTE7,
785 D_ATTRIBUTE8,
786 D_ATTRIBUTE9,
787 D_ATTRIBUTE10,
788 N_ATTRIBUTE1,
789 N_ATTRIBUTE2,
790 N_ATTRIBUTE3,
791 N_ATTRIBUTE4,
792 N_ATTRIBUTE5,
793 N_ATTRIBUTE6,
794 N_ATTRIBUTE7,
795 N_ATTRIBUTE8,
796 N_ATTRIBUTE9,
797 N_ATTRIBUTE10,
798 territory_code,
799 time_since_new,
800 cycles_since_new,
801 time_since_overhaul,
802 cycles_since_overhaul,
803 time_since_repair,
804 cycles_since_repair,
805 time_since_visit,
806 cycles_since_visit,
807 time_since_mark,
808 cycles_since_mark
809 FROM wip_lpn_completions_serials
810 WHERE header_id = l_temp2_header_id
811 AND lot_number = l_lot_number);
812
813 END LOOP;
814 CLOSE wip_lpn_comp_serials_csr;
815
816
817 END LOOP;
818 CLOSE wip_lpn_completions_csr;
819
820 IF (l_debug = 1) THEN
821 mydebug('Backflush: Before calling transaction manager');
822 END IF;
823
824 -- Call the txn processor
825 l_txn_ret := inv_lpn_trx_pub.process_lpn_trx
826 (p_trx_hdr_id => l_next_transaction_header_id,
827 p_commit => fnd_api.g_false,
828 p_proc_mode => 1,
829 x_proc_msg => l_msg_data
830 );
831
832 IF (l_debug = 1) THEN
833 mydebug('Backflush: After Calling txn proc ' || l_txn_ret);
834 END IF;
835
836 --COMMIT;
837 IF l_txn_ret<>0 THEN
838 FND_MESSAGE.SET_NAME('WMS','WMS_TD_TXNMGR_ERROR' );
839 FND_MSG_PUB.ADD;
840
841 l_return_status:=FND_API.G_RET_STS_ERROR;
842 fnd_msg_pub.count_and_get
843 ( p_count => x_msg_count
844 , p_data => x_msg_data
845 );
846 ELSE
847
848 l_return_status:= FND_API.G_RET_STS_SUCCESS;
849
850 END IF;
851
852 ELSE
853 -- No rows found in wip_lpn_completions
854 l_return_status:=FND_API.G_RET_STS_ERROR;
855 fnd_msg_pub.count_and_get
856 ( p_count => x_msg_count
857 , p_data => x_msg_data
858 );
859 END IF;
860
861 x_return_status:=l_return_status;
862
863 EXCEPTION
864
865 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
866 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
867 fnd_msg_pub.count_and_get
868 (p_count => x_msg_count
869 , p_data => x_msg_data
870 );
871
872 WHEN OTHERS THEN
873 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
874 fnd_msg_pub.count_and_get
875 (p_count => x_msg_count
876 , p_data => x_msg_data
877 );
878
879 END Backflush;
880
881
882 PROCEDURE Capture_serial_atts
883 (p_ref_id IN NUMBER,
884 p_temp_id IN NUMBER,
885 p_last_update_date IN DATE,
886 p_last_updated_by IN NUMBER,
887 p_creation_date IN DATE,
888 p_created_by IN NUMBER,
889 p_fm_serial_number IN VARCHAR2,
890 p_to_serial_number IN VARCHAR2,
891 p_serial_temp_id IN NUMBER,
892 p_serial_flag IN NUMBER)
893
894 IS
895 l_serial_temp_id NUMBER;
896
897 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
898 BEGIN
899 --Insert into MSNT attributes from WIP tables
900
901 IF p_serial_flag = 2 THEN
902 l_serial_temp_id := p_serial_temp_id;
903 ELSE
904 l_serial_temp_id := p_temp_id;
905 END IF;
906
907 INSERT INTO mtl_serial_numbers_temp
908 ( transaction_temp_id,
909 last_update_date,
910 last_updated_by,
911 creation_date,
912 created_by,
913 last_update_login,
914 request_id,
915 program_application_id,
916 program_id,
917 program_update_date,
918 fm_serial_number,
919 to_serial_number,
920 serial_prefix,
921 parent_serial_number,
922 error_code,
923 serial_attribute_category,
924 origination_date,
925 C_ATTRIBUTE1,
926 C_ATTRIBUTE2,
927 C_ATTRIBUTE3,
928 C_ATTRIBUTE4,
929 C_ATTRIBUTE5,
930 C_ATTRIBUTE6,
931 C_ATTRIBUTE7,
932 C_ATTRIBUTE8,
933 C_ATTRIBUTE9,
934 C_ATTRIBUTE10,
935 C_ATTRIBUTE11,
936 C_ATTRIBUTE12,
937 C_ATTRIBUTE13,
938 C_ATTRIBUTE14,
939 C_ATTRIBUTE15,
940 C_ATTRIBUTE16,
941 C_ATTRIBUTE17,
942 C_ATTRIBUTE18,
943 C_ATTRIBUTE19,
944 C_ATTRIBUTE20,
945 D_ATTRIBUTE1,
946 D_ATTRIBUTE2,
947 D_ATTRIBUTE3,
948 D_ATTRIBUTE4,
949 D_ATTRIBUTE5,
950 D_ATTRIBUTE6,
951 D_ATTRIBUTE7,
952 D_ATTRIBUTE8,
953 D_ATTRIBUTE9,
954 D_ATTRIBUTE10,
955 N_ATTRIBUTE1,
956 N_ATTRIBUTE2,
957 N_ATTRIBUTE3,
958 N_ATTRIBUTE4,
959 N_ATTRIBUTE5,
960 N_ATTRIBUTE6,
961 N_ATTRIBUTE7,
962 N_ATTRIBUTE8,
963 N_ATTRIBUTE9,
964 N_ATTRIBUTE10,
965 territory_code,
966 time_since_new,
967 cycles_since_new,
968 time_since_overhaul,
969 cycles_since_overhaul,
970 time_since_repair,
971 cycles_since_repair,
972 time_since_visit,
973 cycles_since_visit,
974 time_since_mark,
975 cycles_since_mark
976 )
977 (SELECT l_serial_temp_id,
978 p_last_update_date,
979 p_last_updated_by,
980 p_creation_date,
981 p_created_by,
982 last_update_login,
983 request_id,
984 program_application_id,
985 program_id,
986 program_update_date,
987 p_fm_serial_number,
988 p_to_serial_number,
989 serial_prefix,
990 parent_serial_number,
991 error_code,
992 serial_attribute_category,
993 origination_date,
994 C_ATTRIBUTE1,
995 C_ATTRIBUTE2,
996 C_ATTRIBUTE3,
997 C_ATTRIBUTE4,
998 C_ATTRIBUTE5,
999 C_ATTRIBUTE6,
1000 C_ATTRIBUTE7,
1001 C_ATTRIBUTE8,
1002 C_ATTRIBUTE9,
1003 C_ATTRIBUTE10,
1004 C_ATTRIBUTE11,
1005 C_ATTRIBUTE12,
1006 C_ATTRIBUTE13,
1007 C_ATTRIBUTE14,
1008 C_ATTRIBUTE15,
1009 C_ATTRIBUTE16,
1010 C_ATTRIBUTE17,
1011 C_ATTRIBUTE18,
1012 C_ATTRIBUTE19,
1013 C_ATTRIBUTE20,
1014 D_ATTRIBUTE1,
1015 D_ATTRIBUTE2,
1016 D_ATTRIBUTE3,
1017 D_ATTRIBUTE4,
1018 D_ATTRIBUTE5,
1019 D_ATTRIBUTE6,
1020 D_ATTRIBUTE7,
1021 D_ATTRIBUTE8,
1022 D_ATTRIBUTE9,
1023 D_ATTRIBUTE10,
1024 N_ATTRIBUTE1,
1025 N_ATTRIBUTE2,
1026 N_ATTRIBUTE3,
1027 N_ATTRIBUTE4,
1028 N_ATTRIBUTE5,
1029 N_ATTRIBUTE6,
1030 N_ATTRIBUTE7,
1031 N_ATTRIBUTE8,
1032 N_ATTRIBUTE9,
1033 N_ATTRIBUTE10,
1034 territory_code,
1035 time_since_new,
1036 cycles_since_new,
1037 time_since_overhaul,
1038 cycles_since_overhaul,
1039 time_since_repair,
1040 cycles_since_repair,
1041 time_since_visit,
1042 cycles_since_visit,
1043 time_since_mark,
1044 cycles_since_mark
1045 FROM wip_lpn_completions_serials
1046 WHERE header_id = p_ref_id
1047 AND fm_serial_number = p_fm_serial_number);
1048 END Capture_serial_atts;
1049
1050
1051
1052 PROCEDURE Capture_lot_atts
1053 (p_ref_id IN NUMBER,
1054 p_temp_id IN NUMBER,
1055 p_lot IN VARCHAR2)
1056
1057 IS
1058 c1 VARCHAR2(150);
1059 c2 VARCHAR2(150);
1060 c3 VARCHAR2(150);
1061 c4 VARCHAR2(150);
1062 c5 VARCHAR2(150);
1063 c6 VARCHAR2(150);
1064 c7 VARCHAR2(150);
1065 c8 VARCHAR2(150);
1066 c9 VARCHAR2(150);
1067 c10 VARCHAR2(150);
1068 c11 VARCHAR2(150);
1069 c12 VARCHAR2(150);
1070 c13 VARCHAR2(150);
1071 c14 VARCHAR2(150);
1072 c15 VARCHAR2(150);
1073 c16 VARCHAR2(150);
1074 c17 VARCHAR2(150);
1075 c18 VARCHAR2(150);
1076 c19 VARCHAR2(150);
1077 c20 VARCHAR2(150);
1078 d1 DATE;
1079 d2 DATE;
1080 d3 DATE;
1081 d4 DATE;
1082 d5 DATE;
1083 d6 DATE;
1084 d7 DATE;
1085 d8 DATE;
1086 d9 DATE;
1087 n1 NUMBER;
1088 n2 NUMBER;
1089 n3 NUMBER;
1090 n4 NUMBER;
1091 n5 NUMBER;
1092 n6 NUMBER;
1093 n7 NUMBER;
1094 n8 NUMBER;
1095 n9 NUMBER;
1096 n10 NUMBER;
1097 n11 NUMBER;
1098 d10 DATE;
1099 n12 NUMBER;
1100 d11 DATE;
1101 n13 NUMBER;
1102 n14 NUMBER;
1103 n15 NUMBER;
1104 n16 NUMBER;
1105 n17 NUMBER;
1106 d12 DATE;
1107 n18 NUMBER;
1108 d13 DATE;
1109 v1 VARCHAR2(240);
1110 v2 VARCHAR2(240);
1111 n20 NUMBER;
1112 v4 VARCHAR2(150);
1113 d14 DATE;
1114 v5 VARCHAR2(240);
1115 v6 VARCHAR2(240);
1116 n21 NUMBER;
1117 d15 DATE;
1118 d16 DATE;
1119 n22 NUMBER;
1120 v7 VARCHAR2(150);
1121 n23 NUMBER;
1122 v8 VARCHAR2(3);
1123 v9 VARCHAR2(150);
1124 d17 DATE;
1125 d18 DATE;
1126 n24 NUMBER;
1127 v10 VARCHAR2(3);
1128 n25 NUMBER;
1129 n26 NUMBER;
1130 v11 VARCHAR2(3);
1131 n27 NUMBER;
1132 v12 VARCHAR2(3);
1133 v13 VARCHAR2(150);
1134 v14 VARCHAR2(240);
1135 v15 VARCHAR2(150);
1136 v16 VARCHAR2(30);
1137 -- bug 12708745 start
1138 -- additional lot attrs
1139 n28 NUMBER;
1140 v17 VARCHAR2(32);
1141 d19 DATE;
1142 d20 DATE;
1143 -- bug 12708745 end
1144 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1145 BEGIN
1146
1147 --Update MTLT attributes from WIP tables
1148
1149
1150 SELECT C_ATTRIBUTE1,
1151 C_ATTRIBUTE2,
1152 C_ATTRIBUTE3,
1153 C_ATTRIBUTE4,
1154 C_ATTRIBUTE5,
1155 C_ATTRIBUTE6,
1156 C_ATTRIBUTE7,
1157 C_ATTRIBUTE8,
1158 C_ATTRIBUTE9,
1159 C_ATTRIBUTE10,
1160 C_ATTRIBUTE11,
1161 C_ATTRIBUTE12,
1162 C_ATTRIBUTE13,
1163 C_ATTRIBUTE14,
1164 C_ATTRIBUTE15,
1165 C_ATTRIBUTE16,
1166 C_ATTRIBUTE17,
1167 C_ATTRIBUTE18,
1168 C_ATTRIBUTE19,
1169 C_ATTRIBUTE20,
1170 D_ATTRIBUTE1,
1171 D_ATTRIBUTE2,
1172 D_ATTRIBUTE3,
1173 D_ATTRIBUTE4,
1174 D_ATTRIBUTE5,
1175 D_ATTRIBUTE6,
1176 D_ATTRIBUTE7,
1177 D_ATTRIBUTE8,
1178 D_ATTRIBUTE9,
1179 N_ATTRIBUTE1,
1180 N_ATTRIBUTE2,
1181 N_ATTRIBUTE3,
1182 N_ATTRIBUTE4,
1183 N_ATTRIBUTE5,
1184 N_ATTRIBUTE6,
1185 N_ATTRIBUTE7,
1186 N_ATTRIBUTE8,
1187 N_ATTRIBUTE9,
1188 N_ATTRIBUTE10,
1189 LAST_UPDATE_DATE,
1190 LAST_UPDATED_BY,
1191 CREATION_DATE,
1192 CREATED_BY,
1193 LAST_UPDATE_LOGIN,
1194 REQUEST_ID ,
1195 PROGRAM_APPLICATION_ID ,
1196 PROGRAM_ID,
1197 PROGRAM_UPDATE_DATE,
1198 TRANSACTION_QUANTITY,
1199 LOT_EXPIRATION_DATE,
1200 ERROR_CODE,
1201 LOT_ATTRIBUTE_CATEGORY,
1202 VENDOR_ID,
1203 GRADE_CODE ,
1204 ORIGINATION_DATE,
1205 DATE_CODE,
1206 CHANGE_DATE,
1207 AGE,
1208 RETEST_DATE,
1209 MATURITY_DATE,
1210 ITEM_SIZE,
1211 COLOR,
1212 VOLUME,
1213 VOLUME_UOM,
1214 PLACE_OF_ORIGIN,
1215 BEST_BY_DATE,
1216 LENGTH,
1217 LENGTH_UOM,
1218 RECYCLED_CONTENT,
1219 THICKNESS,
1220 THICKNESS_UOM,
1221 WIDTH,
1222 WIDTH_UOM,
1223 CURL_WRINKLE_FOLD,
1224 VENDOR_NAME,
1225 SUPPLIER_LOT_NUMBER,
1226 TERRITORY_CODE,
1227 --bug 12708745
1228 -- capturing following lot attributes
1229 ORIGINATION_TYPE,
1230 EXPIRATION_ACTION_CODE,
1231 EXPIRATION_ACTION_DATE,
1232 HOLD_DATE
1233 INTO c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,
1234 c17,c18,c19,c20,d1,d2,d3,d4,d5,d6,d7,d8,d9,n1,n2,n3,n4,
1235 n5,n6,n7,n8,n9,n10,
1236 d10,n12,d11,n13,n14,n15,n16,n17,d12,n18,d13,
1237 v1,v2,n20,v4,d14,v5,v6,n21,d15,d16,n22,v7,n23,v8,
1238 v9,d18,n24,v10,n25,n26,v11,n27,v12,v13,v14,
1239 v15,v16,
1240 n28,v17,d19,d20 -- bug 12708745
1241
1242 FROM wip_lpn_completions_lots
1243 WHERE header_id = p_ref_id
1244 AND lot_number = p_lot;
1245
1246
1247
1248 UPDATE mtl_transaction_lots_temp
1249 SET C_ATTRIBUTE1 = c1,
1250 C_ATTRIBUTE2 = c2,
1251 C_ATTRIBUTE3 = c3,
1252 C_ATTRIBUTE4 = c4,
1253 C_ATTRIBUTE5 = c5,
1254 C_ATTRIBUTE6 = c6,
1255 C_ATTRIBUTE7 = c7,
1256 C_ATTRIBUTE8 = c8,
1257 C_ATTRIBUTE9 = c9,
1258 C_ATTRIBUTE10 = c10,
1259 C_ATTRIBUTE11 = c11,
1260 C_ATTRIBUTE12 = c12,
1261 C_ATTRIBUTE13 = c13,
1262 C_ATTRIBUTE14 = c14,
1263 C_ATTRIBUTE15 = c15,
1264 C_ATTRIBUTE16 = c16,
1265 C_ATTRIBUTE17 = c17,
1266 C_ATTRIBUTE18 = c18,
1267 C_ATTRIBUTE19 = c19,
1268 C_ATTRIBUTE20 = c20,
1269 D_ATTRIBUTE1 = d1,
1270 D_ATTRIBUTE2 = d2,
1271 D_ATTRIBUTE3 = d3,
1272 D_ATTRIBUTE4 = d4,
1273 D_ATTRIBUTE5 = d5,
1274 D_ATTRIBUTE6 = d6,
1275 D_ATTRIBUTE7 = d7,
1276 D_ATTRIBUTE8 = d8,
1277 D_ATTRIBUTE9 = d9,
1278 N_ATTRIBUTE1 = n1,
1279 N_ATTRIBUTE2 = n2,
1280 N_ATTRIBUTE3 = n3,
1281 N_ATTRIBUTE4 = n4,
1282 N_ATTRIBUTE5 = n5,
1283 N_ATTRIBUTE6 = n6,
1284 N_ATTRIBUTE7 = n7,
1285 N_ATTRIBUTE8 = n8,
1286 N_ATTRIBUTE9 = n9,
1287 N_ATTRIBUTE10 = n10,
1288 LAST_UPDATE_DATE = d10,
1289 LAST_UPDATED_BY=n12,
1290 CREATION_DATE=d11,
1291 CREATED_BY=n13,
1292 LAST_UPDATE_LOGIN=n14,
1293 REQUEST_ID =n15,
1294 PROGRAM_APPLICATION_ID =n16,
1295 PROGRAM_ID=n17,
1296 PROGRAM_UPDATE_DATE=d12,
1297 -- bug 2748242
1298 --TRANSACTION_QUANTITY=n18,
1299 LOT_EXPIRATION_DATE=d13,
1300 ERROR_CODE=v1,
1301 LOT_ATTRIBUTE_CATEGORY=v2,
1302 VENDOR_ID=n20,
1303 GRADE_CODE =v4,
1304 ORIGINATION_DATE=d14,
1305 DATE_CODE=v5,
1306 CHANGE_DATE=v6,
1307 AGE=n21,
1308 RETEST_DATE=d15,
1309 MATURITY_DATE=d16,
1310 ITEM_SIZE=n22,
1311 COLOR=v7,
1312 VOLUME=n23,
1313 VOLUME_UOM=v8,
1314 PLACE_OF_ORIGIN=v9,
1315 BEST_BY_DATE=d18,
1316 LENGTH=n24,
1317 LENGTH_UOM=v10,
1318 RECYCLED_CONTENT=n25,
1319 THICKNESS=n26,
1320 THICKNESS_UOM=v11,
1321 WIDTH=n27,
1322 WIDTH_UOM=v12,
1323 CURL_WRINKLE_FOLD=v13,
1324 VENDOR_NAME=v14,
1325 SUPPLIER_LOT_NUMBER=v15,
1326 TERRITORY_CODE=v16,
1327 -- bug 12708745
1328 ORIGINATION_TYPE=n28,
1329 EXPIRATION_ACTION_CODE=v17,
1330 EXPIRATION_ACTION_DATE=d19,
1331 HOLD_DATE=d20
1332 WHERE transaction_temp_id=p_temp_id
1333 AND lot_number=p_lot;
1334
1335
1336 END Capture_lot_atts;
1337
1338
1339
1340 PROCEDURE Update_serial
1341 ( p_header_id IN NUMBER
1342 , p_serial_number IN VARCHAR2
1343 , x_return_status OUT NOCOPY VARCHAR2
1344 , x_msg_count OUT NOCOPY NUMBER
1345 , x_msg_data OUT NOCOPY VARCHAR2
1346 )
1347
1348 IS
1349 v1 VARCHAR2(30);
1350 v2 VARCHAR2(30);
1351 v3 VARCHAR2(30);
1352 v4 VARCHAR2(150);
1353 v5 VARCHAR2(30);
1354 v6 VARCHAR2(150);
1355 v7 VARCHAR2(150);
1356 v8 VARCHAR2(150);
1357 v9 VARCHAR2(150);
1358 v10 VARCHAR2(150);
1359 v11 VARCHAR2(150);
1360 v12 VARCHAR2(150);
1361 v13 VARCHAR2(150);
1362 v14 VARCHAR2(150);
1363 v15 VARCHAR2(150);
1364 v16 VARCHAR2(150);
1365 v17 VARCHAR2(150);
1366 v18 VARCHAR2(150);
1367 v19 VARCHAR2(150);
1368 v20 VARCHAR2(150);
1369 v21 VARCHAR2(150);
1370 v22 VARCHAR2(150);
1371 v23 VARCHAR2(150);
1372 v24 VARCHAR2(150);
1373 v25 VARCHAR2(30);
1374 d1 DATE;
1375 d2 DATE;
1376 d3 DATE;
1377 d4 DATE;
1378 d5 DATE;
1379 d6 DATE;
1380 d7 DATE;
1381 d8 DATE;
1382 d9 DATE;
1383 d10 DATE;
1384 d11 DATE;
1385 d12 DATE;
1386 d13 DATE;
1387 d14 DATE;
1388 n1 NUMBER;
1389 n2 NUMBER;
1390 n3 NUMBER;
1391 n4 NUMBER;
1392 n5 NUMBER;
1393 n6 NUMBER;
1394 n7 NUMBER;
1395 n8 NUMBER;
1396 n9 NUMBER;
1397 n10 NUMBER;
1398 n11 NUMBER;
1399 n12 NUMBER;
1400 n13 NUMBER;
1401 n14 NUMBER;
1402 n15 NUMBER;
1403 n16 NUMBER;
1404 n17 NUMBER;
1405 n18 NUMBER;
1406 n19 NUMBER;
1407 n20 NUMBER;
1408 n21 NUMBER;
1409 n22 NUMBER;
1410 n23 NUMBER;
1411 n24 NUMBER;
1412 n25 NUMBER;
1413 n26 NUMBER;
1414 n27 NUMBER;
1415
1416 l_item_id NUMBER;
1417 l_return_status VARCHAR2(1);
1418 l_object_id NUMBER;
1419
1420 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1421
1422 BEGIN
1423
1424 l_return_status := FND_API.G_RET_STS_SUCCESS;
1425
1426 SELECT inventory_item_id
1427 INTO l_item_id
1428 FROM wip_lpn_completions
1429 WHERE header_id = p_header_id;
1430
1431 SELECT
1432 LAST_UPDATE_DATE,
1433 LAST_UPDATED_BY,
1434 CREATION_DATE,
1435 CREATED_BY,
1436 LAST_UPDATE_LOGIN,
1437 REQUEST_ID,
1438 PROGRAM_APPLICATION_ID,
1439 PROGRAM_ID,
1440 PROGRAM_UPDATE_DATE,
1441 FM_SERIAL_NUMBER,
1442 PARENT_SERIAL_NUMBER,
1443 LOT_NUMBER,
1444 C_ATTRIBUTE1,
1445 SERIAL_ATTRIBUTE_CATEGORY,
1446 ORIGINATION_DATE,
1447 C_ATTRIBUTE2,
1448 C_ATTRIBUTE3,
1449 C_ATTRIBUTE4,
1450 C_ATTRIBUTE5,
1451 C_ATTRIBUTE6,
1452 C_ATTRIBUTE7,
1453 C_ATTRIBUTE8,
1454 C_ATTRIBUTE9,
1455 C_ATTRIBUTE10,
1456 C_ATTRIBUTE11,
1457 C_ATTRIBUTE12,
1458 C_ATTRIBUTE13,
1459 C_ATTRIBUTE14,
1460 C_ATTRIBUTE15,
1461 C_ATTRIBUTE16,
1462 C_ATTRIBUTE17,
1463 C_ATTRIBUTE18,
1464 C_ATTRIBUTE19,
1465 C_ATTRIBUTE20,
1466 D_ATTRIBUTE1,
1467 D_ATTRIBUTE2,
1468 D_ATTRIBUTE3,
1469 D_ATTRIBUTE4,
1470 D_ATTRIBUTE5,
1471 D_ATTRIBUTE6,
1472 D_ATTRIBUTE7,
1473 D_ATTRIBUTE8,
1474 D_ATTRIBUTE9,
1475 D_ATTRIBUTE10,
1476 N_ATTRIBUTE1,
1477 N_ATTRIBUTE2,
1478 N_ATTRIBUTE3,
1479 N_ATTRIBUTE4,
1480 N_ATTRIBUTE5,
1481 N_ATTRIBUTE6,
1482 N_ATTRIBUTE7,
1483 N_ATTRIBUTE8,
1484 N_ATTRIBUTE9,
1485 N_ATTRIBUTE10,
1486 TERRITORY_CODE,
1487 TIME_SINCE_NEW,
1488 CYCLES_SINCE_NEW,
1489 TIME_SINCE_OVERHAUL,
1490 CYCLES_SINCE_OVERHAUL,
1491 TIME_SINCE_REPAIR,
1492 CYCLES_SINCE_REPAIR,
1493 TIME_SINCE_VISIT,
1494 CYCLES_SINCE_VISIT,
1495 TIME_SINCE_MARK,
1496 CYCLES_SINCE_MARK,
1497 STATUS_ID
1498
1499 INTO d1,n1,d2,n2,n3,n4,n5,n6,d3,v1,v2,v3,v4,v5,d4,
1500 v6,v7,v8,v9,v10,v11,v12,v13,v14,v15,v16,v17,v18,v19,v20,v21,
1501 v22,v23,v24,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,
1502 n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,v25,n17,n18,n19,
1503 n20,n21,n22,n23,n24,n25,n26,n27
1504
1505 FROM wip_lpn_completions_serials
1506 WHERE fm_serial_number = p_serial_number
1507 AND header_id = p_header_id;
1508
1509 select gen_object_id
1510 into l_object_id
1511 From mtl_serial_numbers
1512 where serial_number = p_serial_number
1513 and inventory_item_id = l_item_id;
1514
1515 if( l_object_id is null ) then
1516 select mtl_gen_object_id_s.nextval into l_object_id from dual;
1517 end if;
1518
1519 UPDATE mtl_serial_numbers
1520 SET
1521 LAST_UPDATE_DATE=d1,
1522 LAST_UPDATED_BY=n1,
1523 CREATION_DATE=d2,
1524 CREATED_BY=n2,
1525 LAST_UPDATE_LOGIN=n3,
1526 REQUEST_ID=n4,
1527 PROGRAM_APPLICATION_ID=n5,
1528 PROGRAM_ID=n6,
1529 PROGRAM_UPDATE_DATE=d3,
1530 PARENT_SERIAL_NUMBER=v2,
1531 LOT_NUMBER=v3,
1532 C_ATTRIBUTE1=v4,
1533 SERIAL_ATTRIBUTE_CATEGORY=v5,
1534 ORIGINATION_DATE=d4,
1535 C_ATTRIBUTE2=v6,
1536 C_ATTRIBUTE3=v7,
1537 C_ATTRIBUTE4=v8,
1538 C_ATTRIBUTE5=v9,
1539 C_ATTRIBUTE6=v10,
1540 C_ATTRIBUTE7=v11,
1541 C_ATTRIBUTE8=v12,
1542 C_ATTRIBUTE9=v13,
1543 C_ATTRIBUTE10=v14,
1544 C_ATTRIBUTE11=v15,
1545 C_ATTRIBUTE12=v16,
1546 C_ATTRIBUTE13=v17,
1547 C_ATTRIBUTE14=v18,
1548 C_ATTRIBUTE15=v19,
1549 C_ATTRIBUTE16=v20,
1550 C_ATTRIBUTE17=v21,
1551 C_ATTRIBUTE18=v22,
1552 C_ATTRIBUTE19=v23,
1553 C_ATTRIBUTE20=v24,
1554 D_ATTRIBUTE1=d5,
1555 D_ATTRIBUTE2=d6,
1556 D_ATTRIBUTE3=d7,
1557 D_ATTRIBUTE4=d8,
1558 D_ATTRIBUTE5=d9,
1559 D_ATTRIBUTE6=d10,
1560 D_ATTRIBUTE7=d11,
1561 D_ATTRIBUTE8=d12,
1562 D_ATTRIBUTE9=d13,
1563 D_ATTRIBUTE10=d14,
1564 N_ATTRIBUTE1=n7,
1565 N_ATTRIBUTE2=n8,
1566 N_ATTRIBUTE3=n9,
1567 N_ATTRIBUTE4=n10,
1568 N_ATTRIBUTE5=n11,
1569 N_ATTRIBUTE6=n12,
1570 N_ATTRIBUTE7=n13,
1571 N_ATTRIBUTE8=n14,
1572 N_ATTRIBUTE9=n15,
1573 N_ATTRIBUTE10=n16,
1574 TERRITORY_CODE=v25,
1575 TIME_SINCE_NEW=n17,
1576 CYCLES_SINCE_NEW=n18,
1577 TIME_SINCE_OVERHAUL=n19,
1578 CYCLES_SINCE_OVERHAUL=n20,
1579 TIME_SINCE_REPAIR=n21,
1580 CYCLES_SINCE_REPAIR=n22,
1581 TIME_SINCE_VISIT=n23,
1582 CYCLES_SINCE_VISIT=n24,
1583 TIME_SINCE_MARK=n25,
1584 CYCLES_SINCE_MARK=n26,
1585 GEN_OBJECT_ID = l_object_id,
1586 STATUS_ID = n27
1587 WHERE serial_number = p_serial_number
1588 AND inventory_item_id = l_item_id;
1589
1590 x_return_status := l_return_status;
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 x_return_status := FND_API.G_RET_STS_ERROR;
1594
1595 IF (l_debug = 1) THEN
1596 mydebug('Update_serial: Unexpected error: ' || sqlcode || ' :: ' || sqlerrm);
1597 END IF;
1598
1599 END Update_serial;
1600
1601
1602 PROCEDURE Insert_lot
1603 ( p_header_id IN NUMBER,
1604 p_lot_number IN VARCHAR2,
1605 x_return_status OUT NOCOPY VARCHAR2,
1606 x_msg_count OUT NOCOPY NUMBER,
1607 x_msg_data OUT NOCOPY VARCHAR2)
1608
1609 IS
1610
1611 l_item_id NUMBER;
1612 l_org_id NUMBER;
1613 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
1614 l_lot_number VARCHAR2(80);
1615 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1616 l_msg_count NUMBER;
1617 l_msg_data VARCHAR2(2000);
1618 l_object_id NUMBER;
1619 l_status_rec inv_material_status_pub.mtl_status_update_rec_type; --bug4073725
1620 l_status_id NUMBER;--bug4073725
1621
1622 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1623 BEGIN
1624
1625 l_return_status := FND_API.G_RET_STS_SUCCESS;
1626
1627 l_lot_number := p_lot_number;
1628
1629 SELECT inventory_item_id,organization_id
1630 INTO l_item_id,l_org_id
1631 FROM wip_lpn_completions
1632 WHERE header_id = p_header_id;
1633
1634 WMS_WIP_Integration.perform_lot_validations
1635 (p_item_id => l_item_id,
1636 p_org_id => l_org_id,
1637 p_lot_number => l_lot_number,
1638 x_return_status => l_return_status,
1639 x_msg_count => l_msg_count,
1640 x_msg_data => l_msg_data);
1641
1642 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1643
1644 -- Lot number does not exist
1645 select mtl_gen_object_id_s.nextval into l_object_id from dual;
1646
1647 INSERT INTO mtl_lot_numbers
1648 (INVENTORY_ITEM_ID
1649 ,ORGANIZATION_ID
1650 ,LOT_NUMBER
1651 ,LAST_UPDATE_DATE
1652 ,LAST_UPDATED_BY
1653 ,CREATION_DATE
1654 ,CREATED_BY
1655 ,LAST_UPDATE_LOGIN
1656 ,EXPIRATION_DATE
1657 ,REQUEST_ID
1658 ,PROGRAM_APPLICATION_ID
1659 ,PROGRAM_ID
1660 ,PROGRAM_UPDATE_DATE
1661 ,DESCRIPTION
1662 ,VENDOR_ID
1663 ,GRADE_CODE
1664 ,ORIGINATION_DATE
1665 ,DATE_CODE
1666 ,CHANGE_DATE
1667 ,AGE
1668 ,RETEST_DATE
1669 ,MATURITY_DATE
1670 ,LOT_ATTRIBUTE_CATEGORY
1671 ,ITEM_SIZE
1672 ,COLOR
1673 ,VOLUME
1674 ,VOLUME_UOM
1675 ,PLACE_OF_ORIGIN
1676 ,BEST_BY_DATE
1677 ,LENGTH
1678 ,LENGTH_UOM
1679 ,RECYCLED_CONTENT
1680 ,THICKNESS
1681 ,THICKNESS_UOM
1682 ,WIDTH
1683 ,WIDTH_UOM
1684 ,CURL_WRINKLE_FOLD
1685 ,C_ATTRIBUTE1
1686 ,C_ATTRIBUTE2
1687 ,C_ATTRIBUTE3
1688 ,C_ATTRIBUTE4
1689 ,C_ATTRIBUTE5
1690 ,C_ATTRIBUTE6
1691 ,C_ATTRIBUTE7
1692 ,C_ATTRIBUTE8
1693 ,C_ATTRIBUTE9
1694 ,C_ATTRIBUTE10
1695 ,C_ATTRIBUTE11
1696 ,C_ATTRIBUTE12
1697 ,C_ATTRIBUTE13
1698 ,C_ATTRIBUTE14
1699 ,C_ATTRIBUTE15
1700 ,C_ATTRIBUTE16
1701 ,C_ATTRIBUTE17
1702 ,C_ATTRIBUTE18
1703 ,C_ATTRIBUTE19
1704 ,C_ATTRIBUTE20
1705 ,D_ATTRIBUTE1
1706 ,D_ATTRIBUTE2
1707 ,D_ATTRIBUTE3
1708 ,D_ATTRIBUTE4
1709 ,D_ATTRIBUTE5
1710 ,D_ATTRIBUTE6
1711 ,D_ATTRIBUTE7
1712 ,D_ATTRIBUTE8
1713 ,D_ATTRIBUTE9
1714 ,D_ATTRIBUTE10
1715 ,N_ATTRIBUTE1
1716 ,N_ATTRIBUTE2
1717 ,N_ATTRIBUTE3
1718 ,N_ATTRIBUTE4
1719 ,N_ATTRIBUTE5
1720 ,N_ATTRIBUTE6
1721 ,N_ATTRIBUTE7
1722 ,N_ATTRIBUTE8
1723 ,N_ATTRIBUTE10
1724 ,VENDOR_NAME
1725 ,SUPPLIER_LOT_NUMBER
1726 ,N_ATTRIBUTE9
1727 ,TERRITORY_CODE
1728 ,GEN_OBJECT_ID
1729 ,STATUS_ID
1730 --bug 12708745
1731 -- capturing following lot attributes
1732 ,ORIGINATION_TYPE
1733 ,EXPIRATION_ACTION_CODE
1734 ,EXPIRATION_ACTION_DATE
1735 ,HOLD_DATE
1736 )
1737 (SELECT
1738 l_item_id
1739 ,l_org_id
1740 ,p_lot_number
1741 ,LAST_UPDATE_DATE
1742 ,LAST_UPDATED_BY
1743 ,CREATION_DATE
1744 ,CREATED_BY
1745 ,LAST_UPDATE_LOGIN
1746 ,LOT_EXPIRATION_DATE
1747 ,REQUEST_ID
1748 ,PROGRAM_APPLICATION_ID
1749 ,PROGRAM_ID
1750 ,PROGRAM_UPDATE_DATE
1751 ,DESCRIPTION
1752 ,VENDOR_ID
1753 ,GRADE_CODE
1754 ,ORIGINATION_DATE
1755 ,DATE_CODE
1756 ,CHANGE_DATE
1757 ,AGE
1758 ,RETEST_DATE
1759 ,MATURITY_DATE
1760 ,LOT_ATTRIBUTE_CATEGORY
1761 ,ITEM_SIZE
1762 ,COLOR
1763 ,VOLUME
1764 ,VOLUME_UOM
1765 ,PLACE_OF_ORIGIN
1766 ,BEST_BY_DATE
1767 ,LENGTH
1768 ,LENGTH_UOM
1769 ,RECYCLED_CONTENT
1770 ,THICKNESS
1771 ,THICKNESS_UOM
1772 ,WIDTH
1773 ,WIDTH_UOM
1774 ,CURL_WRINKLE_FOLD
1775 ,C_ATTRIBUTE1
1776 ,C_ATTRIBUTE2
1777 ,C_ATTRIBUTE3
1778 ,C_ATTRIBUTE4
1779 ,C_ATTRIBUTE5
1780 ,C_ATTRIBUTE6
1781 ,C_ATTRIBUTE7
1782 ,C_ATTRIBUTE8
1783 ,C_ATTRIBUTE9
1784 ,C_ATTRIBUTE10
1785 ,C_ATTRIBUTE11
1786 ,C_ATTRIBUTE12
1787 ,C_ATTRIBUTE13
1788 ,C_ATTRIBUTE14
1789 ,C_ATTRIBUTE15
1790 ,C_ATTRIBUTE16
1791 ,C_ATTRIBUTE17
1792 ,C_ATTRIBUTE18
1793 ,C_ATTRIBUTE19
1794 ,C_ATTRIBUTE20
1795 ,D_ATTRIBUTE1
1796 ,D_ATTRIBUTE2
1797 ,D_ATTRIBUTE3
1798 ,D_ATTRIBUTE4
1799 ,D_ATTRIBUTE5
1800 ,D_ATTRIBUTE6
1801 ,D_ATTRIBUTE7
1802 ,D_ATTRIBUTE8
1803 ,D_ATTRIBUTE9
1804 ,D_ATTRIBUTE10
1805 ,N_ATTRIBUTE1
1806 ,N_ATTRIBUTE2
1807 ,N_ATTRIBUTE3
1808 ,N_ATTRIBUTE4
1809 ,N_ATTRIBUTE5
1810 ,N_ATTRIBUTE6
1811 ,N_ATTRIBUTE7
1812 ,N_ATTRIBUTE8
1813 ,N_ATTRIBUTE10
1814 ,VENDOR_NAME
1815 ,SUPPLIER_LOT_NUMBER
1816 ,N_ATTRIBUTE9
1817 ,territory_code
1818 ,l_object_id
1819 ,status_id
1820 --bug 12708745
1821 -- capturing following lot attributes
1822 ,ORIGINATION_TYPE
1823 ,EXPIRATION_ACTION_CODE
1824 ,EXPIRATION_ACTION_DATE
1825 ,HOLD_DATE
1826 FROM wip_lpn_completions_lots
1827 WHERE header_id = p_header_id
1828 AND lot_number = p_lot_number
1829 );
1830
1831 l_return_status := FND_API.G_RET_STS_SUCCESS;
1832 END IF;
1833 /* bug4073725 changes start */
1834 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1835 SELECT status_id
1836 INTO l_status_id
1837 FROM wip_lpn_completions_lots
1838 WHERE header_id = p_header_id
1839 AND lot_number = p_lot_number;
1840 IF ( l_status_id IS NOT NULL ) THEN
1841 l_status_rec.update_method := inv_material_status_pub.g_update_method_auto;
1842 l_status_rec.organization_id := l_org_id;
1843 l_status_rec.inventory_item_id := l_item_id;
1844 l_status_rec.lot_number := p_lot_number;
1845 l_status_rec.status_id := l_status_id;
1846 l_status_rec.initial_status_flag := 'Y';
1847 l_status_rec.from_mobile_apps_flag := 'Y';
1848 inv_material_status_pkg.insert_status_history ( l_status_rec);
1849 END IF;
1850 END IF;
1851 /* bug4073725 changes end */
1852
1853 x_return_status := l_return_status;
1854
1855 END Insert_lot;
1856
1857
1858 PROCEDURE Perform_lot_validations(
1859 p_item_id IN NUMBER,
1860 p_org_id IN NUMBER,
1861 p_lot_number IN VARCHAR2,
1862 x_return_status OUT NOCOPY VARCHAR2,
1863 x_msg_count OUT NOCOPY NUMBER,
1864 x_msg_data OUT NOCOPY VARCHAR2)
1865
1866 IS
1867 l_item_id NUMBER;
1868 l_org_id NUMBER;
1869 l_count NUMBER;
1870 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
1871 l_lot_number VARCHAR2(80);
1872 l_lotunique NUMBER;
1873 l_lot_control_code NUMBER;
1874 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1875 l_shelf_life_code NUMBER;
1876 l_expiration_date DATE;
1877
1878 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1879 BEGIN
1880
1881 l_return_status := FND_API.G_RET_STS_SUCCESS;
1882
1883 l_item_id:=p_item_id;
1884 l_org_id:=p_org_id;
1885 l_lot_number:=p_lot_number;
1886
1887 l_count:=0;
1888
1889 BEGIN
1890 SELECT lot_control_code, shelf_life_code
1891 INTO l_lot_control_code, l_shelf_life_code
1892 FROM mtl_system_items
1893 WHERE inventory_item_id = l_item_id
1894 AND organization_id = l_org_id;
1895
1896 IF l_lot_control_code = 1 THEN
1897 fnd_message.set_name('INV','INV_NO_LOT_CONTROL');
1898 fnd_msg_pub.add;
1899 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1900 END IF;
1901
1902 EXCEPTION
1903 WHEN NO_DATA_FOUND THEN
1904 fnd_message.set_name('INV','INV_INVALID_ITEM');
1905 fnd_msg_pub.add;
1906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1907 END;
1908
1909
1910 SELECT lot_number_uniqueness
1911 INTO l_lotunique
1912 FROM mtl_parameters
1913 WHERE organization_id = l_org_id;
1914
1915 IF l_lotunique = 1 then
1916 SELECT count(1)
1917 INTO l_count
1918 FROM MTL_LOT_NUMBERS
1919 WHERE inventory_item_id <> l_item_id
1920 AND lot_number = p_lot_number
1921 AND NOT EXISTS( SELECT NULL
1922 FROM mtl_lot_numbers lot
1923 WHERE lot.lot_number = p_lot_number
1924 AND lot.organization_id = l_org_id
1925 AND lot.inventory_item_id = l_item_id);
1926
1927 IF l_count > 0 then
1928 fnd_message.set_name('INV','INV_INT_LOTUNIQEXP');
1929 fnd_msg_pub.add;
1930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1931 END IF;
1932 END IF;
1933
1934 l_count := 0;
1935
1936
1937 SELECT COUNT(1)
1938 INTO l_count
1939 FROM mtl_lot_numbers
1940 WHERE inventory_item_id = l_item_id
1941 AND organization_id = l_org_id
1942 AND lot_number = p_lot_number;
1943
1944 -- Lot exists or not
1945 IF l_count = 1 THEN
1946
1947 fnd_message.set_name('INV','INV_LOT_EXISTS');
1948 fnd_msg_pub.add;
1949
1950 SELECT expiration_date
1951 INTO l_expiration_date
1952 FROM mtl_lot_numbers
1953 WHERE inventory_item_id = l_item_id
1954 AND organization_id = l_org_id
1955 AND lot_number = p_lot_number;
1956
1957
1958 IF l_shelf_life_code = 4 AND l_expiration_date < SYSDATE THEN
1959 fnd_message.set_name('INV','INV_LOT_EXPREQD');
1960 fnd_msg_pub.add;
1961 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1962
1963 ELSE
1964 l_return_status := FND_API.G_RET_STS_SUCCESS;
1965 END IF;
1966
1967 ELSE
1968 l_return_status:=FND_API.G_RET_STS_ERROR;
1969 END IF;
1970
1971 x_return_status := l_return_status;
1972
1973 EXCEPTION
1974
1975 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1976 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1977 fnd_msg_pub.count_and_get
1978 ( p_count => x_msg_count
1979 , p_data => x_msg_data
1980 );
1981
1982 WHEN OTHERS THEN
1983 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1984 fnd_msg_pub.count_and_get
1985 ( p_count => x_msg_count
1986 , p_data => x_msg_data
1987 );
1988
1989 END Perform_lot_validations;
1990
1991
1992 PROCEDURE post_completion
1993 (p_item_id IN NUMBER,
1994 p_org_id IN NUMBER,
1995 p_fm_serial_number IN VARCHAR2,
1996 p_to_serial_number IN VARCHAR2,
1997 p_quantity IN NUMBER,
1998 x_return_status OUT NOCOPY VARCHAR2,
1999 x_msg_count OUT NOCOPY NUMBER,
2000 x_msg_data OUT NOCOPY VARCHAR2
2001 )
2002 IS
2003
2004 l_current_number NUMBER;
2005 l_current_serial_number VARCHAR2(30);
2006 l_prefix VARCHAR2(30);
2007 l_quantity NUMBER;
2008 l_fm_number NUMBER;
2009 l_to_number NUMBER;
2010 l_errorcode NUMBER;
2011 l_padded_length NUMBER;
2012 l_length NUMBER;
2013
2014 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2015 BEGIN
2016 IF (l_debug = 1) THEN
2017 mydebug('post_completion: procedure post_completion begins');
2018 END IF;
2019
2020 x_return_status :=FND_API.g_ret_sts_success;
2021 l_quantity := p_quantity;
2022
2023 /* Call this API to parse the serial numbers into prefixes and numbers */
2024 IF (NOT MTL_Serial_Check.inv_serial_info
2025 (p_from_serial_number => p_fm_serial_number,
2026 p_to_serial_number => p_to_serial_number,
2027 x_prefix => l_prefix,
2028 x_quantity => l_quantity,
2029 x_from_number => l_fm_number,
2030 x_to_number => l_to_number,
2031 x_errorcode => l_errorcode)) THEN
2032
2033 IF (l_debug = 1) THEN
2034 mydebug('post_completion: Invalid serial number given in range');
2035 END IF;
2036 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SER');
2037 FND_MSG_PUB.ADD;
2038 RAISE FND_API.G_EXC_ERROR;
2039 END IF;
2040
2041 -- Check that in the case of a range of serial numbers, that the
2042 -- inputted p_quantity equals the amount of items in the serial range.
2043 IF (p_quantity IS NOT NULL) THEN
2044 IF (p_quantity <> l_quantity) THEN
2045 IF (l_debug = 1) THEN
2046 mydebug('post_completion: Range of serial numbers does not match given qty');
2047 END IF;
2048 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_X_QTY');
2049 FND_MSG_PUB.ADD;
2050 RAISE FND_API.G_EXC_ERROR;
2051 END IF;
2052 END IF;
2053
2054 l_length := length(p_fm_serial_number);
2055 l_current_serial_number := p_fm_serial_number;
2056 l_current_number := l_fm_number;
2057 LOOP
2058
2059 IF (l_debug = 1) THEN
2060 mydebug('post_completion: serial_number: ' || l_current_serial_number);
2061 END IF;
2062
2063 UPDATE mtl_serial_numbers
2064 SET
2065 group_mark_id = NULL,
2066 current_status = 5
2067 WHERE current_organization_id = p_org_id
2068 AND inventory_item_id = p_item_id
2069 AND serial_number = l_current_serial_number;
2070
2071 EXIT WHEN l_current_serial_number = p_to_serial_number;
2072
2073 /* Increment the current serial number */
2074 l_current_number := l_current_number + 1;
2075 l_padded_length := l_length - length(l_current_number);
2076 l_current_serial_number := RPAD(l_prefix, l_padded_length, '0') ||
2077 l_current_number;
2078 END LOOP;
2079
2080 IF (l_debug = 1) THEN
2081 mydebug('post_completion: procedure post_completion ends');
2082 END IF;
2083
2084 x_return_status := FND_API.g_ret_sts_success;
2085
2086 EXCEPTION
2087
2088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2089 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2090 fnd_msg_pub.count_and_get
2091 ( p_count => x_msg_count
2092 , p_data => x_msg_data
2093 );
2094
2095 WHEN OTHERS THEN
2096 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2097 fnd_msg_pub.count_and_get
2098 ( p_count => x_msg_count
2099 , p_data => x_msg_data
2100 );
2101
2102 END post_completion;
2103
2104
2105 PROCEDURE get_wip_job_info
2106 (p_temp_id IN NUMBER,
2107 p_wip_entity_type IN NUMBER,
2108 x_job OUT NOCOPY VARCHAR2,
2109 x_line OUT NOCOPY VARCHAR2,
2110 x_dept OUT NOCOPY VARCHAR2,
2111 x_operation_seq_num OUT NOCOPY NUMBER,
2112 x_start_date OUT NOCOPY DATE,
2113 x_schedule OUT NOCOPY VARCHAR2,
2114 x_assembly OUT NOCOPY VARCHAR2,
2115 x_return_status OUT NOCOPY VARCHAR2,
2116 x_msg_count OUT NOCOPY NUMBER,
2117 x_msg_data OUT NOCOPY VARCHAR2
2118 )
2119 IS
2120
2121
2122 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2123 BEGIN
2124
2125 IF (l_debug = 1) THEN
2126 mydebug('get_wip_job_info: get_wip_job_info begins');
2127 END IF;
2128 x_return_status :=FND_API.g_ret_sts_success;
2129
2130 IF p_wip_entity_type IN (1,5,6) THEN --Included 6 also for eAM-WMS Enhancement (Bug# 4093921)
2131
2132 -- Discrete job or lot based job
2133
2134 SELECT
2135 we.wip_entity_name,
2136 wl.line_code,
2137 bd.department_code,
2138 wro.operation_seq_num
2139
2140 INTO
2141 x_job,
2142 x_line,
2143 x_dept,
2144 x_operation_seq_num
2145
2146 FROM
2147 wip_entities we,
2148 wip_lines wl,
2149 bom_departments bd,
2150 wip_requirement_operations wro,
2151 wip_discrete_jobs wdj,
2152 mtl_txn_request_lines mtrl,
2153 mtl_material_transactions_temp mmtt
2154 WHERE we.wip_entity_id = wdj.wip_entity_id
2155 AND we.organization_id = wdj.organization_id
2156 AND wl.line_id (+) = wdj.line_id
2157 AND wl.organization_id (+) = wdj.organization_id
2158 AND bd.department_id (+) = wro.department_id
2159 AND wro.wip_entity_id = wdj.wip_entity_id
2160 AND wro.organization_id = wdj.organization_id
2161 AND wro.operation_seq_num = mtrl.txn_source_line_id
2162 AND wro.inventory_item_id = mtrl.inventory_item_id
2163 AND wdj.wip_entity_id = mtrl.txn_source_id
2164 AND wdj.organization_id = mtrl.organization_id
2165 AND mtrl.line_id = mmtt.move_order_line_id
2166 AND mmtt.transaction_temp_id = p_temp_id;
2167
2168 ELSIF p_wip_entity_type = 2 THEN
2169
2170 -- Repetitive schedule
2171
2172
2173 SELECT
2174 wl.line_code,
2175 bd.department_code,
2176 wro.operation_seq_num,
2177 msik.concatenated_segments,
2178 wrs.first_unit_start_date
2179
2180 INTO
2181 x_line,
2182 x_dept,
2183 x_operation_seq_num,
2184 x_assembly,
2185 x_start_date
2186
2187 FROM
2188 wip_lines wl,
2189 bom_departments bd,
2190 wip_requirement_operations wro,
2191 wip_repetitive_schedules wrs,
2192 mtl_txn_request_lines mtrl,
2193 mtl_material_transactions_temp mmtt,
2194 mtl_system_items_kfv msik,
2195 wip_entities we
2196 WHERE msik.inventory_item_id = we.primary_item_id
2197 AND msik.organization_id = we.organization_id
2198 AND we.wip_entity_id = wrs.wip_entity_id
2199 AND we.organization_id = wrs.organization_id
2200 AND wl.line_id = wrs.line_id
2201 AND wl.organization_id = wrs.organization_id
2202 AND bd.department_id (+) = wro.department_id
2203 AND wro.wip_entity_id = wrs.wip_entity_id
2204 AND wro.repetitive_schedule_id = wrs.repetitive_schedule_id
2205 AND wro.organization_id = wrs.organization_id
2206 AND wro.operation_seq_num = mtrl.txn_source_line_id
2207 AND wro.inventory_item_id = mtrl.inventory_item_id
2208 AND wrs.wip_entity_id = mtrl.txn_source_id
2209 AND wrs.repetitive_schedule_id = mtrl.reference_id
2210 AND wrs.organization_id = mtrl.organization_id
2211 AND mtrl.line_id = mmtt.move_order_line_id
2212 AND mmtt.transaction_temp_id = p_temp_id;
2213
2214
2215 ELSIF p_wip_entity_type = 4 THEN
2216
2217 -- Flow schedule
2218
2219 SELECT
2220 we.wip_entity_name,
2221 wl.line_code,
2222 bd.department_code,
2223 mtrl.txn_source_line_id
2224
2225 INTO
2226 x_schedule,
2227 x_line,
2228 x_dept,
2229 x_operation_seq_num
2230
2231 FROM
2232 wip_entities we,
2233 wip_lines wl,
2234 bom_departments bd,
2235 bom_operation_sequences bos,
2236 bom_operational_routings bor,
2237 wip_flow_schedules wfs,
2238 mtl_txn_request_lines mtrl,
2239 mtl_material_transactions_temp mmtt
2240 WHERE we.wip_entity_id = wfs.wip_entity_id
2241 AND we.organization_id = wfs.organization_id
2242 AND wl.line_id = wfs.line_id
2243 AND wl.organization_id = wfs.organization_id
2244 AND bd.department_id = bos.department_id
2245 AND bos.routing_sequence_id = bor.routing_sequence_id
2246 AND bos.operation_type = 1
2247 AND bos.effectivity_date >= sysdate
2248 AND ( wfs.alternate_routing_designator = bor.alternate_routing_designator
2249 OR (wfs.alternate_routing_designator IS NULL
2250 AND bor.alternate_routing_designator IS NULL) )
2251 AND bor.assembly_item_id = wfs.primary_item_id
2252 AND bor.organization_id = wfs.organization_id
2253 AND wfs.wip_entity_id = mtrl.txn_source_id
2254 AND wfs.organization_id = mtrl.organization_id
2255 AND mtrl.line_id = mmtt.move_order_line_id
2256 AND mmtt.transaction_temp_id = p_temp_id;
2257
2258 END IF;
2259
2260 IF (l_debug = 1) THEN
2261 mydebug('get_wip_job_info: x_job: ' || x_job);
2262 mydebug('get_wip_job_info: x_line: ' || x_line);
2263 mydebug('get_wip_job_info: x_dept: ' || x_dept);
2264 mydebug('get_wip_job_info: x_operation_seq_num: ' || x_operation_seq_num);
2265 mydebug('get_wip_job_info: x_start_date: ' || x_start_date);
2266 mydebug('get_wip_job_info: x_schedule: ' || x_schedule);
2267 mydebug('get_wip_job_info: x_assembly: ' || x_assembly);
2268 END IF;
2269
2270 IF (l_debug = 1) THEN
2271 mydebug('get_wip_job_info: Get_wip_job_info ends');
2272 END IF;
2273 x_return_status := FND_API.g_ret_sts_success;
2274
2275 EXCEPTION
2276
2277 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2278 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2279 fnd_msg_pub.count_and_get
2280 ( p_count => x_msg_count
2281 , p_data => x_msg_data
2282 );
2283
2284 WHEN OTHERS THEN
2285 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2286 fnd_msg_pub.count_and_get
2287 ( p_count => x_msg_count
2288 , p_data => x_msg_data
2289 );
2290
2291 END get_wip_job_info;
2292
2293
2294
2295 PROCEDURE get_wip_info_for_putaway
2296 (p_temp_id IN NUMBER,
2297 x_wip_entity_type OUT NOCOPY NUMBER,
2298 x_job OUT NOCOPY VARCHAR2,
2299 x_line OUT NOCOPY VARCHAR2,
2300 x_dept OUT NOCOPY VARCHAR2,
2301 x_operation_seq_num OUT NOCOPY NUMBER,
2302 x_start_date OUT NOCOPY DATE,
2303 x_schedule OUT NOCOPY VARCHAR2,
2304 x_assembly OUT NOCOPY VARCHAR2,
2305 x_wip_entity_id OUT NOCOPY NUMBER,
2306 x_return_status OUT NOCOPY VARCHAR2,
2307 x_msg_count OUT NOCOPY NUMBER,
2308 x_msg_data OUT NOCOPY VARCHAR2
2309 )
2310
2311 IS
2312
2313 l_wip_entity_type NUMBER;
2314 l_org_id NUMBER;
2315 l_wip_entity_id NUMBER;
2316
2317 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2318 BEGIN
2319
2320 IF (l_debug = 1) THEN
2321 mydebug('get_wip_info_for_putaway: get_wip_info_for_putaway begins');
2322 END IF;
2323 x_return_status :=FND_API.g_ret_sts_success;
2324
2325 x_wip_entity_type :=NULL;
2326 x_job :=NULL;
2327 x_line :=NULL;
2328 x_dept :=NULL;
2329 x_operation_seq_num :=NULL;
2330 x_start_date :=NULL;
2331 x_schedule :=NULL;
2332 x_assembly :=NULL;
2333 x_wip_entity_id :=NULL;
2334
2335
2336
2337
2338 SELECT demand_source_header_id, organization_id
2339 INTO l_wip_entity_id, l_org_id
2340 FROM mtl_material_transactions_temp
2341 WHERE transaction_temp_id = p_temp_id;
2342
2343 x_wip_entity_id := l_wip_entity_id;
2344
2345 SELECT entity_type
2346 INTO l_wip_entity_type
2347 FROM wip_entities
2348 WHERE organization_id = l_org_id
2349 AND wip_entity_id = l_wip_entity_id;
2350
2351
2352 x_wip_entity_type := l_wip_entity_type;
2353
2354 IF l_wip_entity_type = 1 OR l_wip_entity_type = 5 THEN
2355
2356 -- Discrete job or lot based job
2357
2358 SELECT
2359 we.wip_entity_name,
2360 wl.line_code,
2361 bd.department_code,
2362 wro.operation_seq_num
2363
2364 INTO
2365 x_job,
2366 x_line,
2367 x_dept,
2368 x_operation_seq_num
2369
2370 FROM
2371 wip_entities we,
2372 wip_lines wl,
2373 bom_departments bd,
2374 wip_requirement_operations wro,
2375 wip_discrete_jobs wdj,
2376 mtl_material_transactions_temp mmtt
2377 WHERE we.wip_entity_id = wdj.wip_entity_id
2378 AND we.organization_id = wdj.organization_id
2379 AND wl.line_id (+) = wdj.line_id
2380 AND wl.organization_id (+) = wdj.organization_id
2381 AND bd.department_id (+) = wro.department_id
2382 AND wro.wip_entity_id = wdj.wip_entity_id
2383 AND wro.organization_id = wdj.organization_id
2384 AND wro.operation_seq_num = mmtt.operation_seq_num
2385 AND wro.inventory_item_id = mmtt.inventory_item_id
2386 AND wdj.wip_entity_id = mmtt.demand_source_header_id
2387 AND wdj.organization_id = mmtt.organization_id
2388 AND mmtt.transaction_temp_id = p_temp_id;
2389
2390 ELSIF l_wip_entity_type = 2 THEN
2391
2392 -- Repetitive schedule
2393
2394
2395 SELECT
2396 wl.line_code,
2397 bd.department_code,
2398 wro.operation_seq_num,
2399 msik.concatenated_segments,
2400 wrs.first_unit_start_date
2401
2402 INTO
2403 x_line,
2404 x_dept,
2405 x_operation_seq_num,
2406 x_assembly,
2407 x_start_date
2408
2409 FROM
2410 wip_lines wl,
2411 bom_departments bd,
2412 wip_requirement_operations wro,
2413 wip_repetitive_schedules wrs,
2414 mtl_material_transactions_temp mmtt,
2415 mtl_system_items_kfv msik,
2416 wip_entities we
2417 WHERE msik.inventory_item_id = we.primary_item_id
2418 AND msik.organization_id = we.organization_id
2419 AND we.wip_entity_id = wrs.wip_entity_id
2420 AND we.organization_id = wrs.organization_id
2421 AND wl.line_id = wrs.line_id
2422 AND wl.organization_id = wrs.organization_id
2423 AND bd.department_id (+) = wro.department_id
2424 AND wro.repetitive_schedule_id = wrs.repetitive_schedule_id
2425 AND wro.wip_entity_id = wrs.wip_entity_id
2426 AND wro.organization_id = wrs.organization_id
2427 AND wro.operation_seq_num = mmtt.operation_seq_num
2428 AND wro.inventory_item_id = mmtt.inventory_item_id
2429 AND wrs.repetitive_schedule_id = mmtt.repetitive_line_id
2430 AND wrs.wip_entity_id = mmtt.demand_source_header_id
2431 AND wrs.organization_id = mmtt.organization_id
2432 AND mmtt.transaction_temp_id = p_temp_id;
2433
2434
2435 ELSIF l_wip_entity_type = 4 THEN
2436
2437 -- Flow schedule
2438
2439 SELECT
2440 we.wip_entity_name,
2441 wl.line_code,
2442 bd.department_code,
2443 mmtt.operation_seq_num
2444
2445 INTO
2446 x_schedule,
2447 x_line,
2448 x_dept,
2449 x_operation_seq_num
2450
2451 FROM
2452 wip_entities we,
2453 wip_lines wl,
2454 bom_departments bd,
2455 bom_operation_sequences bos,
2456 bom_operational_routings bor,
2457 wip_flow_schedules wfs,
2458 mtl_material_transactions_temp mmtt
2459 WHERE we.wip_entity_id = wfs.wip_entity_id
2460 AND we.organization_id = wfs.organization_id
2461 AND wl.line_id = wfs.line_id
2462 AND wl.organization_id = wfs.organization_id
2463 AND bd.department_id = bos.department_id
2464 AND bos.routing_sequence_id = bor.routing_sequence_id
2465 AND bos.operation_type = 1
2466 AND bos.effectivity_date >= sysdate
2467 AND ( wfs.alternate_routing_designator = bor.alternate_routing_designator
2468 OR (wfs.alternate_routing_designator IS NULL
2469 AND bor.alternate_routing_designator IS NULL) )
2470 AND bor.assembly_item_id = wfs.primary_item_id
2471 AND bor.organization_id = wfs.organization_id
2472 AND wfs.wip_entity_id = mmtt.demand_source_header_id
2473 AND wfs.organization_id = mmtt.organization_id
2474 AND mmtt.transaction_temp_id = p_temp_id;
2475
2476 END IF;
2477
2478 IF (l_debug = 1) THEN
2479 mydebug('get_wip_info_for_putaway: x_wip_entity_type: ' || x_wip_entity_type);
2480 mydebug('get_wip_info_for_putaway: x_job: ' || x_job);
2481 mydebug('get_wip_info_for_putaway: x_line: ' || x_line);
2482 mydebug('get_wip_info_for_putaway: x_dept: ' || x_dept);
2483 mydebug('get_wip_info_for_putaway: x_operation_seq_num: ' || x_operation_seq_num);
2484 mydebug('get_wip_info_for_putaway: x_start_date: ' || x_start_date);
2485 mydebug('get_wip_info_for_putaway: x_schedule: ' || x_schedule);
2486 mydebug('get_wip_info_for_putaway: x_assembly: ' || x_assembly);
2487 END IF;
2488
2489 IF (l_debug = 1) THEN
2490 mydebug('get_wip_info_for_putaway: Get_wip_info_for_putaway ends');
2491 END IF;
2492 x_return_status := FND_API.g_ret_sts_success;
2493
2494 EXCEPTION
2495
2496 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2497
2498 IF (l_debug = 1) THEN
2499 mydebug('get_wip_info_for_putaway: unexpected error: ' || Sqlerrm);
2500 END IF;
2501 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2502 fnd_msg_pub.count_and_get
2503 ( p_count => x_msg_count
2504 , p_data => x_msg_data
2505 );
2506
2507 WHEN OTHERS THEN
2508 IF (l_debug = 1) THEN
2509 mydebug('get_wip_info_for_putaway: unexpected error: ' || Sqlerrm);
2510 END IF;
2511 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2512 fnd_msg_pub.count_and_get
2513 ( p_count => x_msg_count
2514 , p_data => x_msg_data
2515 );
2516
2517 END get_wip_info_for_putaway;
2518
2519
2520
2521 PROCEDURE unallocate_material
2522 (p_wip_entity_id IN NUMBER,
2523 p_operation_seq_num IN NUMBER,
2524 p_inventory_item_id IN NUMBER,
2525 p_repetitive_schedule_id IN NUMBER := NULL,
2526 p_primary_quantity IN NUMBER,
2527 x_return_status OUT NOCOPY VARCHAR2,
2528 x_msg_data OUT NOCOPY VARCHAR2
2529 )
2530
2531 IS
2532 l_return_status VARCHAR2(1);
2533 l_msg_data VARCHAR2(2500);
2534 l_msg_count NUMBER;
2535
2536 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2537 BEGIN
2538
2539 l_return_status := FND_API.G_RET_STS_SUCCESS;
2540
2541 IF (l_debug = 1) THEN
2542 mydebug('wip_picking_pub.unallocate_material ends');
2543 END IF;
2544 wip_picking_pub.unallocate_material
2545 (p_wip_entity_id => p_wip_entity_id,
2546 p_operation_seq_num => p_operation_seq_num,
2547 p_inventory_item_id => p_inventory_item_id,
2548 p_repetitive_schedule_id=> p_repetitive_schedule_id,
2549 p_primary_quantity => p_primary_quantity,
2550 x_return_status => l_return_status,
2551 x_msg_data => l_msg_data);
2552
2553 IF (l_debug = 1) THEN
2554 mydebug('unallocate_material ends');
2555 END IF;
2556 x_return_status := l_return_status;
2557 x_msg_data := l_msg_data;
2558
2559 EXCEPTION
2560
2561 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2562 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2563 fnd_msg_pub.count_and_get
2564 ( p_count => l_msg_count
2565 , p_data => l_msg_data
2566 );
2567
2568 WHEN OTHERS THEN
2569 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2570 fnd_msg_pub.count_and_get
2571 ( p_count => l_msg_count
2572 , p_data => l_msg_data
2573 );
2574
2575
2576 END unallocate_material;
2577
2578 PROCEDURE transfer_Reservation
2579 (
2580 P_HEADER_ID IN NUMBER,
2581 P_SUBINVENTORY_CODE IN VARCHAR2,
2582 P_LOCATOR_ID IN NUMBER,
2583 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2584 X_MSG_COUNT OUT NOCOPY NUMBER,
2585 X_ERR_MSG OUT NOCOPY VARCHAR2,
2586 p_temp_id IN NUMBER)
2587 IS
2588
2589 CURSOR mtlt_csr IS
2590 SELECT mtlt.lot_number, mtlt.primary_quantity
2591 FROM
2592 mtl_material_transactions_temp mmtt,
2593 mtl_transaction_lots_temp mtlt
2594 WHERE mmtt.transaction_temp_id = p_temp_id
2595 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id;
2596
2597 l_lot_control_code NUMBER;
2598 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2599 l_lot_number VARCHAR2(80);
2600 l_lpn_id NUMBER;
2601 l_xfr_lpn_id NUMBER;
2602 l_content_lpn_id NUMBER;
2603 l_lot_primary_qty NUMBER;
2604 l_primary_qty NUMBER;
2605 l_lpn_controlled_flag NUMBER;
2606 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2607 BEGIN
2608
2609 IF (l_debug = 1) THEN
2610 mydebug('transfer_Reservation: transfer_Reservation API begins');
2611 END IF;
2612
2613 SELECT
2614 msi.lot_control_code,
2615 mmtt.primary_quantity,
2616 mmtt.transfer_lpn_id,
2617 mmtt.content_lpn_id,
2618 msin.LPN_CONTROLLED_FLAG
2619 INTO
2620 l_lot_control_code,
2621 l_primary_qty,
2622 l_xfr_lpn_id,
2623 l_content_lpn_id,
2624 l_lpn_controlled_flag
2625 FROM
2626 mtl_system_items msi,
2627 mtl_material_transactions_temp mmtt,
2628 mtl_secondary_inventories msin
2629 WHERE mmtt.transaction_temp_id = p_temp_id
2630 AND mmtt.organization_id = msi.organization_id
2631 AND mmtt.inventory_item_id = msi.inventory_item_id
2632 AND mmtt.organization_id = msin.organization_id
2633 AND mmtt.subinventory_code = msin.SECONDARY_INVENTORY_NAME;
2634
2635 IF l_content_lpn_id IS NOT NULL THEN
2636
2637 l_lpn_id := l_content_lpn_id;
2638
2639 ELSIF l_xfr_lpn_id IS NOT NULL THEN
2640
2641 l_lpn_id := l_xfr_lpn_id;
2642
2643 ELSE
2644
2645 l_lpn_id := NULL;
2646
2647 END IF;
2648
2649 IF l_lpn_controlled_flag = 2 THEN --NON LPN CONTROLLED SUBINVENTORY
2650 l_lpn_id := NULL;
2651 END IF;
2652
2653
2654 IF l_lot_control_code > 1 THEN
2655
2656 IF (l_debug = 1) THEN
2657 mydebug('transfer_Reservation: Lot controlled item');
2658 END IF;
2659
2660 OPEN mtlt_csr;
2661 LOOP
2662 FETCH mtlt_csr INTO l_lot_number, l_lot_primary_qty;
2663 EXIT WHEN mtlt_csr%notfound;
2664
2665 wma_inv_wrappers.transferReservation
2666 (P_HEADER_ID => p_header_id,
2667 P_SUBINVENTORY_CODE => p_subinventory_code,
2668 P_LOCATOR_ID => p_locator_id,
2669 p_primary_quantity => l_lot_primary_qty,
2670 p_lpn_id => l_lpn_id,
2671 p_lot_number => l_lot_number,
2672 X_RETURN_STATUS => x_return_status,
2673 X_MSG_COUNT => x_msg_count,
2674 X_ERR_MSG => x_err_msg);
2675
2676 END LOOP;
2677 CLOSE mtlt_csr;
2678
2679 ELSE
2680 IF (l_debug = 1) THEN
2681 mydebug('transfer_Reservation: Not a lot controlled item');
2682 END IF;
2683
2684 wma_inv_wrappers.transferReservation
2685 (P_HEADER_ID => p_header_id,
2686 P_SUBINVENTORY_CODE => p_subinventory_code,
2687 P_LOCATOR_ID => p_locator_id,
2688 p_primary_quantity => l_primary_qty,
2689 p_lpn_id => l_lpn_id,
2690 p_lot_number => null,
2691 X_RETURN_STATUS => x_return_status,
2692 X_MSG_COUNT => x_msg_count,
2693 X_ERR_MSG => x_err_msg);
2694
2695 END IF;
2696
2697 IF (l_debug = 1) THEN
2698 mydebug('transfer_Reservation: transfer_Reservation API complete');
2699 END IF;
2700
2701 EXCEPTION
2702
2703 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2704 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2705 fnd_msg_pub.count_and_get
2706 ( p_count => x_msg_count
2707 , p_data => x_err_msg
2708 );
2709
2710 IF (l_debug = 1) THEN
2711 mydebug('transfer_reservation: G_EXC_UNEXPECTED_ERROR ' || sqlerrm);
2712 END IF;
2713
2714 WHEN OTHERS THEN
2715 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2716 fnd_msg_pub.count_and_get
2717 ( p_count => x_msg_count
2718 , p_data => x_err_msg
2719 );
2720 IF (l_debug = 1) THEN
2721 mydebug('transfer_reservation: Other errors ' || sqlerrm);
2722 END IF;
2723
2724 END transfer_reservation;
2725
2726
2727 PROCEDURE mydebug(msg in varchar2)
2728 IS
2729 l_msg VARCHAR2(5100);
2730 l_ts VARCHAR2(30);
2731 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2732 BEGIN
2733 -- select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
2734 -- l_msg:=l_ts||' '||msg;
2735
2736 l_msg := msg;
2737
2738 inv_mobile_helper_functions.tracelog
2739 (p_err_msg => l_msg,
2740 p_module => 'WMSWIPIB',
2741 p_level => 4);
2742 --dbms_output.put_line('WMS_WIP_Integration' || l_msg);
2743
2744 null;
2745 END;
2746
2747 PROCEDURE update_mmtt_for_wip
2748 ( p_transaction_temp_id IN NUMBER
2749 , p_wip_entity_id IN NUMBER
2750 , p_operation_seq_num IN NUMBER
2751 , p_repetitive_schedule_id IN NUMBER
2752 , p_transaction_type_id IN NUMBER
2753 ) IS
2754
2755 l_organization_id NUMBER;
2756 l_entity_type NUMBER;
2757 l_repetitive_line_id NUMBER;
2758 l_department_id NUMBER;
2759 l_department_code bom_departments.department_code%TYPE;
2760
2761 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2762 l_project_id NUMBER := NULL ; --Bug6604904
2763 l_task_id NUMBER := NULL ; --Bug6604904
2764
2765 BEGIN
2766
2767 IF (l_debug = 1) THEN
2768 mydebug ('update_mmtt_for_wip: ' ||
2769 'p_transaction_temp_id=' || to_char(p_transaction_temp_id) ||
2770 ', p_wip_entity_id=' || to_char(p_wip_entity_id) ||
2771 ', p_operation_seq_num=' || to_char(p_operation_seq_num)
2772 );
2773 END IF;
2774
2775 IF p_transaction_type_id IS NULL OR
2776 (p_transaction_type_id <> INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE AND
2777 p_transaction_type_id <> INV_Globals.G_TYPE_XFER_ORDER_REPL_SUBXFR) THEN
2778 IF (l_debug = 1) THEN
2779 mydebug ('update_mmtt_for_wip:'||'Invalid transaction type: ' || to_char(p_transaction_type_id));
2780 END IF;
2781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2782 END IF;
2783
2784 BEGIN
2785 /* Bug6604904. Modified query to get project and task info from MTRL */
2786 SELECT mmtt.organization_id, mtrl.project_id , mtrl.task_id
2787 INTO l_organization_id , l_project_id, l_task_id
2788 FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
2789 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
2790 AND mmtt.move_order_line_id = mtrl.line_id ;
2791 EXCEPTION
2792 WHEN OTHERS THEN
2793 IF (l_debug = 1) THEN
2794 mydebug ('update_mmtt_for_wip:'||'Could not determine Org ID for passed in temp ID: ' || to_char(p_transaction_temp_id));
2795 END IF;
2796 RAISE;
2797 END;
2798
2799 -- entity type
2800 BEGIN
2801 SELECT entity_type
2802 INTO l_entity_type
2803 FROM wip_entities
2804 WHERE wip_entity_id = p_wip_entity_id
2805 AND organization_id = l_organization_id;
2806 EXCEPTION
2807 WHEN OTHERS THEN
2808 IF (l_debug = 1) THEN
2809 mydebug ('update_mmtt_for_wip:'||'Could not determine WIP entity type for passed in entity ID: ' || to_char(p_wip_entity_id));
2810 END IF;
2811 RAISE;
2812 END;
2813
2814
2815
2816 IF p_transaction_type_id = INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
2817 THEN
2818
2819 IF l_entity_type = 2 THEN
2820 IF p_repetitive_schedule_id IS NULL THEN
2821 IF (l_debug = 1) THEN
2822 mydebug ('update_mmtt_for_wip:'||
2823 'Parameter p_repetitive_schedule_id cannot be null for entity type 2.');
2824 END IF;
2825 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2826 ELSE
2827 BEGIN
2828 SELECT line_id
2829 INTO l_repetitive_line_id
2830 FROM wip_repetitive_schedules
2831 WHERE wip_entity_id = p_wip_entity_id
2832 AND repetitive_schedule_id = p_repetitive_schedule_id
2833 AND organization_id = l_organization_id;
2834 EXCEPTION
2835 WHEN OTHERS THEN
2836 IF (l_debug = 1) THEN
2837 mydebug ('update_mmtt_for_wip:'||'Unable to determine rep line ID for rep sch ID ' || to_char(p_repetitive_schedule_id));
2838 END IF;
2839 RAISE;
2840 END;
2841 END IF;
2842 END IF;
2843
2844 -- dept ID, code
2845 BEGIN
2846 IF l_entity_type IN (1,5) THEN
2847 SELECT wo.department_id
2848 , bd.department_code
2849 INTO l_department_id
2850 , l_department_code
2851 FROM bom_departments bd
2852 , wip_operations wo
2853 WHERE bd.department_id = wo.department_id
2854 AND wo.wip_entity_id = p_wip_entity_id
2855 AND wo.organization_id = l_organization_id
2856 AND wo.operation_seq_num = p_operation_seq_num;
2857 ELSIF l_entity_type = 2 THEN
2858 SELECT wo.department_id
2859 , bd.department_code
2860 INTO l_department_id
2861 , l_department_code
2862 FROM bom_departments bd
2863 , wip_operations wo
2864 WHERE bd.department_id = wo.department_id
2865 AND wo.wip_entity_id = p_wip_entity_id
2866 AND wo.organization_id = l_organization_id
2867 AND wo.operation_seq_num = p_operation_seq_num
2868 AND wo.repetitive_schedule_id = p_repetitive_schedule_id;
2869 ELSIF l_entity_type = 4 THEN
2870 SELECT bos.department_id
2871 , bd.department_code
2872 INTO l_department_id
2873 , l_department_code
2874 FROM bom_departments bd
2875 , bom_operation_sequences bos
2876 , bom_operational_routings bor
2877 , wip_flow_schedules wfs
2878 WHERE bd.department_id = bos.department_id
2879 AND bos.routing_sequence_id = bor.routing_sequence_id
2880 AND bos.operation_type = 1
2881 AND bos.effectivity_date >= sysdate
2882 AND (bor.alternate_routing_designator = wfs.alternate_routing_designator
2883 OR (wfs.alternate_routing_designator IS NULL
2884 AND bor.alternate_routing_designator IS NULL)
2885 )
2886 AND bor.assembly_item_id = wfs.primary_item_id
2887 AND bor.organization_id = wfs.organization_id
2888 AND wfs.wip_entity_id = p_wip_entity_id
2889 AND wfs.organization_id = l_organization_id;
2890 ELSE
2891 IF (l_debug = 1) THEN
2892 mydebug ('update_mmtt_for_wip:'||'Invalid entity type: ' || to_char(l_entity_type));
2893 END IF;
2894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2895 END IF;
2896 EXCEPTION
2897 WHEN NO_DATA_FOUND THEN
2898 -- Bug 2023916:
2899 -- No entry in wip operations for discrete/rep job (routing does not exist)
2900 IF (l_debug = 1) THEN
2901 mydebug ('update_mmtt_for_wip:'||'No data for dept ID/code (entity type ' || l_entity_type || ')');
2902 END IF;
2903 l_department_id := NULL;
2904 l_department_code := NULL;
2905 WHEN OTHERS THEN
2906 IF (l_debug = 1) THEN
2907 mydebug ('update_mmtt_for_wip:'||'Unable to determine department ID and department code.');
2908 END IF;
2909 RAISE;
2910 END;
2911 END IF; -- end if txn type is wip issue
2912
2913 IF p_transaction_type_id = INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
2914 THEN
2915 UPDATE mtl_material_transactions_temp
2916 SET transaction_source_id = p_wip_entity_id
2917 , trx_source_line_id = p_operation_seq_num
2918 , demand_source_header_id = p_wip_entity_id
2919 , demand_source_line = p_operation_seq_num
2920 , transaction_source_type_id = INV_Globals.G_SourceType_WIP
2921 , transaction_type_id = p_transaction_type_id
2922 , transaction_action_id = INV_Globals.G_Action_Issue
2923 , wip_entity_type = l_entity_type
2924 , repetitive_line_id = l_repetitive_line_id
2925 , operation_seq_num = p_operation_seq_num
2926 , department_id = l_department_id
2927 , department_code = l_department_code
2928 , lock_flag = 'N'
2929 , primary_switch = 1
2930 , wip_supply_type = 1
2931 , negative_req_flag = sign(transaction_quantity)
2932 , required_flag = '1'
2933 , process_flag = 'Y' -- Forward Port for bug 5188464
2934 , flow_schedule = NULL
2935 , project_id = l_project_id -- Bug6604904
2936 , task_id = l_task_id -- Bug6604904
2937 ,source_project_id = l_project_id --bug 6688561
2938 ,source_task_id = l_task_id --bug 6688561
2939 , transaction_date = SYSDATE --Bug 7305385
2940 WHERE transaction_temp_id = p_transaction_temp_id;
2941 IF (l_debug = 1) THEN
2942 mydebug ('update_mmtt_for_wip:'||'Done updating mmtt rec ' || p_transaction_temp_id || ' for WIP Issue.');
2943 END IF;
2944
2945 ELSIF p_transaction_type_id = INV_Globals.G_TYPE_XFER_ORDER_REPL_SUBXFR THEN
2946 --
2947 -- Bug 2057540: explicitly set WIP_SUPPLY_TYPE to null
2948 --
2949 UPDATE mtl_material_transactions_temp
2950 SET transaction_source_id = p_wip_entity_id
2951 , trx_source_line_id = p_operation_seq_num
2952 , demand_source_header_id = p_wip_entity_id
2953 , demand_source_line = p_operation_seq_num
2954 , transaction_source_type_id = INV_Globals.G_SourceType_Inventory
2955 , transaction_type_id = p_transaction_type_id
2956 , transaction_action_id = INV_Globals.G_Action_Subxfr
2957 , wip_entity_type = l_entity_type
2958 , wip_supply_type = NULL
2959 WHERE transaction_temp_id = p_transaction_temp_id;
2960 IF (l_debug = 1) THEN
2961 mydebug ('update_mmtt_for_wip:'||'Done updating mmtt record ' || p_transaction_temp_id ||
2962 ' for backflush sub transfer.');
2963 END IF;
2964 END IF;
2965
2966 EXCEPTION
2967 WHEN OTHERS THEN
2968 IF (l_debug = 1) THEN
2969 mydebug ('Error in update_mmtt_for_wip: ' || sqlcode || ', '||sqlerrm);
2970 END IF;
2971 RAISE;
2972 END update_mmtt_for_wip;
2973
2974
2975 -- Bug 2747945 : Added business flow code to the call to the wip processor.
2976 PROCEDURE wip_processor
2977 (p_txn_hdr_id IN NUMBER,
2978 p_business_flow_code IN NUMBER,
2979 x_return_status OUT NOCOPY VARCHAR2)
2980 IS
2981
2982 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2983 l_errorMsg VARCHAR2(100);
2984 BEGIN
2985
2986 IF (l_debug = 1) THEN
2987 mydebug('wip_processor: Begins');
2988 mydebug('Txn Header ID : '|| p_txn_hdr_id );
2989 mydebug('business flow code : '|| p_business_flow_code);
2990 END IF;
2991
2992 wip_mtlTempProc_grp.processtemp(p_initMsgList => FND_API.g_true,
2993 p_processInv => FND_API.g_true, --whether call inventory TM OR not
2994 p_txnHdrID => p_txn_hdr_id,
2995 p_mtlTxnBusinessFlowCode => p_business_flow_code,
2996 x_returnStatus => x_return_status,
2997 x_errorMsg => l_errorMsg);
2998
2999 IF (l_debug = 1) THEN
3000 mydebug('wip_processor: Ends');
3001 END IF;
3002
3003 EXCEPTION
3004
3005 WHEN FND_API.G_EXC_ERROR THEN
3006 x_return_status:=FND_API.G_RET_STS_ERROR;
3007
3008 FND_MSG_PUB.ADD;
3009 IF (l_debug = 1) THEN
3010 mydebug('wip_processor: Error: ' || sqlerrm);
3011 END IF;
3012
3013 WHEN OTHERS THEN
3014 x_return_status := FND_API.g_ret_sts_error;
3015
3016 FND_MSG_PUB.ADD;
3017 IF (l_debug = 1) THEN
3018 mydebug('wip_processor: Other Error: ' || sqlerrm);
3019 END IF;
3020
3021 END wip_processor;
3022
3023
3024 END WMS_WIP_Integration ;