[Home] [Help]
PACKAGE BODY: APPS.WMS_WIP_INTEGRATION
Source
1 PACKAGE BODY WMS_WIP_Integration AS
2 /* $Header: WMSWIPIB.pls 120.3.12010000.4 2008/10/10 18:12:38 bvanjaku 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 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1138 BEGIN
1139
1140 --Update MTLT attributes from WIP tables
1141
1142
1143 SELECT C_ATTRIBUTE1,
1144 C_ATTRIBUTE2,
1145 C_ATTRIBUTE3,
1146 C_ATTRIBUTE4,
1147 C_ATTRIBUTE5,
1148 C_ATTRIBUTE6,
1149 C_ATTRIBUTE7,
1150 C_ATTRIBUTE8,
1151 C_ATTRIBUTE9,
1152 C_ATTRIBUTE10,
1153 C_ATTRIBUTE11,
1154 C_ATTRIBUTE12,
1155 C_ATTRIBUTE13,
1156 C_ATTRIBUTE14,
1157 C_ATTRIBUTE15,
1158 C_ATTRIBUTE16,
1159 C_ATTRIBUTE17,
1160 C_ATTRIBUTE18,
1161 C_ATTRIBUTE19,
1162 C_ATTRIBUTE20,
1163 D_ATTRIBUTE1,
1164 D_ATTRIBUTE2,
1165 D_ATTRIBUTE3,
1166 D_ATTRIBUTE4,
1167 D_ATTRIBUTE5,
1168 D_ATTRIBUTE6,
1169 D_ATTRIBUTE7,
1170 D_ATTRIBUTE8,
1171 D_ATTRIBUTE9,
1172 N_ATTRIBUTE1,
1173 N_ATTRIBUTE2,
1174 N_ATTRIBUTE3,
1175 N_ATTRIBUTE4,
1176 N_ATTRIBUTE5,
1177 N_ATTRIBUTE6,
1178 N_ATTRIBUTE7,
1179 N_ATTRIBUTE8,
1180 N_ATTRIBUTE9,
1181 N_ATTRIBUTE10,
1182 LAST_UPDATE_DATE,
1183 LAST_UPDATED_BY,
1184 CREATION_DATE,
1185 CREATED_BY,
1186 LAST_UPDATE_LOGIN,
1187 REQUEST_ID ,
1188 PROGRAM_APPLICATION_ID ,
1189 PROGRAM_ID,
1190 PROGRAM_UPDATE_DATE,
1191 TRANSACTION_QUANTITY,
1192 LOT_EXPIRATION_DATE,
1193 ERROR_CODE,
1194 LOT_ATTRIBUTE_CATEGORY,
1195 VENDOR_ID,
1196 GRADE_CODE ,
1197 ORIGINATION_DATE,
1198 DATE_CODE,
1199 CHANGE_DATE,
1200 AGE,
1201 RETEST_DATE,
1202 MATURITY_DATE,
1203 ITEM_SIZE,
1204 COLOR,
1205 VOLUME,
1206 VOLUME_UOM,
1207 PLACE_OF_ORIGIN,
1208 BEST_BY_DATE,
1209 LENGTH,
1210 LENGTH_UOM,
1211 RECYCLED_CONTENT,
1212 THICKNESS,
1213 THICKNESS_UOM,
1214 WIDTH,
1215 WIDTH_UOM,
1216 CURL_WRINKLE_FOLD,
1217 VENDOR_NAME,
1218 SUPPLIER_LOT_NUMBER,
1219 TERRITORY_CODE
1220
1221 INTO c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,
1222 c17,c18,c19,c20,d1,d2,d3,d4,d5,d6,d7,d8,d9,n1,n2,n3,n4,
1223 n5,n6,n7,n8,n9,n10,
1224 d10,n12,d11,n13,n14,n15,n16,n17,d12,n18,d13,
1225 v1,v2,n20,v4,d14,v5,v6,n21,d15,d16,n22,v7,n23,v8,
1226 v9,d18,n24,v10,n25,n26,v11,n27,v12,v13,v14,
1227 v15,v16
1228
1229 FROM wip_lpn_completions_lots
1230 WHERE header_id = p_ref_id
1231 AND lot_number = p_lot;
1232
1233
1234
1235 UPDATE mtl_transaction_lots_temp
1236 SET C_ATTRIBUTE1 = c1,
1237 C_ATTRIBUTE2 = c2,
1238 C_ATTRIBUTE3 = c3,
1239 C_ATTRIBUTE4 = c4,
1240 C_ATTRIBUTE5 = c5,
1241 C_ATTRIBUTE6 = c6,
1242 C_ATTRIBUTE7 = c7,
1243 C_ATTRIBUTE8 = c8,
1244 C_ATTRIBUTE9 = c9,
1245 C_ATTRIBUTE10 = c10,
1246 C_ATTRIBUTE11 = c11,
1247 C_ATTRIBUTE12 = c12,
1248 C_ATTRIBUTE13 = c13,
1249 C_ATTRIBUTE14 = c14,
1250 C_ATTRIBUTE15 = c15,
1251 C_ATTRIBUTE16 = c16,
1252 C_ATTRIBUTE17 = c17,
1253 C_ATTRIBUTE18 = c18,
1254 C_ATTRIBUTE19 = c19,
1255 C_ATTRIBUTE20 = c20,
1256 D_ATTRIBUTE1 = d1,
1257 D_ATTRIBUTE2 = d2,
1258 D_ATTRIBUTE3 = d3,
1259 D_ATTRIBUTE4 = d4,
1260 D_ATTRIBUTE5 = d5,
1261 D_ATTRIBUTE6 = d6,
1262 D_ATTRIBUTE7 = d7,
1263 D_ATTRIBUTE8 = d8,
1264 D_ATTRIBUTE9 = d9,
1265 N_ATTRIBUTE1 = n1,
1266 N_ATTRIBUTE2 = n2,
1267 N_ATTRIBUTE3 = n3,
1268 N_ATTRIBUTE4 = n4,
1269 N_ATTRIBUTE5 = n5,
1270 N_ATTRIBUTE6 = n6,
1271 N_ATTRIBUTE7 = n7,
1272 N_ATTRIBUTE8 = n8,
1273 N_ATTRIBUTE9 = n9,
1274 N_ATTRIBUTE10 = n10,
1275 LAST_UPDATE_DATE = d10,
1276 LAST_UPDATED_BY=n12,
1277 CREATION_DATE=d11,
1278 CREATED_BY=n13,
1279 LAST_UPDATE_LOGIN=n14,
1280 REQUEST_ID =n15,
1281 PROGRAM_APPLICATION_ID =n16,
1282 PROGRAM_ID=n17,
1283 PROGRAM_UPDATE_DATE=d12,
1284 -- bug 2748242
1285 --TRANSACTION_QUANTITY=n18,
1286 LOT_EXPIRATION_DATE=d13,
1287 ERROR_CODE=v1,
1288 LOT_ATTRIBUTE_CATEGORY=v2,
1289 VENDOR_ID=n20,
1290 GRADE_CODE =v4,
1291 ORIGINATION_DATE=d14,
1292 DATE_CODE=v5,
1293 CHANGE_DATE=v6,
1294 AGE=n21,
1295 RETEST_DATE=d15,
1296 MATURITY_DATE=d16,
1297 ITEM_SIZE=n22,
1298 COLOR=v7,
1299 VOLUME=n23,
1300 VOLUME_UOM=v8,
1301 PLACE_OF_ORIGIN=v9,
1302 BEST_BY_DATE=d18,
1303 LENGTH=n24,
1304 LENGTH_UOM=v10,
1305 RECYCLED_CONTENT=n25,
1306 THICKNESS=n26,
1307 THICKNESS_UOM=v11,
1308 WIDTH=n27,
1309 WIDTH_UOM=v12,
1310 CURL_WRINKLE_FOLD=v13,
1311 VENDOR_NAME=v14,
1312 SUPPLIER_LOT_NUMBER=v15,
1313 TERRITORY_CODE=v16
1314 WHERE transaction_temp_id=p_temp_id
1315 AND lot_number=p_lot;
1316
1317
1318 END Capture_lot_atts;
1319
1320
1321
1322 PROCEDURE Update_serial
1323 ( p_header_id IN NUMBER
1324 , p_serial_number IN VARCHAR2
1325 , x_return_status OUT NOCOPY VARCHAR2
1326 , x_msg_count OUT NOCOPY NUMBER
1327 , x_msg_data OUT NOCOPY VARCHAR2
1328 )
1329
1330 IS
1331 v1 VARCHAR2(30);
1332 v2 VARCHAR2(30);
1333 v3 VARCHAR2(30);
1334 v4 VARCHAR2(150);
1335 v5 VARCHAR2(30);
1336 v6 VARCHAR2(150);
1337 v7 VARCHAR2(150);
1338 v8 VARCHAR2(150);
1339 v9 VARCHAR2(150);
1340 v10 VARCHAR2(150);
1341 v11 VARCHAR2(150);
1342 v12 VARCHAR2(150);
1343 v13 VARCHAR2(150);
1344 v14 VARCHAR2(150);
1345 v15 VARCHAR2(150);
1346 v16 VARCHAR2(150);
1347 v17 VARCHAR2(150);
1348 v18 VARCHAR2(150);
1349 v19 VARCHAR2(150);
1350 v20 VARCHAR2(150);
1351 v21 VARCHAR2(150);
1352 v22 VARCHAR2(150);
1353 v23 VARCHAR2(150);
1354 v24 VARCHAR2(150);
1355 v25 VARCHAR2(30);
1356 d1 DATE;
1357 d2 DATE;
1358 d3 DATE;
1359 d4 DATE;
1360 d5 DATE;
1361 d6 DATE;
1362 d7 DATE;
1363 d8 DATE;
1364 d9 DATE;
1365 d10 DATE;
1366 d11 DATE;
1367 d12 DATE;
1368 d13 DATE;
1369 d14 DATE;
1370 n1 NUMBER;
1371 n2 NUMBER;
1372 n3 NUMBER;
1373 n4 NUMBER;
1374 n5 NUMBER;
1375 n6 NUMBER;
1376 n7 NUMBER;
1377 n8 NUMBER;
1378 n9 NUMBER;
1379 n10 NUMBER;
1380 n11 NUMBER;
1381 n12 NUMBER;
1382 n13 NUMBER;
1383 n14 NUMBER;
1384 n15 NUMBER;
1385 n16 NUMBER;
1386 n17 NUMBER;
1387 n18 NUMBER;
1388 n19 NUMBER;
1389 n20 NUMBER;
1390 n21 NUMBER;
1391 n22 NUMBER;
1392 n23 NUMBER;
1393 n24 NUMBER;
1394 n25 NUMBER;
1395 n26 NUMBER;
1396 n27 NUMBER;
1397
1398 l_item_id NUMBER;
1399 l_return_status VARCHAR2(1);
1400 l_object_id NUMBER;
1401
1402 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1403
1404 BEGIN
1405
1406 l_return_status := FND_API.G_RET_STS_SUCCESS;
1407
1408 SELECT inventory_item_id
1409 INTO l_item_id
1410 FROM wip_lpn_completions
1411 WHERE header_id = p_header_id;
1412
1413 SELECT
1414 LAST_UPDATE_DATE,
1415 LAST_UPDATED_BY,
1416 CREATION_DATE,
1417 CREATED_BY,
1418 LAST_UPDATE_LOGIN,
1419 REQUEST_ID,
1420 PROGRAM_APPLICATION_ID,
1421 PROGRAM_ID,
1422 PROGRAM_UPDATE_DATE,
1423 FM_SERIAL_NUMBER,
1424 PARENT_SERIAL_NUMBER,
1425 LOT_NUMBER,
1426 C_ATTRIBUTE1,
1427 SERIAL_ATTRIBUTE_CATEGORY,
1428 ORIGINATION_DATE,
1429 C_ATTRIBUTE2,
1430 C_ATTRIBUTE3,
1431 C_ATTRIBUTE4,
1432 C_ATTRIBUTE5,
1433 C_ATTRIBUTE6,
1434 C_ATTRIBUTE7,
1435 C_ATTRIBUTE8,
1436 C_ATTRIBUTE9,
1437 C_ATTRIBUTE10,
1438 C_ATTRIBUTE11,
1439 C_ATTRIBUTE12,
1440 C_ATTRIBUTE13,
1441 C_ATTRIBUTE14,
1442 C_ATTRIBUTE15,
1443 C_ATTRIBUTE16,
1444 C_ATTRIBUTE17,
1445 C_ATTRIBUTE18,
1446 C_ATTRIBUTE19,
1447 C_ATTRIBUTE20,
1448 D_ATTRIBUTE1,
1449 D_ATTRIBUTE2,
1450 D_ATTRIBUTE3,
1451 D_ATTRIBUTE4,
1452 D_ATTRIBUTE5,
1453 D_ATTRIBUTE6,
1454 D_ATTRIBUTE7,
1455 D_ATTRIBUTE8,
1456 D_ATTRIBUTE9,
1457 D_ATTRIBUTE10,
1458 N_ATTRIBUTE1,
1459 N_ATTRIBUTE2,
1460 N_ATTRIBUTE3,
1461 N_ATTRIBUTE4,
1462 N_ATTRIBUTE5,
1463 N_ATTRIBUTE6,
1464 N_ATTRIBUTE7,
1465 N_ATTRIBUTE8,
1466 N_ATTRIBUTE9,
1467 N_ATTRIBUTE10,
1468 TERRITORY_CODE,
1469 TIME_SINCE_NEW,
1470 CYCLES_SINCE_NEW,
1471 TIME_SINCE_OVERHAUL,
1472 CYCLES_SINCE_OVERHAUL,
1473 TIME_SINCE_REPAIR,
1474 CYCLES_SINCE_REPAIR,
1475 TIME_SINCE_VISIT,
1476 CYCLES_SINCE_VISIT,
1477 TIME_SINCE_MARK,
1478 CYCLES_SINCE_MARK,
1479 STATUS_ID
1480
1481 INTO d1,n1,d2,n2,n3,n4,n5,n6,d3,v1,v2,v3,v4,v5,d4,
1482 v6,v7,v8,v9,v10,v11,v12,v13,v14,v15,v16,v17,v18,v19,v20,v21,
1483 v22,v23,v24,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,
1484 n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,v25,n17,n18,n19,
1485 n20,n21,n22,n23,n24,n25,n26,n27
1486
1487 FROM wip_lpn_completions_serials
1488 WHERE fm_serial_number = p_serial_number
1489 AND header_id = p_header_id;
1490
1491 select gen_object_id
1492 into l_object_id
1493 From mtl_serial_numbers
1494 where serial_number = p_serial_number
1495 and inventory_item_id = l_item_id;
1496
1497 if( l_object_id is null ) then
1498 select mtl_gen_object_id_s.nextval into l_object_id from dual;
1499 end if;
1500
1501 UPDATE mtl_serial_numbers
1502 SET
1503 LAST_UPDATE_DATE=d1,
1504 LAST_UPDATED_BY=n1,
1505 CREATION_DATE=d2,
1506 CREATED_BY=n2,
1507 LAST_UPDATE_LOGIN=n3,
1508 REQUEST_ID=n4,
1509 PROGRAM_APPLICATION_ID=n5,
1510 PROGRAM_ID=n6,
1511 PROGRAM_UPDATE_DATE=d3,
1512 PARENT_SERIAL_NUMBER=v2,
1513 LOT_NUMBER=v3,
1514 C_ATTRIBUTE1=v4,
1515 SERIAL_ATTRIBUTE_CATEGORY=v5,
1516 ORIGINATION_DATE=d4,
1517 C_ATTRIBUTE2=v6,
1518 C_ATTRIBUTE3=v7,
1519 C_ATTRIBUTE4=v8,
1520 C_ATTRIBUTE5=v9,
1521 C_ATTRIBUTE6=v10,
1522 C_ATTRIBUTE7=v11,
1523 C_ATTRIBUTE8=v12,
1524 C_ATTRIBUTE9=v13,
1525 C_ATTRIBUTE10=v14,
1526 C_ATTRIBUTE11=v15,
1527 C_ATTRIBUTE12=v16,
1528 C_ATTRIBUTE13=v17,
1529 C_ATTRIBUTE14=v18,
1530 C_ATTRIBUTE15=v19,
1531 C_ATTRIBUTE16=v20,
1532 C_ATTRIBUTE17=v21,
1533 C_ATTRIBUTE18=v22,
1534 C_ATTRIBUTE19=v23,
1535 C_ATTRIBUTE20=v24,
1536 D_ATTRIBUTE1=d5,
1537 D_ATTRIBUTE2=d6,
1538 D_ATTRIBUTE3=d7,
1539 D_ATTRIBUTE4=d8,
1540 D_ATTRIBUTE5=d9,
1541 D_ATTRIBUTE6=d10,
1542 D_ATTRIBUTE7=d11,
1543 D_ATTRIBUTE8=d12,
1544 D_ATTRIBUTE9=d13,
1545 D_ATTRIBUTE10=d14,
1546 N_ATTRIBUTE1=n7,
1547 N_ATTRIBUTE2=n8,
1548 N_ATTRIBUTE3=n9,
1549 N_ATTRIBUTE4=n10,
1550 N_ATTRIBUTE5=n11,
1551 N_ATTRIBUTE6=n12,
1552 N_ATTRIBUTE7=n13,
1553 N_ATTRIBUTE8=n14,
1554 N_ATTRIBUTE9=n15,
1555 N_ATTRIBUTE10=n16,
1556 TERRITORY_CODE=v25,
1557 TIME_SINCE_NEW=n17,
1558 CYCLES_SINCE_NEW=n18,
1559 TIME_SINCE_OVERHAUL=n19,
1560 CYCLES_SINCE_OVERHAUL=n20,
1561 TIME_SINCE_REPAIR=n21,
1562 CYCLES_SINCE_REPAIR=n22,
1563 TIME_SINCE_VISIT=n23,
1564 CYCLES_SINCE_VISIT=n24,
1565 TIME_SINCE_MARK=n25,
1566 CYCLES_SINCE_MARK=n26,
1567 GEN_OBJECT_ID = l_object_id,
1568 STATUS_ID = n27
1569 WHERE serial_number = p_serial_number
1570 AND inventory_item_id = l_item_id;
1571
1572 x_return_status := l_return_status;
1573 EXCEPTION
1574 WHEN OTHERS THEN
1575 x_return_status := FND_API.G_RET_STS_ERROR;
1576
1577 IF (l_debug = 1) THEN
1578 mydebug('Update_serial: Unexpected error: ' || sqlcode || ' :: ' || sqlerrm);
1579 END IF;
1580
1581 END Update_serial;
1582
1583
1584 PROCEDURE Insert_lot
1585 ( p_header_id IN NUMBER,
1586 p_lot_number IN VARCHAR2,
1587 x_return_status OUT NOCOPY VARCHAR2,
1588 x_msg_count OUT NOCOPY NUMBER,
1589 x_msg_data OUT NOCOPY VARCHAR2)
1590
1591 IS
1592
1593 l_item_id NUMBER;
1594 l_org_id NUMBER;
1595 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
1596 l_lot_number VARCHAR2(80);
1597 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1598 l_msg_count NUMBER;
1599 l_msg_data VARCHAR2(2000);
1600 l_object_id NUMBER;
1601 l_status_rec inv_material_status_pub.mtl_status_update_rec_type; --bug4073725
1602 l_status_id NUMBER;--bug4073725
1603
1604 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1605 BEGIN
1606
1607 l_return_status := FND_API.G_RET_STS_SUCCESS;
1608
1609 l_lot_number := p_lot_number;
1610
1611 SELECT inventory_item_id,organization_id
1612 INTO l_item_id,l_org_id
1613 FROM wip_lpn_completions
1614 WHERE header_id = p_header_id;
1615
1616 WMS_WIP_Integration.perform_lot_validations
1617 (p_item_id => l_item_id,
1618 p_org_id => l_org_id,
1619 p_lot_number => l_lot_number,
1620 x_return_status => l_return_status,
1621 x_msg_count => l_msg_count,
1622 x_msg_data => l_msg_data);
1623
1624 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1625
1626 -- Lot number does not exist
1627 select mtl_gen_object_id_s.nextval into l_object_id from dual;
1628
1629 INSERT INTO mtl_lot_numbers
1630 (INVENTORY_ITEM_ID
1631 ,ORGANIZATION_ID
1632 ,LOT_NUMBER
1633 ,LAST_UPDATE_DATE
1634 ,LAST_UPDATED_BY
1635 ,CREATION_DATE
1636 ,CREATED_BY
1637 ,LAST_UPDATE_LOGIN
1638 ,EXPIRATION_DATE
1639 ,REQUEST_ID
1640 ,PROGRAM_APPLICATION_ID
1641 ,PROGRAM_ID
1642 ,PROGRAM_UPDATE_DATE
1643 ,DESCRIPTION
1644 ,VENDOR_ID
1645 ,GRADE_CODE
1646 ,ORIGINATION_DATE
1647 ,DATE_CODE
1648 ,CHANGE_DATE
1649 ,AGE
1650 ,RETEST_DATE
1651 ,MATURITY_DATE
1652 ,LOT_ATTRIBUTE_CATEGORY
1653 ,ITEM_SIZE
1654 ,COLOR
1655 ,VOLUME
1656 ,VOLUME_UOM
1657 ,PLACE_OF_ORIGIN
1658 ,BEST_BY_DATE
1659 ,LENGTH
1660 ,LENGTH_UOM
1661 ,RECYCLED_CONTENT
1662 ,THICKNESS
1663 ,THICKNESS_UOM
1664 ,WIDTH
1665 ,WIDTH_UOM
1666 ,CURL_WRINKLE_FOLD
1667 ,C_ATTRIBUTE1
1668 ,C_ATTRIBUTE2
1669 ,C_ATTRIBUTE3
1670 ,C_ATTRIBUTE4
1671 ,C_ATTRIBUTE5
1672 ,C_ATTRIBUTE6
1673 ,C_ATTRIBUTE7
1674 ,C_ATTRIBUTE8
1675 ,C_ATTRIBUTE9
1676 ,C_ATTRIBUTE10
1677 ,C_ATTRIBUTE11
1678 ,C_ATTRIBUTE12
1679 ,C_ATTRIBUTE13
1680 ,C_ATTRIBUTE14
1681 ,C_ATTRIBUTE15
1682 ,C_ATTRIBUTE16
1683 ,C_ATTRIBUTE17
1684 ,C_ATTRIBUTE18
1685 ,C_ATTRIBUTE19
1686 ,C_ATTRIBUTE20
1687 ,D_ATTRIBUTE1
1688 ,D_ATTRIBUTE2
1689 ,D_ATTRIBUTE3
1690 ,D_ATTRIBUTE4
1691 ,D_ATTRIBUTE5
1692 ,D_ATTRIBUTE6
1693 ,D_ATTRIBUTE7
1694 ,D_ATTRIBUTE8
1695 ,D_ATTRIBUTE9
1696 ,D_ATTRIBUTE10
1697 ,N_ATTRIBUTE1
1698 ,N_ATTRIBUTE2
1699 ,N_ATTRIBUTE3
1700 ,N_ATTRIBUTE4
1701 ,N_ATTRIBUTE5
1702 ,N_ATTRIBUTE6
1703 ,N_ATTRIBUTE7
1704 ,N_ATTRIBUTE8
1705 ,N_ATTRIBUTE10
1706 ,VENDOR_NAME
1707 ,SUPPLIER_LOT_NUMBER
1708 ,N_ATTRIBUTE9
1709 ,TERRITORY_CODE
1710 ,GEN_OBJECT_ID
1711 ,STATUS_ID
1712 )
1713 (SELECT
1714 l_item_id
1715 ,l_org_id
1716 ,p_lot_number
1717 ,LAST_UPDATE_DATE
1718 ,LAST_UPDATED_BY
1719 ,CREATION_DATE
1720 ,CREATED_BY
1721 ,LAST_UPDATE_LOGIN
1722 ,LOT_EXPIRATION_DATE
1723 ,REQUEST_ID
1724 ,PROGRAM_APPLICATION_ID
1725 ,PROGRAM_ID
1726 ,PROGRAM_UPDATE_DATE
1727 ,DESCRIPTION
1728 ,VENDOR_ID
1729 ,GRADE_CODE
1730 ,ORIGINATION_DATE
1731 ,DATE_CODE
1732 ,CHANGE_DATE
1733 ,AGE
1734 ,RETEST_DATE
1735 ,MATURITY_DATE
1736 ,LOT_ATTRIBUTE_CATEGORY
1737 ,ITEM_SIZE
1738 ,COLOR
1739 ,VOLUME
1740 ,VOLUME_UOM
1741 ,PLACE_OF_ORIGIN
1742 ,BEST_BY_DATE
1743 ,LENGTH
1744 ,LENGTH_UOM
1745 ,RECYCLED_CONTENT
1746 ,THICKNESS
1747 ,THICKNESS_UOM
1748 ,WIDTH
1749 ,WIDTH_UOM
1750 ,CURL_WRINKLE_FOLD
1751 ,C_ATTRIBUTE1
1752 ,C_ATTRIBUTE2
1753 ,C_ATTRIBUTE3
1754 ,C_ATTRIBUTE4
1755 ,C_ATTRIBUTE5
1756 ,C_ATTRIBUTE6
1757 ,C_ATTRIBUTE7
1758 ,C_ATTRIBUTE8
1759 ,C_ATTRIBUTE9
1760 ,C_ATTRIBUTE10
1761 ,C_ATTRIBUTE11
1762 ,C_ATTRIBUTE12
1763 ,C_ATTRIBUTE13
1764 ,C_ATTRIBUTE14
1765 ,C_ATTRIBUTE15
1766 ,C_ATTRIBUTE16
1767 ,C_ATTRIBUTE17
1768 ,C_ATTRIBUTE18
1769 ,C_ATTRIBUTE19
1770 ,C_ATTRIBUTE20
1771 ,D_ATTRIBUTE1
1772 ,D_ATTRIBUTE2
1773 ,D_ATTRIBUTE3
1774 ,D_ATTRIBUTE4
1775 ,D_ATTRIBUTE5
1776 ,D_ATTRIBUTE6
1777 ,D_ATTRIBUTE7
1778 ,D_ATTRIBUTE8
1779 ,D_ATTRIBUTE9
1780 ,D_ATTRIBUTE10
1781 ,N_ATTRIBUTE1
1782 ,N_ATTRIBUTE2
1783 ,N_ATTRIBUTE3
1784 ,N_ATTRIBUTE4
1785 ,N_ATTRIBUTE5
1786 ,N_ATTRIBUTE6
1787 ,N_ATTRIBUTE7
1788 ,N_ATTRIBUTE8
1789 ,N_ATTRIBUTE10
1790 ,VENDOR_NAME
1791 ,SUPPLIER_LOT_NUMBER
1792 ,N_ATTRIBUTE9
1793 ,territory_code
1794 ,l_object_id
1795 ,status_id
1796 FROM wip_lpn_completions_lots
1797 WHERE header_id = p_header_id
1798 AND lot_number = p_lot_number
1799 );
1800
1801 l_return_status := FND_API.G_RET_STS_SUCCESS;
1802 END IF;
1803 /* bug4073725 changes start */
1804 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1805 SELECT status_id
1806 INTO l_status_id
1807 FROM wip_lpn_completions_lots
1808 WHERE header_id = p_header_id
1809 AND lot_number = p_lot_number;
1810 IF ( l_status_id IS NOT NULL ) THEN
1811 l_status_rec.update_method := inv_material_status_pub.g_update_method_auto;
1812 l_status_rec.organization_id := l_org_id;
1813 l_status_rec.inventory_item_id := l_item_id;
1814 l_status_rec.lot_number := p_lot_number;
1815 l_status_rec.status_id := l_status_id;
1816 l_status_rec.initial_status_flag := 'Y';
1817 l_status_rec.from_mobile_apps_flag := 'Y';
1818 inv_material_status_pkg.insert_status_history ( l_status_rec);
1819 END IF;
1820 END IF;
1821 /* bug4073725 changes end */
1822
1823 x_return_status := l_return_status;
1824
1825 END Insert_lot;
1826
1827
1828 PROCEDURE Perform_lot_validations(
1829 p_item_id IN NUMBER,
1830 p_org_id IN NUMBER,
1831 p_lot_number IN VARCHAR2,
1832 x_return_status OUT NOCOPY VARCHAR2,
1833 x_msg_count OUT NOCOPY NUMBER,
1834 x_msg_data OUT NOCOPY VARCHAR2)
1835
1836 IS
1837 l_item_id NUMBER;
1838 l_org_id NUMBER;
1839 l_count NUMBER;
1840 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
1841 l_lot_number VARCHAR2(80);
1842 l_lotunique NUMBER;
1843 l_lot_control_code NUMBER;
1844 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1845 l_shelf_life_code NUMBER;
1846 l_expiration_date DATE;
1847
1848 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1849 BEGIN
1850
1851 l_return_status := FND_API.G_RET_STS_SUCCESS;
1852
1853 l_item_id:=p_item_id;
1854 l_org_id:=p_org_id;
1855 l_lot_number:=p_lot_number;
1856
1857 l_count:=0;
1858
1859 BEGIN
1860 SELECT lot_control_code, shelf_life_code
1861 INTO l_lot_control_code, l_shelf_life_code
1862 FROM mtl_system_items
1863 WHERE inventory_item_id = l_item_id
1864 AND organization_id = l_org_id;
1865
1866 IF l_lot_control_code = 1 THEN
1867 fnd_message.set_name('INV','INV_NO_LOT_CONTROL');
1868 fnd_msg_pub.add;
1869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1870 END IF;
1871
1872 EXCEPTION
1873 WHEN NO_DATA_FOUND THEN
1874 fnd_message.set_name('INV','INV_INVALID_ITEM');
1875 fnd_msg_pub.add;
1876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1877 END;
1878
1879
1880 SELECT lot_number_uniqueness
1881 INTO l_lotunique
1882 FROM mtl_parameters
1883 WHERE organization_id = l_org_id;
1884
1885 IF l_lotunique = 1 then
1886 SELECT count(1)
1887 INTO l_count
1888 FROM MTL_LOT_NUMBERS
1889 WHERE inventory_item_id <> l_item_id
1890 AND lot_number = p_lot_number
1891 AND NOT EXISTS( SELECT NULL
1892 FROM mtl_lot_numbers lot
1893 WHERE lot.lot_number = p_lot_number
1894 AND lot.organization_id = l_org_id
1895 AND lot.inventory_item_id = l_item_id);
1896
1897 IF l_count > 0 then
1898 fnd_message.set_name('INV','INV_INT_LOTUNIQEXP');
1899 fnd_msg_pub.add;
1900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1901 END IF;
1902 END IF;
1903
1904 l_count := 0;
1905
1906
1907 SELECT COUNT(1)
1908 INTO l_count
1909 FROM mtl_lot_numbers
1910 WHERE inventory_item_id = l_item_id
1911 AND organization_id = l_org_id
1912 AND lot_number = p_lot_number;
1913
1914 -- Lot exists or not
1915 IF l_count = 1 THEN
1916
1917 fnd_message.set_name('INV','INV_LOT_EXISTS');
1918 fnd_msg_pub.add;
1919
1920 SELECT expiration_date
1921 INTO l_expiration_date
1922 FROM mtl_lot_numbers
1923 WHERE inventory_item_id = l_item_id
1924 AND organization_id = l_org_id
1925 AND lot_number = p_lot_number;
1926
1927
1928 IF l_shelf_life_code = 4 AND l_expiration_date < SYSDATE THEN
1929 fnd_message.set_name('INV','INV_LOT_EXPREQD');
1930 fnd_msg_pub.add;
1931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1932
1933 ELSE
1934 l_return_status := FND_API.G_RET_STS_SUCCESS;
1935 END IF;
1936
1937 ELSE
1938 l_return_status:=FND_API.G_RET_STS_ERROR;
1939 END IF;
1940
1941 x_return_status := l_return_status;
1942
1943 EXCEPTION
1944
1945 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1946 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1947 fnd_msg_pub.count_and_get
1948 ( p_count => x_msg_count
1949 , p_data => x_msg_data
1950 );
1951
1952 WHEN OTHERS THEN
1953 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1954 fnd_msg_pub.count_and_get
1955 ( p_count => x_msg_count
1956 , p_data => x_msg_data
1957 );
1958
1959 END Perform_lot_validations;
1960
1961
1962 PROCEDURE post_completion
1963 (p_item_id IN NUMBER,
1964 p_org_id IN NUMBER,
1965 p_fm_serial_number IN VARCHAR2,
1966 p_to_serial_number IN VARCHAR2,
1967 p_quantity IN NUMBER,
1968 x_return_status OUT NOCOPY VARCHAR2,
1969 x_msg_count OUT NOCOPY NUMBER,
1970 x_msg_data OUT NOCOPY VARCHAR2
1971 )
1972 IS
1973
1974 l_current_number NUMBER;
1975 l_current_serial_number VARCHAR2(30);
1976 l_prefix VARCHAR2(30);
1977 l_quantity NUMBER;
1978 l_fm_number NUMBER;
1979 l_to_number NUMBER;
1980 l_errorcode NUMBER;
1981 l_padded_length NUMBER;
1982 l_length NUMBER;
1983
1984 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1985 BEGIN
1986 IF (l_debug = 1) THEN
1987 mydebug('post_completion: procedure post_completion begins');
1988 END IF;
1989
1990 x_return_status :=FND_API.g_ret_sts_success;
1991 l_quantity := p_quantity;
1992
1993 /* Call this API to parse the serial numbers into prefixes and numbers */
1994 IF (NOT MTL_Serial_Check.inv_serial_info
1995 (p_from_serial_number => p_fm_serial_number,
1996 p_to_serial_number => p_to_serial_number,
1997 x_prefix => l_prefix,
1998 x_quantity => l_quantity,
1999 x_from_number => l_fm_number,
2000 x_to_number => l_to_number,
2001 x_errorcode => l_errorcode)) THEN
2002
2003 IF (l_debug = 1) THEN
2004 mydebug('post_completion: Invalid serial number given in range');
2005 END IF;
2006 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SER');
2007 FND_MSG_PUB.ADD;
2008 RAISE FND_API.G_EXC_ERROR;
2009 END IF;
2010
2011 -- Check that in the case of a range of serial numbers, that the
2012 -- inputted p_quantity equals the amount of items in the serial range.
2013 IF (p_quantity IS NOT NULL) THEN
2014 IF (p_quantity <> l_quantity) THEN
2015 IF (l_debug = 1) THEN
2016 mydebug('post_completion: Range of serial numbers does not match given qty');
2017 END IF;
2018 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_X_QTY');
2019 FND_MSG_PUB.ADD;
2020 RAISE FND_API.G_EXC_ERROR;
2021 END IF;
2022 END IF;
2023
2024 l_length := length(p_fm_serial_number);
2025 l_current_serial_number := p_fm_serial_number;
2026 l_current_number := l_fm_number;
2027 LOOP
2028
2029 IF (l_debug = 1) THEN
2030 mydebug('post_completion: serial_number: ' || l_current_serial_number);
2031 END IF;
2032
2033 UPDATE mtl_serial_numbers
2034 SET
2035 group_mark_id = NULL,
2036 current_status = 5
2037 WHERE current_organization_id = p_org_id
2038 AND inventory_item_id = p_item_id
2039 AND serial_number = l_current_serial_number;
2040
2041 EXIT WHEN l_current_serial_number = p_to_serial_number;
2042
2043 /* Increment the current serial number */
2044 l_current_number := l_current_number + 1;
2045 l_padded_length := l_length - length(l_current_number);
2046 l_current_serial_number := RPAD(l_prefix, l_padded_length, '0') ||
2047 l_current_number;
2048 END LOOP;
2049
2050 IF (l_debug = 1) THEN
2051 mydebug('post_completion: procedure post_completion ends');
2052 END IF;
2053
2054 x_return_status := FND_API.g_ret_sts_success;
2055
2056 EXCEPTION
2057
2058 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2059 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2060 fnd_msg_pub.count_and_get
2061 ( p_count => x_msg_count
2062 , p_data => x_msg_data
2063 );
2064
2065 WHEN OTHERS THEN
2066 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2067 fnd_msg_pub.count_and_get
2068 ( p_count => x_msg_count
2069 , p_data => x_msg_data
2070 );
2071
2072 END post_completion;
2073
2074
2075 PROCEDURE get_wip_job_info
2076 (p_temp_id IN NUMBER,
2077 p_wip_entity_type IN NUMBER,
2078 x_job OUT NOCOPY VARCHAR2,
2079 x_line OUT NOCOPY VARCHAR2,
2080 x_dept OUT NOCOPY VARCHAR2,
2081 x_operation_seq_num OUT NOCOPY NUMBER,
2082 x_start_date OUT NOCOPY DATE,
2083 x_schedule OUT NOCOPY VARCHAR2,
2084 x_assembly OUT NOCOPY VARCHAR2,
2085 x_return_status OUT NOCOPY VARCHAR2,
2086 x_msg_count OUT NOCOPY NUMBER,
2087 x_msg_data OUT NOCOPY VARCHAR2
2088 )
2089 IS
2090
2091
2092 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2093 BEGIN
2094
2095 IF (l_debug = 1) THEN
2096 mydebug('get_wip_job_info: get_wip_job_info begins');
2097 END IF;
2098 x_return_status :=FND_API.g_ret_sts_success;
2099
2100 IF p_wip_entity_type IN (1,5,6) THEN --Included 6 also for eAM-WMS Enhancement (Bug# 4093921)
2101
2102 -- Discrete job or lot based job
2103
2104 SELECT
2105 we.wip_entity_name,
2106 wl.line_code,
2107 bd.department_code,
2108 wro.operation_seq_num
2109
2110 INTO
2111 x_job,
2112 x_line,
2113 x_dept,
2114 x_operation_seq_num
2115
2116 FROM
2117 wip_entities we,
2118 wip_lines wl,
2119 bom_departments bd,
2120 wip_requirement_operations wro,
2121 wip_discrete_jobs wdj,
2122 mtl_txn_request_lines mtrl,
2123 mtl_material_transactions_temp mmtt
2124 WHERE we.wip_entity_id = wdj.wip_entity_id
2125 AND we.organization_id = wdj.organization_id
2126 AND wl.line_id (+) = wdj.line_id
2127 AND wl.organization_id (+) = wdj.organization_id
2128 AND bd.department_id (+) = wro.department_id
2129 AND wro.wip_entity_id = wdj.wip_entity_id
2130 AND wro.organization_id = wdj.organization_id
2131 AND wro.operation_seq_num = mtrl.txn_source_line_id
2132 AND wro.inventory_item_id = mtrl.inventory_item_id
2133 AND wdj.wip_entity_id = mtrl.txn_source_id
2134 AND wdj.organization_id = mtrl.organization_id
2135 AND mtrl.line_id = mmtt.move_order_line_id
2136 AND mmtt.transaction_temp_id = p_temp_id;
2137
2138 ELSIF p_wip_entity_type = 2 THEN
2139
2140 -- Repetitive schedule
2141
2142
2143 SELECT
2144 wl.line_code,
2145 bd.department_code,
2146 wro.operation_seq_num,
2147 msik.concatenated_segments,
2148 wrs.first_unit_start_date
2149
2150 INTO
2151 x_line,
2152 x_dept,
2153 x_operation_seq_num,
2154 x_assembly,
2155 x_start_date
2156
2157 FROM
2158 wip_lines wl,
2159 bom_departments bd,
2160 wip_requirement_operations wro,
2161 wip_repetitive_schedules wrs,
2162 mtl_txn_request_lines mtrl,
2163 mtl_material_transactions_temp mmtt,
2164 mtl_system_items_kfv msik,
2165 wip_entities we
2166 WHERE msik.inventory_item_id = we.primary_item_id
2167 AND msik.organization_id = we.organization_id
2168 AND we.wip_entity_id = wrs.wip_entity_id
2169 AND we.organization_id = wrs.organization_id
2170 AND wl.line_id = wrs.line_id
2171 AND wl.organization_id = wrs.organization_id
2172 AND bd.department_id (+) = wro.department_id
2173 AND wro.wip_entity_id = wrs.wip_entity_id
2174 AND wro.repetitive_schedule_id = wrs.repetitive_schedule_id
2175 AND wro.organization_id = wrs.organization_id
2176 AND wro.operation_seq_num = mtrl.txn_source_line_id
2177 AND wro.inventory_item_id = mtrl.inventory_item_id
2178 AND wrs.wip_entity_id = mtrl.txn_source_id
2179 AND wrs.repetitive_schedule_id = mtrl.reference_id
2180 AND wrs.organization_id = mtrl.organization_id
2181 AND mtrl.line_id = mmtt.move_order_line_id
2182 AND mmtt.transaction_temp_id = p_temp_id;
2183
2184
2185 ELSIF p_wip_entity_type = 4 THEN
2186
2187 -- Flow schedule
2188
2189 SELECT
2190 we.wip_entity_name,
2191 wl.line_code,
2192 bd.department_code,
2193 mtrl.txn_source_line_id
2194
2195 INTO
2196 x_schedule,
2197 x_line,
2198 x_dept,
2199 x_operation_seq_num
2200
2201 FROM
2202 wip_entities we,
2203 wip_lines wl,
2204 bom_departments bd,
2205 bom_operation_sequences bos,
2206 bom_operational_routings bor,
2207 wip_flow_schedules wfs,
2208 mtl_txn_request_lines mtrl,
2209 mtl_material_transactions_temp mmtt
2210 WHERE we.wip_entity_id = wfs.wip_entity_id
2211 AND we.organization_id = wfs.organization_id
2212 AND wl.line_id = wfs.line_id
2213 AND wl.organization_id = wfs.organization_id
2214 AND bd.department_id = bos.department_id
2215 AND bos.routing_sequence_id = bor.routing_sequence_id
2216 AND bos.operation_type = 1
2217 AND bos.effectivity_date >= sysdate
2218 AND ( wfs.alternate_routing_designator = bor.alternate_routing_designator
2219 OR (wfs.alternate_routing_designator IS NULL
2220 AND bor.alternate_routing_designator IS NULL) )
2221 AND bor.assembly_item_id = wfs.primary_item_id
2222 AND bor.organization_id = wfs.organization_id
2223 AND wfs.wip_entity_id = mtrl.txn_source_id
2224 AND wfs.organization_id = mtrl.organization_id
2225 AND mtrl.line_id = mmtt.move_order_line_id
2226 AND mmtt.transaction_temp_id = p_temp_id;
2227
2228 END IF;
2229
2230 IF (l_debug = 1) THEN
2231 mydebug('get_wip_job_info: x_job: ' || x_job);
2232 mydebug('get_wip_job_info: x_line: ' || x_line);
2233 mydebug('get_wip_job_info: x_dept: ' || x_dept);
2234 mydebug('get_wip_job_info: x_operation_seq_num: ' || x_operation_seq_num);
2235 mydebug('get_wip_job_info: x_start_date: ' || x_start_date);
2236 mydebug('get_wip_job_info: x_schedule: ' || x_schedule);
2237 mydebug('get_wip_job_info: x_assembly: ' || x_assembly);
2238 END IF;
2239
2240 IF (l_debug = 1) THEN
2241 mydebug('get_wip_job_info: Get_wip_job_info ends');
2242 END IF;
2243 x_return_status := FND_API.g_ret_sts_success;
2244
2245 EXCEPTION
2246
2247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2248 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2249 fnd_msg_pub.count_and_get
2250 ( p_count => x_msg_count
2251 , p_data => x_msg_data
2252 );
2253
2254 WHEN OTHERS THEN
2255 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2256 fnd_msg_pub.count_and_get
2257 ( p_count => x_msg_count
2258 , p_data => x_msg_data
2259 );
2260
2261 END get_wip_job_info;
2262
2263
2264
2265 PROCEDURE get_wip_info_for_putaway
2266 (p_temp_id IN NUMBER,
2267 x_wip_entity_type OUT NOCOPY NUMBER,
2268 x_job OUT NOCOPY VARCHAR2,
2269 x_line OUT NOCOPY VARCHAR2,
2270 x_dept OUT NOCOPY VARCHAR2,
2271 x_operation_seq_num OUT NOCOPY NUMBER,
2272 x_start_date OUT NOCOPY DATE,
2273 x_schedule OUT NOCOPY VARCHAR2,
2274 x_assembly OUT NOCOPY VARCHAR2,
2275 x_wip_entity_id OUT NOCOPY NUMBER,
2276 x_return_status OUT NOCOPY VARCHAR2,
2277 x_msg_count OUT NOCOPY NUMBER,
2278 x_msg_data OUT NOCOPY VARCHAR2
2279 )
2280
2281 IS
2282
2283 l_wip_entity_type NUMBER;
2284 l_org_id NUMBER;
2285 l_wip_entity_id NUMBER;
2286
2287 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2288 BEGIN
2289
2290 IF (l_debug = 1) THEN
2291 mydebug('get_wip_info_for_putaway: get_wip_info_for_putaway begins');
2292 END IF;
2293 x_return_status :=FND_API.g_ret_sts_success;
2294
2295 x_wip_entity_type :=NULL;
2296 x_job :=NULL;
2297 x_line :=NULL;
2298 x_dept :=NULL;
2299 x_operation_seq_num :=NULL;
2300 x_start_date :=NULL;
2301 x_schedule :=NULL;
2302 x_assembly :=NULL;
2303 x_wip_entity_id :=NULL;
2304
2305
2306
2307
2308 SELECT demand_source_header_id, organization_id
2309 INTO l_wip_entity_id, l_org_id
2310 FROM mtl_material_transactions_temp
2311 WHERE transaction_temp_id = p_temp_id;
2312
2313 x_wip_entity_id := l_wip_entity_id;
2314
2315 SELECT entity_type
2316 INTO l_wip_entity_type
2317 FROM wip_entities
2318 WHERE organization_id = l_org_id
2319 AND wip_entity_id = l_wip_entity_id;
2320
2321
2322 x_wip_entity_type := l_wip_entity_type;
2323
2324 IF l_wip_entity_type = 1 OR l_wip_entity_type = 5 THEN
2325
2326 -- Discrete job or lot based job
2327
2328 SELECT
2329 we.wip_entity_name,
2330 wl.line_code,
2331 bd.department_code,
2332 wro.operation_seq_num
2333
2334 INTO
2335 x_job,
2336 x_line,
2337 x_dept,
2338 x_operation_seq_num
2339
2340 FROM
2341 wip_entities we,
2342 wip_lines wl,
2343 bom_departments bd,
2344 wip_requirement_operations wro,
2345 wip_discrete_jobs wdj,
2346 mtl_material_transactions_temp mmtt
2347 WHERE we.wip_entity_id = wdj.wip_entity_id
2348 AND we.organization_id = wdj.organization_id
2349 AND wl.line_id (+) = wdj.line_id
2350 AND wl.organization_id (+) = wdj.organization_id
2351 AND bd.department_id (+) = wro.department_id
2352 AND wro.wip_entity_id = wdj.wip_entity_id
2353 AND wro.organization_id = wdj.organization_id
2354 AND wro.operation_seq_num = mmtt.operation_seq_num
2355 AND wro.inventory_item_id = mmtt.inventory_item_id
2356 AND wdj.wip_entity_id = mmtt.demand_source_header_id
2357 AND wdj.organization_id = mmtt.organization_id
2358 AND mmtt.transaction_temp_id = p_temp_id;
2359
2360 ELSIF l_wip_entity_type = 2 THEN
2361
2362 -- Repetitive schedule
2363
2364
2365 SELECT
2366 wl.line_code,
2367 bd.department_code,
2368 wro.operation_seq_num,
2369 msik.concatenated_segments,
2370 wrs.first_unit_start_date
2371
2372 INTO
2373 x_line,
2374 x_dept,
2375 x_operation_seq_num,
2376 x_assembly,
2377 x_start_date
2378
2379 FROM
2380 wip_lines wl,
2381 bom_departments bd,
2382 wip_requirement_operations wro,
2383 wip_repetitive_schedules wrs,
2384 mtl_material_transactions_temp mmtt,
2385 mtl_system_items_kfv msik,
2386 wip_entities we
2387 WHERE msik.inventory_item_id = we.primary_item_id
2388 AND msik.organization_id = we.organization_id
2389 AND we.wip_entity_id = wrs.wip_entity_id
2390 AND we.organization_id = wrs.organization_id
2391 AND wl.line_id = wrs.line_id
2392 AND wl.organization_id = wrs.organization_id
2393 AND bd.department_id (+) = wro.department_id
2394 AND wro.repetitive_schedule_id = wrs.repetitive_schedule_id
2395 AND wro.wip_entity_id = wrs.wip_entity_id
2396 AND wro.organization_id = wrs.organization_id
2397 AND wro.operation_seq_num = mmtt.operation_seq_num
2398 AND wro.inventory_item_id = mmtt.inventory_item_id
2399 AND wrs.repetitive_schedule_id = mmtt.repetitive_line_id
2400 AND wrs.wip_entity_id = mmtt.demand_source_header_id
2401 AND wrs.organization_id = mmtt.organization_id
2402 AND mmtt.transaction_temp_id = p_temp_id;
2403
2404
2405 ELSIF l_wip_entity_type = 4 THEN
2406
2407 -- Flow schedule
2408
2409 SELECT
2410 we.wip_entity_name,
2411 wl.line_code,
2412 bd.department_code,
2413 mmtt.operation_seq_num
2414
2415 INTO
2416 x_schedule,
2417 x_line,
2418 x_dept,
2419 x_operation_seq_num
2420
2421 FROM
2422 wip_entities we,
2423 wip_lines wl,
2424 bom_departments bd,
2425 bom_operation_sequences bos,
2426 bom_operational_routings bor,
2427 wip_flow_schedules wfs,
2428 mtl_material_transactions_temp mmtt
2429 WHERE we.wip_entity_id = wfs.wip_entity_id
2430 AND we.organization_id = wfs.organization_id
2431 AND wl.line_id = wfs.line_id
2432 AND wl.organization_id = wfs.organization_id
2433 AND bd.department_id = bos.department_id
2434 AND bos.routing_sequence_id = bor.routing_sequence_id
2435 AND bos.operation_type = 1
2436 AND bos.effectivity_date >= sysdate
2437 AND ( wfs.alternate_routing_designator = bor.alternate_routing_designator
2438 OR (wfs.alternate_routing_designator IS NULL
2439 AND bor.alternate_routing_designator IS NULL) )
2440 AND bor.assembly_item_id = wfs.primary_item_id
2441 AND bor.organization_id = wfs.organization_id
2442 AND wfs.wip_entity_id = mmtt.demand_source_header_id
2443 AND wfs.organization_id = mmtt.organization_id
2444 AND mmtt.transaction_temp_id = p_temp_id;
2445
2446 END IF;
2447
2448 IF (l_debug = 1) THEN
2449 mydebug('get_wip_info_for_putaway: x_wip_entity_type: ' || x_wip_entity_type);
2450 mydebug('get_wip_info_for_putaway: x_job: ' || x_job);
2451 mydebug('get_wip_info_for_putaway: x_line: ' || x_line);
2452 mydebug('get_wip_info_for_putaway: x_dept: ' || x_dept);
2453 mydebug('get_wip_info_for_putaway: x_operation_seq_num: ' || x_operation_seq_num);
2454 mydebug('get_wip_info_for_putaway: x_start_date: ' || x_start_date);
2455 mydebug('get_wip_info_for_putaway: x_schedule: ' || x_schedule);
2456 mydebug('get_wip_info_for_putaway: x_assembly: ' || x_assembly);
2457 END IF;
2458
2459 IF (l_debug = 1) THEN
2460 mydebug('get_wip_info_for_putaway: Get_wip_info_for_putaway ends');
2461 END IF;
2462 x_return_status := FND_API.g_ret_sts_success;
2463
2464 EXCEPTION
2465
2466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2467
2468 IF (l_debug = 1) THEN
2469 mydebug('get_wip_info_for_putaway: unexpected error: ' || Sqlerrm);
2470 END IF;
2471 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2472 fnd_msg_pub.count_and_get
2473 ( p_count => x_msg_count
2474 , p_data => x_msg_data
2475 );
2476
2477 WHEN OTHERS THEN
2478 IF (l_debug = 1) THEN
2479 mydebug('get_wip_info_for_putaway: unexpected error: ' || Sqlerrm);
2480 END IF;
2481 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2482 fnd_msg_pub.count_and_get
2483 ( p_count => x_msg_count
2484 , p_data => x_msg_data
2485 );
2486
2487 END get_wip_info_for_putaway;
2488
2489
2490
2491 PROCEDURE unallocate_material
2492 (p_wip_entity_id IN NUMBER,
2493 p_operation_seq_num IN NUMBER,
2494 p_inventory_item_id IN NUMBER,
2495 p_repetitive_schedule_id IN NUMBER := NULL,
2496 p_primary_quantity IN NUMBER,
2497 x_return_status OUT NOCOPY VARCHAR2,
2498 x_msg_data OUT NOCOPY VARCHAR2
2499 )
2500
2501 IS
2502 l_return_status VARCHAR2(1);
2503 l_msg_data VARCHAR2(2500);
2504 l_msg_count NUMBER;
2505
2506 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2507 BEGIN
2508
2509 l_return_status := FND_API.G_RET_STS_SUCCESS;
2510
2511 IF (l_debug = 1) THEN
2512 mydebug('wip_picking_pub.unallocate_material ends');
2513 END IF;
2514 wip_picking_pub.unallocate_material
2515 (p_wip_entity_id => p_wip_entity_id,
2516 p_operation_seq_num => p_operation_seq_num,
2517 p_inventory_item_id => p_inventory_item_id,
2518 p_repetitive_schedule_id=> p_repetitive_schedule_id,
2519 p_primary_quantity => p_primary_quantity,
2520 x_return_status => l_return_status,
2521 x_msg_data => l_msg_data);
2522
2523 IF (l_debug = 1) THEN
2524 mydebug('unallocate_material ends');
2525 END IF;
2526 x_return_status := l_return_status;
2527 x_msg_data := l_msg_data;
2528
2529 EXCEPTION
2530
2531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2532 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2533 fnd_msg_pub.count_and_get
2534 ( p_count => l_msg_count
2535 , p_data => l_msg_data
2536 );
2537
2538 WHEN OTHERS THEN
2539 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2540 fnd_msg_pub.count_and_get
2541 ( p_count => l_msg_count
2542 , p_data => l_msg_data
2543 );
2544
2545
2546 END unallocate_material;
2547
2548 PROCEDURE transfer_Reservation
2549 (
2550 P_HEADER_ID IN NUMBER,
2551 P_SUBINVENTORY_CODE IN VARCHAR2,
2552 P_LOCATOR_ID IN NUMBER,
2553 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2554 X_MSG_COUNT OUT NOCOPY NUMBER,
2555 X_ERR_MSG OUT NOCOPY VARCHAR2,
2556 p_temp_id IN NUMBER)
2557 IS
2558
2559 CURSOR mtlt_csr IS
2560 SELECT mtlt.lot_number, mtlt.primary_quantity
2561 FROM
2562 mtl_material_transactions_temp mmtt,
2563 mtl_transaction_lots_temp mtlt
2564 WHERE mmtt.transaction_temp_id = p_temp_id
2565 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id;
2566
2567 l_lot_control_code NUMBER;
2568 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2569 l_lot_number VARCHAR2(80);
2570 l_lpn_id NUMBER;
2571 l_xfr_lpn_id NUMBER;
2572 l_content_lpn_id NUMBER;
2573 l_lot_primary_qty NUMBER;
2574 l_primary_qty NUMBER;
2575 l_lpn_controlled_flag NUMBER;
2576 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2577 BEGIN
2578
2579 IF (l_debug = 1) THEN
2580 mydebug('transfer_Reservation: transfer_Reservation API begins');
2581 END IF;
2582
2583 SELECT
2584 msi.lot_control_code,
2585 mmtt.primary_quantity,
2586 mmtt.transfer_lpn_id,
2587 mmtt.content_lpn_id,
2588 msin.LPN_CONTROLLED_FLAG
2589 INTO
2590 l_lot_control_code,
2591 l_primary_qty,
2592 l_xfr_lpn_id,
2593 l_content_lpn_id,
2594 l_lpn_controlled_flag
2595 FROM
2596 mtl_system_items msi,
2597 mtl_material_transactions_temp mmtt,
2598 mtl_secondary_inventories msin
2599 WHERE mmtt.transaction_temp_id = p_temp_id
2600 AND mmtt.organization_id = msi.organization_id
2601 AND mmtt.inventory_item_id = msi.inventory_item_id
2602 AND mmtt.organization_id = msin.organization_id
2603 AND mmtt.subinventory_code = msin.SECONDARY_INVENTORY_NAME;
2604
2605 IF l_content_lpn_id IS NOT NULL THEN
2606
2607 l_lpn_id := l_content_lpn_id;
2608
2609 ELSIF l_xfr_lpn_id IS NOT NULL THEN
2610
2611 l_lpn_id := l_xfr_lpn_id;
2612
2613 ELSE
2614
2615 l_lpn_id := NULL;
2616
2617 END IF;
2618
2619 IF l_lpn_controlled_flag = 2 THEN --NON LPN CONTROLLED SUBINVENTORY
2620 l_lpn_id := NULL;
2621 END IF;
2622
2623
2624 IF l_lot_control_code > 1 THEN
2625
2626 IF (l_debug = 1) THEN
2627 mydebug('transfer_Reservation: Lot controlled item');
2628 END IF;
2629
2630 OPEN mtlt_csr;
2631 LOOP
2632 FETCH mtlt_csr INTO l_lot_number, l_lot_primary_qty;
2633 EXIT WHEN mtlt_csr%notfound;
2634
2635 wma_inv_wrappers.transferReservation
2636 (P_HEADER_ID => p_header_id,
2637 P_SUBINVENTORY_CODE => p_subinventory_code,
2638 P_LOCATOR_ID => p_locator_id,
2639 p_primary_quantity => l_lot_primary_qty,
2640 p_lpn_id => l_lpn_id,
2641 p_lot_number => l_lot_number,
2642 X_RETURN_STATUS => x_return_status,
2643 X_MSG_COUNT => x_msg_count,
2644 X_ERR_MSG => x_err_msg);
2645
2646 END LOOP;
2647 CLOSE mtlt_csr;
2648
2649 ELSE
2650 IF (l_debug = 1) THEN
2651 mydebug('transfer_Reservation: Not a lot controlled item');
2652 END IF;
2653
2654 wma_inv_wrappers.transferReservation
2655 (P_HEADER_ID => p_header_id,
2656 P_SUBINVENTORY_CODE => p_subinventory_code,
2657 P_LOCATOR_ID => p_locator_id,
2658 p_primary_quantity => l_primary_qty,
2659 p_lpn_id => l_lpn_id,
2660 p_lot_number => null,
2661 X_RETURN_STATUS => x_return_status,
2662 X_MSG_COUNT => x_msg_count,
2663 X_ERR_MSG => x_err_msg);
2664
2665 END IF;
2666
2667 IF (l_debug = 1) THEN
2668 mydebug('transfer_Reservation: transfer_Reservation API complete');
2669 END IF;
2670
2671 EXCEPTION
2672
2673 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2674 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2675 fnd_msg_pub.count_and_get
2676 ( p_count => x_msg_count
2677 , p_data => x_err_msg
2678 );
2679
2680 IF (l_debug = 1) THEN
2681 mydebug('transfer_reservation: G_EXC_UNEXPECTED_ERROR ' || sqlerrm);
2682 END IF;
2683
2684 WHEN OTHERS THEN
2685 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2686 fnd_msg_pub.count_and_get
2687 ( p_count => x_msg_count
2688 , p_data => x_err_msg
2689 );
2690 IF (l_debug = 1) THEN
2691 mydebug('transfer_reservation: Other errors ' || sqlerrm);
2692 END IF;
2693
2694 END transfer_reservation;
2695
2696
2697 PROCEDURE mydebug(msg in varchar2)
2698 IS
2699 l_msg VARCHAR2(5100);
2700 l_ts VARCHAR2(30);
2701 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2702 BEGIN
2703 -- select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
2704 -- l_msg:=l_ts||' '||msg;
2705
2706 l_msg := msg;
2707
2708 inv_mobile_helper_functions.tracelog
2709 (p_err_msg => l_msg,
2710 p_module => 'WMSWIPIB',
2711 p_level => 4);
2712 --dbms_output.put_line('WMS_WIP_Integration' || l_msg);
2713
2714 null;
2715 END;
2716
2717 PROCEDURE update_mmtt_for_wip
2718 ( p_transaction_temp_id IN NUMBER
2719 , p_wip_entity_id IN NUMBER
2720 , p_operation_seq_num IN NUMBER
2721 , p_repetitive_schedule_id IN NUMBER
2722 , p_transaction_type_id IN NUMBER
2723 ) IS
2724
2725 l_organization_id NUMBER;
2726 l_entity_type NUMBER;
2727 l_repetitive_line_id NUMBER;
2728 l_department_id NUMBER;
2729 l_department_code bom_departments.department_code%TYPE;
2730
2731 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2732 l_project_id NUMBER := NULL ; --Bug6604904
2733 l_task_id NUMBER := NULL ; --Bug6604904
2734
2735 BEGIN
2736
2737 IF (l_debug = 1) THEN
2738 mydebug ('update_mmtt_for_wip: ' ||
2739 'p_transaction_temp_id=' || to_char(p_transaction_temp_id) ||
2740 ', p_wip_entity_id=' || to_char(p_wip_entity_id) ||
2741 ', p_operation_seq_num=' || to_char(p_operation_seq_num)
2742 );
2743 END IF;
2744
2745 IF p_transaction_type_id IS NULL OR
2746 (p_transaction_type_id <> INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE AND
2747 p_transaction_type_id <> INV_Globals.G_TYPE_XFER_ORDER_REPL_SUBXFR) THEN
2748 IF (l_debug = 1) THEN
2749 mydebug ('update_mmtt_for_wip:'||'Invalid transaction type: ' || to_char(p_transaction_type_id));
2750 END IF;
2751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2752 END IF;
2753
2754 BEGIN
2755 /* Bug6604904. Modified query to get project and task info from MTRL */
2756 SELECT mmtt.organization_id, mtrl.project_id , mtrl.task_id
2757 INTO l_organization_id , l_project_id, l_task_id
2758 FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
2759 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
2760 AND mmtt.move_order_line_id = mtrl.line_id ;
2761 EXCEPTION
2762 WHEN OTHERS THEN
2763 IF (l_debug = 1) THEN
2764 mydebug ('update_mmtt_for_wip:'||'Could not determine Org ID for passed in temp ID: ' || to_char(p_transaction_temp_id));
2765 END IF;
2766 RAISE;
2767 END;
2768
2769 -- entity type
2770 BEGIN
2771 SELECT entity_type
2772 INTO l_entity_type
2773 FROM wip_entities
2774 WHERE wip_entity_id = p_wip_entity_id
2775 AND organization_id = l_organization_id;
2776 EXCEPTION
2777 WHEN OTHERS THEN
2778 IF (l_debug = 1) THEN
2779 mydebug ('update_mmtt_for_wip:'||'Could not determine WIP entity type for passed in entity ID: ' || to_char(p_wip_entity_id));
2780 END IF;
2781 RAISE;
2782 END;
2783
2784
2785
2786 IF p_transaction_type_id = INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
2787 THEN
2788
2789 IF l_entity_type = 2 THEN
2790 IF p_repetitive_schedule_id IS NULL THEN
2791 IF (l_debug = 1) THEN
2792 mydebug ('update_mmtt_for_wip:'||
2793 'Parameter p_repetitive_schedule_id cannot be null for entity type 2.');
2794 END IF;
2795 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2796 ELSE
2797 BEGIN
2798 SELECT line_id
2799 INTO l_repetitive_line_id
2800 FROM wip_repetitive_schedules
2801 WHERE wip_entity_id = p_wip_entity_id
2802 AND repetitive_schedule_id = p_repetitive_schedule_id
2803 AND organization_id = l_organization_id;
2804 EXCEPTION
2805 WHEN OTHERS THEN
2806 IF (l_debug = 1) THEN
2807 mydebug ('update_mmtt_for_wip:'||'Unable to determine rep line ID for rep sch ID ' || to_char(p_repetitive_schedule_id));
2808 END IF;
2809 RAISE;
2810 END;
2811 END IF;
2812 END IF;
2813
2814 -- dept ID, code
2815 BEGIN
2816 IF l_entity_type IN (1,5) THEN
2817 SELECT wo.department_id
2818 , bd.department_code
2819 INTO l_department_id
2820 , l_department_code
2821 FROM bom_departments bd
2822 , wip_operations wo
2823 WHERE bd.department_id = wo.department_id
2824 AND wo.wip_entity_id = p_wip_entity_id
2825 AND wo.organization_id = l_organization_id
2826 AND wo.operation_seq_num = p_operation_seq_num;
2827 ELSIF l_entity_type = 2 THEN
2828 SELECT wo.department_id
2829 , bd.department_code
2830 INTO l_department_id
2831 , l_department_code
2832 FROM bom_departments bd
2833 , wip_operations wo
2834 WHERE bd.department_id = wo.department_id
2835 AND wo.wip_entity_id = p_wip_entity_id
2836 AND wo.organization_id = l_organization_id
2837 AND wo.operation_seq_num = p_operation_seq_num
2838 AND wo.repetitive_schedule_id = p_repetitive_schedule_id;
2839 ELSIF l_entity_type = 4 THEN
2840 SELECT bos.department_id
2841 , bd.department_code
2842 INTO l_department_id
2843 , l_department_code
2844 FROM bom_departments bd
2845 , bom_operation_sequences bos
2846 , bom_operational_routings bor
2847 , wip_flow_schedules wfs
2848 WHERE bd.department_id = bos.department_id
2849 AND bos.routing_sequence_id = bor.routing_sequence_id
2850 AND bos.operation_type = 1
2851 AND bos.effectivity_date >= sysdate
2852 AND (bor.alternate_routing_designator = wfs.alternate_routing_designator
2853 OR (wfs.alternate_routing_designator IS NULL
2854 AND bor.alternate_routing_designator IS NULL)
2855 )
2856 AND bor.assembly_item_id = wfs.primary_item_id
2857 AND bor.organization_id = wfs.organization_id
2858 AND wfs.wip_entity_id = p_wip_entity_id
2859 AND wfs.organization_id = l_organization_id;
2860 ELSE
2861 IF (l_debug = 1) THEN
2862 mydebug ('update_mmtt_for_wip:'||'Invalid entity type: ' || to_char(l_entity_type));
2863 END IF;
2864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2865 END IF;
2866 EXCEPTION
2867 WHEN NO_DATA_FOUND THEN
2868 -- Bug 2023916:
2869 -- No entry in wip operations for discrete/rep job (routing does not exist)
2870 IF (l_debug = 1) THEN
2871 mydebug ('update_mmtt_for_wip:'||'No data for dept ID/code (entity type ' || l_entity_type || ')');
2872 END IF;
2873 l_department_id := NULL;
2874 l_department_code := NULL;
2875 WHEN OTHERS THEN
2876 IF (l_debug = 1) THEN
2877 mydebug ('update_mmtt_for_wip:'||'Unable to determine department ID and department code.');
2878 END IF;
2879 RAISE;
2880 END;
2881 END IF; -- end if txn type is wip issue
2882
2883 IF p_transaction_type_id = INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
2884 THEN
2885 UPDATE mtl_material_transactions_temp
2886 SET transaction_source_id = p_wip_entity_id
2887 , trx_source_line_id = p_operation_seq_num
2888 , demand_source_header_id = p_wip_entity_id
2889 , demand_source_line = p_operation_seq_num
2890 , transaction_source_type_id = INV_Globals.G_SourceType_WIP
2891 , transaction_type_id = p_transaction_type_id
2892 , transaction_action_id = INV_Globals.G_Action_Issue
2893 , wip_entity_type = l_entity_type
2894 , repetitive_line_id = l_repetitive_line_id
2895 , operation_seq_num = p_operation_seq_num
2896 , department_id = l_department_id
2897 , department_code = l_department_code
2898 , lock_flag = 'N'
2899 , primary_switch = 1
2900 , wip_supply_type = 1
2901 , negative_req_flag = sign(transaction_quantity)
2902 , required_flag = '1'
2903 , process_flag = 'Y' -- Forward Port for bug 5188464
2904 , flow_schedule = NULL
2905 , project_id = l_project_id -- Bug6604904
2906 , task_id = l_task_id -- Bug6604904
2907 ,source_project_id = l_project_id --bug 6688561
2908 ,source_task_id = l_task_id --bug 6688561
2909 , transaction_date = SYSDATE --Bug 7305385
2910 WHERE transaction_temp_id = p_transaction_temp_id;
2911 IF (l_debug = 1) THEN
2912 mydebug ('update_mmtt_for_wip:'||'Done updating mmtt rec ' || p_transaction_temp_id || ' for WIP Issue.');
2913 END IF;
2914
2915 ELSIF p_transaction_type_id = INV_Globals.G_TYPE_XFER_ORDER_REPL_SUBXFR THEN
2916 --
2917 -- Bug 2057540: explicitly set WIP_SUPPLY_TYPE to null
2918 --
2919 UPDATE mtl_material_transactions_temp
2920 SET transaction_source_id = p_wip_entity_id
2921 , trx_source_line_id = p_operation_seq_num
2922 , demand_source_header_id = p_wip_entity_id
2923 , demand_source_line = p_operation_seq_num
2924 , transaction_source_type_id = INV_Globals.G_SourceType_Inventory
2925 , transaction_type_id = p_transaction_type_id
2926 , transaction_action_id = INV_Globals.G_Action_Subxfr
2927 , wip_entity_type = l_entity_type
2928 , wip_supply_type = NULL
2929 WHERE transaction_temp_id = p_transaction_temp_id;
2930 IF (l_debug = 1) THEN
2931 mydebug ('update_mmtt_for_wip:'||'Done updating mmtt record ' || p_transaction_temp_id ||
2932 ' for backflush sub transfer.');
2933 END IF;
2934 END IF;
2935
2936 EXCEPTION
2937 WHEN OTHERS THEN
2938 IF (l_debug = 1) THEN
2939 mydebug ('Error in update_mmtt_for_wip: ' || sqlcode || ', '||sqlerrm);
2940 END IF;
2941 RAISE;
2942 END update_mmtt_for_wip;
2943
2944
2945 -- Bug 2747945 : Added business flow code to the call to the wip processor.
2946 PROCEDURE wip_processor
2947 (p_txn_hdr_id IN NUMBER,
2948 p_business_flow_code IN NUMBER,
2949 x_return_status OUT NOCOPY VARCHAR2)
2950 IS
2951
2952 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2953 l_errorMsg VARCHAR2(100);
2954 BEGIN
2955
2956 IF (l_debug = 1) THEN
2957 mydebug('wip_processor: Begins');
2958 mydebug('Txn Header ID : '|| p_txn_hdr_id );
2959 mydebug('business flow code : '|| p_business_flow_code);
2960 END IF;
2961
2962 wip_mtlTempProc_grp.processtemp(p_initMsgList => FND_API.g_true,
2963 p_processInv => FND_API.g_true, --whether call inventory TM OR not
2964 p_txnHdrID => p_txn_hdr_id,
2965 p_mtlTxnBusinessFlowCode => p_business_flow_code,
2966 x_returnStatus => x_return_status,
2967 x_errorMsg => l_errorMsg);
2968
2969 IF (l_debug = 1) THEN
2970 mydebug('wip_processor: Ends');
2971 END IF;
2972
2973 EXCEPTION
2974
2975 WHEN FND_API.G_EXC_ERROR THEN
2976 x_return_status:=FND_API.G_RET_STS_ERROR;
2977
2978 FND_MSG_PUB.ADD;
2979 IF (l_debug = 1) THEN
2980 mydebug('wip_processor: Error: ' || sqlerrm);
2981 END IF;
2982
2983 WHEN OTHERS THEN
2984 x_return_status := FND_API.g_ret_sts_error;
2985
2986 FND_MSG_PUB.ADD;
2987 IF (l_debug = 1) THEN
2988 mydebug('wip_processor: Other Error: ' || sqlerrm);
2989 END IF;
2990
2991 END wip_processor;
2992
2993
2994 END WMS_WIP_Integration ;