[Home] [Help]
PACKAGE BODY: APPS.WSMPINVL
Source
1 PACKAGE BODY WSMPINVL AS
2 /* $Header: WSMINVLB.pls 120.7.12010000.3 2009/12/02 12:23:19 sisankar ship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CalIFornia, USA |
6 | All rights reserved. |
7 |
8 | DESCRIPTION
9 |
10 | Server side package body for inventory split/merge program.
11 |
12 | Created By : Vikram Singhvi
13 | Last Update Date : 04/04/2000
14 |
15 | Jul 22, 2000 Bala Balakumar
16 | Bug: 1362550 - Handle if inventory txn worker is not launched.
17 |
18 | Aug 25, 2000 Bala Balakumar.
19 | Introduced comments on the process flow of the program.
20 |
21 | Sep 12th, 2000 Bala Balakumar
22 | Inventory Lot Txn Interface Improvement Project (IIIP).
23 |
24 | Oct 11th, 2000 Abedajna
25 | Performance Tuning
26 |
27 | Jun 27th, 2001 Bala Balakumar
28 | Unhandled exceptions will be handled now appropriately.
29 | TRANSLATE functionality now should allow change of Lot Name also.
30 |
31 | Jul 27th, 2001 Shashi Bhaskaran
32 | Bugfix 2449452: Made group_id an optional parameter and made necessary changes
33 | to the code as per the open-interface document.
34 |==========================================================================*/
35
36
37 x_exp_date DATE;
38
39 PROCEDURE PROCESS_INTERFACE_ROWS(
40 errbuf OUT NOCOPY VARCHAR2,
41 retcode OUT NOCOPY NUMBER,
42 P_group_id IN NUMBER,
43 p_header_id IN NUMBER,
44 P_MODE IN NUMBER) IS
45
46 X_header_id NUMBER :=0;
47 o_err_code NUMBER := 0;
48 o_err_message VARCHAR2(2000) := null;
49 x_err_cnt NUMBER :=0;
50 conc_status BOOLEAN;
51 wsm_worker_failed EXCEPTION;
52 dummy_header NUMBER :=0;
53
54 -- added by BBK. for debugging.
55 lProcName VARCHAR2(32) := 'PROCESS_INTERFACE_ROWS';
56 lProcLocation NUMBER := 0;
57
58 BEGIN
59
60 /*BA#IIIP*/
61 g_debug := FND_PROFILE.VALUE('MRP_DEBUG');
62 /*EA#IIIP*/
63
64 fnd_file.put_line(fnd_file.log, 'Starting Import Inventory Lot Transactions with following parameters.. ');
65 fnd_file.put_line(fnd_file.log, 'Group Id : '||p_group_id||
66 ', Header Id : '||p_header_id||
67 ', Mode : '||p_mode);
68 -- 2449452: added if clause
69 if g_debug = 'Y' then
70 fnd_file.put_line(fnd_file.log, 'Debug ENABLED.');
71 else
72 fnd_file.put_line(fnd_file.log, 'Debug DISABLED.');
73 end if;
74
75 /*BA#IIIP*/
76
77 lProcLocation := 10;
78
79 showProgress(
80 processingMode => p_mode
81 , headerId => dummy_header
82 , procName => lProcName
83 , procLocation => lProcLocation
84 , showMessage => ( 'Group Id '|| P_group_id));
85 /*EA#IIIP*/
86
87 Transact(P_group_id,
88 p_header_id,
89 P_mode,
90 x_header_id,
91 o_err_code,
92 o_err_message,
93 x_err_cnt);
94
95 IF x_err_cnt > 0 THEN
96 raise wsm_worker_failed;
97 END IF;
98
99 EXCEPTION
100 WHEN wsm_worker_failed THEN
101 CONC_STATUS :=
102 FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',o_err_message);
103 fnd_file.put_line(fnd_file.log,o_err_message);
104
105 /*BA#IIIP*/
106
107 writeToLog(requestId => FND_GLOBAL.conc_request_id
108 , programId => FND_GLOBAL.conc_program_id
109 , programApplnId => FND_GLOBAL.prog_appl_id
110 );
111 /*EA#IIIP*/
112
113 END;
114
115 ----------------------------------------------------------------------
116
117 PROCEDURE Transact(
118 P_Group_Id IN NUMBER,
119 p_header_id IN NUMBER,
120 P_Mode IN NUMBER,
121 x_header_id OUT NOCOPY NUMBER,
122 o_err_code OUT NOCOPY NUMBER,
123 o_err_message OUT NOCOPY VARCHAR2,
124 x_err_cnt OUT NOCOPY NUMBER
125 ) IS
126 /* Bugfix 2449452: Modified the cursor txns and added a new cursor no_grp_id_rows.
127
128 call p_header_id p_group_id
129 --------------- ----------- ----------
130 ONLINE (form) not-null null
131
132 CONCURRENT (iface) Null Null
133 CONCURRENT (iface) Null Not Null
134 CONCURRENT (iface) Not Null Null
135 CONCURRENT (iface) Not Null Not Null
136
137 */
138
139 CURSOR NO_GRP_ID_ROWS IS
140 SELECT header_id
141 FROM wsm_lot_split_merges_interface
142 WHERE group_id IS NULL
143 AND process_status = WSMPINVL.PENDING
144 AND transaction_date <= SYSDATE
145 ORDER BY transaction_date;
146
147 Cursor txns IS
148 SELECT transaction_id, header_id, process_status,
149 transaction_type_id, created_by, transaction_date
150 , organization_id -- bugfix 2832025/2841055: added orgn_id
151 FROM wsm_lot_split_merges_interface
152 WHERE nvl(group_id,-1) = nvl(p_group_id, nvl(group_id,-1) )
153 AND header_id = nvl(p_header_id, header_id)
154 AND process_status = WSMPINVL.PENDING
155 AND transaction_date <= SYSDATE
156 ORDER BY transaction_date;
157
158 --bugfix 2624791 added cursor comp_txns, the cursor picks up same rows as txns, but select status = complete.
159 --this is to fix the issue, that no genealoy got populated when doing Inventory Txn through interface.
160 --detail please check at where the enter_genealogy_records was being called.
161 Cursor comp_txns IS
162 SELECT transaction_id, header_id, process_status,
163 transaction_type_id, created_by, transaction_date
164 FROM wsm_lot_split_merges_interface
165 WHERE nvl(group_id,-1) = nvl(p_group_id, nvl(group_id,-1) )
166 AND header_id = nvl(p_header_id, header_id)
167 AND process_status = WSMPINVL.COMPLETE
168 AND transaction_date <= SYSDATE
169 ORDER BY transaction_date;
170
171 l_header_id NUMBER;
172 l_group_id NUMBER;
173
174
175
176 x_header_id1 NUMBER;
177 x_return_id NUMBER;
178 worker_success BOOLEAN;
179 x_continue BOOLEAN := TRUE;
180 x_success_rows NUMBER := 0;
181 x_failure_rows NUMBER := 0;
182 x_message VARCHAR2(2000);
183 x_user_id NUMBER;
184 x_resp_id NUMBER;
185 x_resp_appl_id NUMBER;
186 x_created_by NUMBER;
187 x_cnt NUMBER;
188 l_err_code NUMBER;
189 l_err_msg VARCHAR2(2000);
190 -- added by BBK. for debugging.
191 lProcName VARCHAR2(32) := 'TRANSACT';
192 lProcLocation NUMBER := 0;
193
194 rep_flag_count NUMBER:=0; -- added by sisankar for bug 4920235
195
196 BEGIN
197 x_header_id := Get_Header_Id;
198 x_header_id1 := x_header_id;
199
200 lProcLocation := 10;
201
202 Set_Vars;
203 /*BA#IIIP*/
204 showProgress(
205 processingMode => p_mode
206 , headerId => 0
207 , procName => lProcName
208 , procLocation => lProcLocation
209 , showMessage => ' Returned from Set_vars');
210 /*EA#IIIP*/
211
212
213 /**
214
215 -- bugfix 2449452: Modified the logic as per the open-interface manual.
216
217 -- If a GROUP_ID is specified, then only those records in the group
218 -- and in the Pending status would by considered for processing. If no group is specified while
219 -- launching the import program, then the processing is done group by group for all the
220 -- pending records in the respective group. Unique GROUP_IDs are assigned to records in
221 -- which the GROUP_ID is null.
222
223 **/
224
225 if (p_mode = WSMPINVL.CONCURRENT) then
226 --
227 -- Populate the group_id column if null only in case of CONCURRENT mode.
228 --
229 if (p_group_id IS NULL ) then
230
231 if (g_debug = 'Y') then
232 fnd_file.put_line(fnd_file.log, 'p_group_id IS NULL');
233 end if;
234
235 open no_grp_id_rows;
236 loop
237 fetch no_grp_id_rows into l_header_id;
238 exit when no_grp_id_rows%NOTFOUND;
239
240 UPDATE wsm_lot_split_merges_interface
241 SET group_id = wsm_lot_sm_ifc_header_s.nextval
242 WHERE header_id = l_header_id
243 RETURNING group_id INTO l_group_id;
244
245 IF (g_debug = 'Y') then
246 IF sql%found THEN
247 fnd_file.put_line(fnd_file.log, 'Updated group_id to '||l_group_id||
248 ' for header_id:'||l_header_id);
249 ELSE
250 fnd_file.put_line(fnd_file.log, 'Failed to Update group_id for header_id: '||l_header_id);
251 END IF;
252 END IF;
253
254 commit;
255 end loop;
256
257 close no_grp_id_rows;
258 end if;
259 end if;
260
261 FOR txn in txns LOOP
262 IF (g_debug = 'Y') THEN
263 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------');
264 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Processing Header Id : ' || txn.header_Id );
265 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------');
266 END IF;
267
268 x_continue := TRUE;
269
270 /**************Header Validations**************************************/
271
272 IF x_continue THEN
273 lProcLocation := 20;
274 Validate_Header(txn.header_Id ,x_header_id ,o_err_message );
275 showProgress(
276 processingMode => p_mode
277 , headerId => txn.header_id
278 , procName => lProcName
279 , procLocation => lProcLocation
280 , showMessage => ('x_header_id '||x_header_id|| ' after validate_header') );
281
282
283 IF x_header_id=-1 and p_mode = CONCURRENT THEN
284
285 lProcLocation := 30;
286
287 Error_All(txn.header_id, p_group_id,o_err_message);
288
289 /*BA#IIIP*/
290 showProgress(
291 processingMode => p_mode
292 , headerId => txn.header_id
293 , procName => lProcName
294 , procLocation => lProcLocation
295 , showMessage => o_err_message);
296 /*EA#IIIP*/
297
298 x_continue := FALSE;
299
300 END IF;
301
302 IF x_header_id = -1 and p_mode= ONLINE THEN
303 return;
304 END IF;
305
306 END IF;
307
308 /******************Starting Lots Validattions**************************/
309
310
311 IF x_continue THEN
312
313 lProcLocation := 40;
314 Validate_parent (txn.header_Id ,x_header_id ,o_err_message );
315 showProgress(
316 processingMode => p_mode
317 , headerId => txn.header_id
318 , procName => lProcName
319 , procLocation => lProcLocation
320 , showMessage => ('x_header_id '||x_header_id|| ' after validate_parent') );
321
322
323
324 IF x_header_id=-1 and p_mode = CONCURRENT THEN
325 lProcLocation := 50;
326 Error_All(txn.header_id, p_group_id,o_err_message);
327
328 /*BA#IIIP*/
329 showProgress(
330 processingMode => p_mode
331 , headerId => txn.header_id
332 , procName => lProcName
333 , procLocation => lProcLocation
334 , showMessage => o_err_message);
335 /*EA#IIIP*/
336 x_continue := FALSE;
337 END IF;
338
339 IF x_header_id = -1 and p_mode= ONLINE THEN
340 return;
341 END IF;
342
343
344 END IF;
345
346 IF x_continue THEN
347
348 lProcLocation := 60;
349 Validate_Starting(txn.header_id ,x_header_id ,o_err_message );
350
351 showProgress(
352 processingMode => p_mode
353 , headerId => txn.header_id
354 , procName => lProcName
355 , procLocation => lProcLocation
356 , showMessage => ('x_header_id '||x_header_id||' after validate_starting'));
357
358 IF x_header_id=-1 and p_mode = CONCURRENT THEN
359 lProcLocation := 70;
360 Error_All(txn.header_id, p_group_id,o_err_message);
361
362 /*BA#IIIP*/
363 showProgress(
364 processingMode => p_mode
365 , headerId => txn.header_id
366 , procName => lProcName
367 , procLocation => lProcLocation
368 , showMessage => o_err_message);
369 /*EA#IIIP*/
370 x_continue := FALSE;
371 END IF;
372
373 IF x_header_id = -1 and p_mode= ONLINE THEN
374 return;
375 END IF;
376
377 END IF;
378
379 /*********************Resulting Lots Validations**********************/
380
381 IF x_continue THEN
382 lProcLocation := 80;
383
384 Validate_resulting(txn.header_id ,x_header_id ,o_err_message );
385
386 showProgress(
387 processingMode => p_mode
388 , headerId => txn.header_id
389 , procName => lProcName
390 , procLocation => lProcLocation
391 , showMessage => ('x_header_id '||x_header_id||' after validate_resulting'));
392
393 IF x_header_id=-1 and p_mode = CONCURRENT THEN
394 lProcLocation := 90;
395 Error_All(txn.header_id, p_group_id,o_err_message);
396
397 /*BA#IIIP*/
398 showProgress(
399 processingMode => p_mode
400 , headerId => txn.header_id
401 , procName => lProcName
402 , procLocation => lProcLocation
403 , showMessage => o_err_message);
404 /*EA#IIIP*/
405 x_continue := FALSE;
406 END IF;
407 IF x_header_id = -1 and p_mode= ONLINE THEN
408 return;
409 END IF;
410 END IF;
411
412 /****************************Validation for Merge*********************/
413
414 IF x_continue and txn.transaction_type_id = MERGE THEN
415 lProcLocation := 100;
416
417 -- added by sisankar for bug 4920235
418
419 select count(1) into rep_flag_count from wsm_starting_lots_interface wsli
420 where wsli.representative_flag='Y' and wsli.header_id=txn.header_id;
421
422 IF rep_flag_count <> 1 THEN
423
424 fnd_message.set_name('WSM', 'WSM_REPRESENTATIVE_LOT');
425 o_err_message:=fnd_message.get;
426 Error_All(txn.header_id, p_group_id,o_err_message);
427 x_header_id :=-1;
428 x_continue := FALSE;
429
430 END IF;
431
432 IF x_continue THEN
433 Validate_Merge(txn.header_id,x_header_id,o_err_message);
434
435 showProgress(
436 processingMode => p_mode
437 , headerId => txn.header_id
438 , procName => lProcName
439 , procLocation => lProcLocation
440 , showMessage => ('x_header_id '||x_header_id|| ' after validate_merge') );
441
442 IF x_header_id=-1 and p_mode = CONCURRENT THEN
443 lProcLocation := 110;
444 Error_All(txn.header_id, p_group_id,o_err_message);
445
446 /*BA#IIIP*/
447 showProgress(
448 processingMode => p_mode
449 , headerId => txn.header_id
450 , procName => lProcName
451 , procLocation => lProcLocation
452 , showMessage => o_err_message);
453 /*EA#IIIP*/
454 x_continue := FALSE;
455 END IF;
456 END IF;
457 IF x_header_id = -1 and p_mode= ONLINE THEN
458 return;
459 END IF;
460 IF not x_continue THEN
461 x_return_id := -1;
462 END IF;
463
464 END IF;
465 /*************************************************************************/
466
467 IF x_continue THEN
468 x_created_by := txn.created_by;
469 x_success_rows := x_success_rows + 1;
470
471 -- If this is a SPLIT TXN create_extra_record.
472
473 IF txn.transaction_type_id = SPLIT THEN
474
475 lProcLocation := 120;
476 Create_Extra_Record(txn.header_id,
477 l_err_code ,
478 l_err_msg );
479 END IF; -- EndIf txntype Split.
480
481 IF l_err_code > 0 THEN
482 o_err_code := l_err_code;
483 o_err_message := l_err_msg;
484
485 /*BA#IIIP*/
486 showProgress(
487 processingMode => p_mode
488 , headerId => txn.header_id
489 , procName => lProcName
490 , procLocation => lProcLocation
491 , showMessage => l_err_msg);
492 /*EA#IIIP*/
493 return;
494 ELSIF l_err_code = -10 THEN
495 o_err_code := -10 ;
496 o_err_message := l_err_msg;
497
498 END IF;
499
500 -- Call create_Mtl_Records.
501
502 /*
503 ** Before making a call to create mtl records,
504 ** we need to have the transaction_id generated
505 ** so that the mtl records have a reference back
506 ** to the transacton_id created in OSFM using
507 ** the source_code and source_line_id.
508 ** - Bala BALAKUMAR.
509 */
510
511 -- commented out by abedajna for perf. tuning
512 /* select wsm_split_merge_transactions_s.nextval
513 ** into txn.transaction_id
514 ** from dual;
515 **
516 **
517 ** update wsm_lot_split_merges_interface
518 ** set transaction_id = txn.transaction_id
519 ** Where header_id = txn.header_id;
520 */
521
522 -- begin modification by abedajna for perf. tuning
523
524 lProcLocation := 130;
525 update wsm_lot_split_merges_interface
526 set transaction_id = wsm_split_merge_transactions_s.nextval
527 Where header_id = txn.header_id
528 returning transaction_id into txn.transaction_id;
529
530 -- end modification by abedajna for perf. tuning
531
532 Savepoint if_err_in_create_mtl_rec;
533 lProcLocation := 140;
534 Create_Mtl_Records(p_header_id => txn.header_id,
535 p_header_id1 => x_header_id1,
536 p_transaction_id => txn.transaction_id,
537 p_transaction_type => txn.transaction_type_id,
538 x_err_code => l_err_code ,
539 x_err_msg => l_err_msg );
540 -- added by sisankar for bug 4920235
541 IF l_err_code > 0 THEN
542 o_err_code := l_err_code;
543 o_err_message := l_err_msg;
544
545 /*BA#IIIP*/
546 showProgress(
547 processingMode => p_mode
548 , headerId => txn.header_id
549 , procName => lProcName
550 , procLocation => lProcLocation
551 , showMessage => l_err_msg);
552 /*EA#IIIP*/
553 x_continue:=FALSE;
554 x_failure_rows:=x_failure_rows+1;
555 x_err_cnt:=x_failure_rows;
556 rollback to if_err_in_create_mtl_rec;
557 Error_all(txn.header_id,null, l_err_msg);
558 return;
559 END IF;
560
561
562
563 END IF;
564
565 IF not x_continue THEN
566 x_failure_rows:=x_failure_rows+1;
567 x_err_cnt:=x_failure_rows;
568 END IF;
569 END LOOP; -- End of Transaction_Id Loop.
570
571 /**************************************************************************/
572
573 IF p_mode = CONCURRENT AND x_success_rows > 0 THEN
574
575 --bugfix 2449452. added these debug stmts
576 IF (g_debug = 'Y') THEN
577 FND_FILE.PUT_LINE(FND_FILE.LOG, '-----------------------------------------------------------------');
578 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling Inventory Worker to process records for txn header id '||
579 x_header_id1);
580 FND_FILE.PUT_LINE(FND_FILE.LOG, '-----------------------------------------------------------------');
581 END IF;
582
583 lProcLocation := 150;
584 worker_success := Launch_Worker(x_header_id1, x_message);
585
586
587 /*BA#IIIP*/
588
589 If worker_success THEN
590 fnd_file.put_line(fnd_file.log,
591 'Inventory Worker returned success.');
592 showProgress(
593 processingMode => p_mode
594 , headerId => -9999
595 , procName => lProcName
596 , procLocation => lProcLocation
597 , showMessage => 'Inventory Worker returned success.');
598
599 Else
600
601 showProgress(
602 processingMode => p_mode
603 , headerId => -9999
604 , procName => lProcName
605 , procLocation => lProcLocation
606 , showMessage => substr(('Inventory Worker Failure; '||
607 x_message), 1, 2000)
608 );
609
610 End If;
611
612 /*EA#IIIP*/
613
614 /* -- commented out by Bala BALAKUMAR, Bug:1362550.
615
616 IF worker_success THEN
617
618
619 lProcLocation := 160;
620 FOR txn in txns LOOP
621
622 select count(*) into x_cnt
623 from mtl_material_transactions
624 where source_line_id=txn.transaction_id
625 and organization_id = txn.organization_id -- ADD: BUG2832025/2841055
626 and transaction_date = txn.transaction_date; -- ADD: BUG2832025/2841055
627
628
629 IF x_cnt >0 then
630 Success_all(txn.header_id,
631 p_group_id,
632 l_err_code ,
633 l_err_msg );
634
635 END IF;
636 IF not worker_success THEN
637
638
639 Error_all(txn.header_id, p_group_id, x_message);
640 END IF;
641
642 END LOOP;
643 END IF;
644
645 ** End of commenting out script Bug: 1362550.
646 */
647
648 -- Introduced to fix Bug: 1362550.
649
650 FOR txn in txns LOOP
651
652 IF worker_success THEN
653
654 lProcLocation := 160;
655
656 BEGIN
657
658 select 1 into x_cnt
659 from mtl_material_transactions
660 where transaction_source_type_id = 13 --ADD : BUG 3756725
661 --and transaction_id = txn.transaction_id --removed: bug 4401205
662 and transaction_date = txn.transaction_date --ADD : bug 4919094
663 and organization_id = txn.organization_id --ADD : BUG 3756725
664 and source_code = 'WSM'
665 and source_line_id = txn.transaction_id
666 and rownum=1; --ADD : BUG 3756725
667
668
669
670 EXCEPTION
671 when no_data_found Then
672 Error_all(txn.header_id, p_group_id, x_message);
673 o_err_message := x_message;
674 x_err_cnt := x_err_cnt+1;
675
676 END;
677
678 IF x_cnt >0 then
679 lProcLocation := 170;
680 Success_all(txn.header_id,
681 p_group_id,
682 l_err_code ,
683 l_err_msg , /*BA#IIIP*/
684 p_mode); /*Bug 4779518 fix*/
685 showProgress(
686 processingMode => p_mode
687 , headerId => txn.header_id
688 , procName => lProcName
689 , procLocation => lProcLocation
690 , showMessage => l_err_msg);
691 /* bug 4919094 begin : moved up call to enter_genealogy_records */
692 l_err_code := 0 ;
693 lProcLocation := 175;
694
695 enter_genealogy_records(txn.transaction_id,
696 txn.transaction_type_id,
697 txn.header_id,
698 txn.process_status,
699 l_err_code,
700 l_err_msg );
701
702 IF ( l_err_code <> 0 ) THEN
703
704 x_message := 'Insert into genealogy tables failed : '||l_err_msg ;
705
706 showProgress(
707 processingMode => p_mode
708 , headerId => txn.header_id
709 , procName => lProcName
710 , procLocation => lProcLocation
711 , showMessage => x_message);
712 END IF ;
713 /* bug 4919094 end */
714
715 /*EA#IIIP*/
716
717 END IF;
718
719
720 END IF;
721
722 IF not worker_success THEN
723
724 /*BA#IIIP*/
725 If x_message is NULL Then
726
727 x_message := 'Inventory Worker Failed.';
728
729 End If;
730 /*EA#IIIP*/
731
732 lProcLocation := 180;
733 Error_all(txn.header_id, p_group_id, x_message);
734 o_err_message := x_message;
735
736 /*BA#IIIP*/
737 x_err_cnt := x_err_cnt+1;
738 showProgress(
739 processingMode => p_mode
740 , headerId => txn.header_id
741 , procName => lProcName
742 , procLocation => lProcLocation
743 , showMessage => x_message);
744 /*EA#IIIP*/
745
746 END IF;
747
748 END LOOP;
749
750 -- Added code for inserting records into MTL_OBJECT_GENEALOGY table
751 /**************************************************************************************
752 bug 4660398 : Commented out and moved enter_genealogy_records above after success_all
753
754
755
756 lProcLocation := 190;
757
758 IF x_success_rows > 0 THEN
759 --bugfix 2624791 replace the cursor txn with comp_txns.
760 --the success_all has being called, process_status was set to completed, the cursor
761 --txns will returns nothing.
762
763 FOR txn IN comp_txns LOOP
764
765 l_err_code := 0 ;
766
767 enter_genealogy_records ( txn.transaction_id,
768 txn.transaction_type_id,
769 txn.header_id,
770 txn.process_status,
771 l_err_code,
772 l_err_msg );
773
774 IF ( l_err_code <> 0 ) THEN
775
776 x_message := 'Insert into genealogy tables failed : '||l_err_msg ;
777
778 showProgress(
779 processingMode => p_mode
780 , headerId => txn.header_id
781 , procName => lProcName
782 , procLocation => lProcLocation
783 , showMessage => x_message);
784 END IF ;
785
786 END LOOP;
787
788 END IF ;
789
790 -- End of the MTL_OBJECT_GENEALOGY changes
791 End : commented out for bug fix 4660398
792 **************************************************************************************/
793
794
795 ELSIF p_mode = ONLINE AND x_success_rows > 0 THEN
796 Success_all( p_header_id,
797 p_group_id,
798 l_err_code ,
799 l_err_msg ,
800 p_mode); /*Bug 4779518 fix*/
801
802 END IF;
803
804 x_header_id:=x_header_id1;
805
806
807
808 EXCEPTION
809 when others THEN
810 Error_all(p_header_id, p_group_id, SQLERRM);
811 x_err_cnt := x_err_cnt+1;
812 return;
813
814 END Transact;
815
816 /*******************************************************************************/
817
818 /**
819 * this procedure calls the API that inserts
820 * into mtl_object_genealogy
821 **/
822
823 PROCEDURE enter_genealogy_records ( p_transaction_id NUMBER ,
824 p_transaction_type_id NUMBER,
825 p_header_id NUMBER,
826 p_process_status NUMBER ,
827 err_status OUT NOCOPY NUMBER ,
828 o_err_message OUT NOCOPY VARCHAR ) IS
829
830 l_return_status VARCHAR2(200);
831 l_msg_count NUMBER;
832 l_msg_data VARCHAR2(200);
833 l_err_msg VARCHAR2(2000);
834 l_genealogy_type NUMBER;
835 l_proc_name VARCHAR2(30) := 'enter_genealogy_record';
836 l_stmt_num NUMBER := 0;
837 l_header_id NUMBER :=0;
838 i NUMBER :=0 ;
839
840 CURSOR genealogy is
841
842 SELECT wssl.lot_number,
843 wssl.inventory_item_id,
844 wssl.organization_id,
845 wsrl.lot_number parent_lot_number,
846 wsrl.inventory_item_id parent_inventory_item_id,
847 wsrl.organization_id parent_organization_id
848 FROM wsm_sm_starting_lots wssl,
849 wsm_sm_resulting_lots wsrl
850 WHERE wssl.transaction_id = wsrl.transaction_id --p_transaction_id
851 -- To avoid MERGE JOIN CARTESIAN
852 AND wsrl.transaction_id = p_transaction_id;
853
854
855
856 BEGIN
857
858 err_status := 0;
859
860 l_stmt_num := 10;
861
862 IF (p_transaction_type_id in (1,2,3)) THEN
863
864 l_stmt_num := 20;
865
866 FOR g_rec in genealogy LOOP
867 --Bug 5359483:If both parent and child lots are same,genealogy
868 --API should not be called.Following if condition is added.
869 if not (g_rec.lot_number = g_rec.parent_lot_number AND
870 g_rec.organization_id = g_rec.parent_organization_id AND
871 g_rec.inventory_item_id = g_rec.parent_inventory_item_id) THEN
872
873 inv_genealogy_pub.insert_genealogy
874 ( p_api_version => 1.0,
875 p_object_type => 1,
876 p_parent_object_type => 1,
877 p_object_number => g_rec.lot_number,
878 p_inventory_item_id => g_rec.inventory_item_id,
879 p_org_id => g_rec.organization_id,
880 p_parent_object_number => g_rec.parent_lot_number,
881 p_parent_inventory_item_id => g_rec.parent_inventory_item_id,
882 p_parent_org_id => g_rec.parent_organization_id,
883 p_genealogy_origin => 3,
884 p_genealogy_type => 4,
885 p_origin_txn_id => p_transaction_id,
886 x_return_status => l_return_status,
887 x_msg_count => l_msg_count,
888 x_msg_data => l_msg_data ) ;
889
890 /* ST : bug fix 3732133 : Added condition to check for the return status and also nvl as the INV API returns msg_count as NULL in some cases */
891 IF ( nvl(l_msg_count,-1) = 1) THEN
892
893 /* ST : bug fix 3732133 : commented out this code as l_msg_data will not be the message code */
894 /*fnd_message.set_name('INV',l_msg_data);
895 l_err_msg := fnd_message.get;*/
896 l_err_msg := l_msg_data;
897
898 showProgress(
899 processingMode => p_process_status
900 , headerId => l_header_id
901 , procName => l_proc_name
902 , procLocation => l_stmt_num
903 , showMessage => l_err_msg );
904
905 /* ST : bug fix 3732133 : Added code to check for the return status */
906 if l_return_status <> fnd_api.g_ret_sts_success then
907 err_status := -1;
908 o_err_message := l_err_msg;
909 end if;
910
911 -- Resolved Bug 2095269 by replacing ELSE by ELSIF (...) THEN
912 -- made change to the following one line only
913 /* ST : bug fix 3732133 : Added condition to check for the return status and also nvl as the INV API returns msg_count as NULL in some cases */
914 ELSIF ( nvl(l_msg_count,-1) > 0 ) THEN
915
916 FOR i IN 1..l_msg_count LOOP
917
918 l_msg_data := fnd_msg_pub.get;
919 l_err_msg := fnd_message.get;
920
921 showProgress(
922 processingMode => p_process_status
923 , headerId => l_header_id
924 , procName => l_proc_name
925 , procLocation => l_stmt_num
926 , showMessage => l_err_msg );
927 /* ST : bug fix 3732133 : Added code to check for the return status */
928 if l_return_status <> fnd_api.g_ret_sts_success then
929 err_status := -1;
930 o_err_message := l_err_msg;
931 end if;
932
933 END LOOP;
934 END IF;
935
936
937 showProgress(
938 processingMode => p_process_status
939 , headerId => l_header_id
940 , procName => l_proc_name
941 , procLocation => l_stmt_num
942 , showMessage => l_err_msg);
943 end if;--Bug 5359483:End of check on if the parent and child lot is same or not.
944
945 END LOOP;
946 END IF;
947 l_stmt_num:= 30;
948
949 EXCEPTION
950 WHEN OTHERS THEN
951
952 err_status := -1;
953 o_err_message := 'exception block of enter_genealogy_record';
954 showProgress(
955 processingMode => p_process_status
956 , headerId => p_header_id
957 , procName => l_proc_name
958 , procLocation => l_stmt_num
959 , showMessage => l_err_msg);
960
961
962 END enter_genealogy_records ;
963
964
965 /*******************************************************************************/
966
967 FUNCTION Get_Header_Id RETURN NUMBER IS
968 X_Header_Id NUMBER;
969
970
971 BEGIN
972
973 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
974 INTO X_Header_Id
975 FROM DUAL;
976
977 return(X_Header_Id);
978
979 END Get_Header_Id;
980
981
982 /*
983 FUNCTION Get_Header_Id RETURN NUMBER IS
984
985 BEGIN
986
987 return(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL);
988
989 END Get_Header_Id;
990 */
991
992
993
994 --------------------------------------------------------------------------
995
996 PROCEDURE Set_Vars IS
997 BEGIN
998 USER := FND_GLOBAL.user_id;
999 LOGIN := FND_GLOBAL.login_id;
1000 PROGRAM := FND_GLOBAL.conc_program_id;
1001 REQUEST := FND_GLOBAL.conc_request_id;
1002 PROGAPPL := FND_GLOBAL.prog_appl_id;
1003
1004 SELECT transaction_type_id
1005 INTO WSMISSUE
1006 FROM mtl_transaction_types
1007 --WHERE transaction_type_name = 'Miscellaneous issue';
1008 WHERE transaction_type_id = 32;
1009
1010 SELECT transaction_type_id
1011 INTO WSMRECEIPT
1012 FROM mtl_transaction_types
1013 --WHERE transaction_type_name = 'Miscellaneous receipt';
1014 WHERE transaction_type_id = 42;
1015
1016 EXCEPTION
1017 WHEN no_data_found then
1018 null;
1019
1020
1021 END Set_Vars;
1022
1023 -------------------------------------------------------------------------
1024
1025 PROCEDURE Validate_parent(
1026 p_header_id IN NUMBER,
1027 err_status OUT NOCOPY NUMBER ,
1028 o_err_message OUT NOCOPY VARCHAR) is
1029
1030 cursor c is
1031 SELECT
1032 organization_id
1033 FROM wsm_starting_lots_interface
1034 WHERE header_id = p_header_id;
1035
1036 x_dummy NUMBER;
1037
1038
1039 BEGIN
1040 /*
1041 ** For each record in the starting lot interface WSLI table,
1042 ** select the org and ensure in the Parent table WLSMI a
1043 ** record exists.
1044 */
1045
1046
1047 for crec in c loop
1048
1049 BEGIN
1050 SELECT 1
1051 into x_dummy
1052 FROM wsm_lot_split_merges_interface wlsmi
1053 WHERE wlsmi.header_id = p_header_id
1054 AND wlsmi.organization_id = crec.organization_id;
1055
1056 EXCEPTION when others THEN
1057 --fnd_message.set_name('WSM', 'WSM_RESULTING_DIFFERENT');
1058 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1059 FND_MESSAGE.SET_TOKEN('FLD_NAME',
1060 'organization_id. Mismatch between Header and Starting Lot.');
1061 o_err_message:= fnd_message.get;
1062 err_status := -1;
1063 return;
1064 END;
1065
1066 end loop;
1067
1068 END ;
1069
1070 ------------------------------------------------------------------
1071
1072 PROCEDURE Validate_starting(
1073 p_header_id IN NUMBER,
1074 err_status OUT NOCOPY NUMBER ,
1075 o_err_message OUT NOCOPY VARCHAR) is
1076 cursor c is
1077 SELECT
1078 lot_number,
1079 /*BA#1414465*/
1080 inventory_item_id,
1081 /*EA#1414465*/
1082 organization_id,
1083 quantity,
1084 subinventory_code,
1085 locator_id,
1086 revision,
1087 last_updated_by,
1088 created_by
1089 FROM wsm_starting_lots_interface
1090 WHERE header_id = p_header_id;
1091 --bugfix 1823316
1092
1093 cursor lot (org_id NUMBER, lot_n VARCHAR2) is
1094 select inventory_item_id, subinventory_code, locator_id, revision, quantity
1095 from wsm_source_lots_v
1096 where lot_number = lot_n
1097 and organization_id = org_id;
1098 --end 1823316
1099
1100 x_dummy NUMBER;
1101 x_cnt1 NUMBER;
1102 x_temp_id NUMBER;
1103 x_item_id NUMBER;
1104 x_sub VARCHAR2(10);
1105 x_locator_id NUMBER;
1106 x_revision VARCHAR2(3);
1107 x_quantity NUMBER;
1108 x_org_id NUMBER;
1109 x_return_code NUMBER;
1110 x_err_code NUMBER;
1111 x_err_msg VARCHAR2(2000);
1112 mtl_unique NUMBER;
1113 unique_lot BOOLEAN := TRUE;
1114 valid_lot BOOLEAN;
1115 invalid_field VARCHAR2(30);
1116 -- added by BBK for debugging
1117 lProcName VARCHAR2(32) := 'validate_starting';
1118 lProcLocation NUMBER := 0;
1119
1120 l_serial_ctrl NUMBER;
1121
1122 BEGIN
1123
1124 /*
1125 ** For each record Header Id, get the records in the
1126 ** WSLI table and then do the validation of records.
1127 */
1128
1129
1130 FOR crec in c LOOP
1131
1132 BEGIN -- FOR UNHANDLED EXCEPTION HANDLING for INNER BLOCKS --BBK
1133
1134 -- validate User.
1135
1136 lProcLocation := 10;
1137
1138 BEGIN
1139 SELECT 1
1140 into x_dummy
1141 FROM fnd_user
1142 WHERE user_id = crec.created_by
1143 AND sysdate between start_date
1144 and nvl(END_date, sysdate + 1);
1145
1146 EXCEPTION when others THEN
1147 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1148 FND_MESSAGE.SET_TOKEN('FLD_NAME','created_by in Staring Lots');
1149 o_err_message:= fnd_message.get;
1150 err_status := -1;
1151 return;
1152 END;
1153 /*
1154 BEGIN
1155 x_org_id:=crec.organization_id;
1156
1157 x_return_code:= WSMPUTIL.CHECK_WSM_ORG(
1158 x_org_id
1159 ,x_err_code
1160 ,x_err_msg );
1161
1162 IF (x_return_code=0) then
1163 fnd_message.set_name('WSM','WSM_ORG_INVALID');
1164 -- FND_MESSAGE.SET_TOKEN('FLD_NAME','wsm_organization_id');
1165 o_err_message:= fnd_message.get;
1166 err_status := -1;
1167 --fnd_message.error;
1168 END IF;
1169 END;
1170 */
1171 -- check if this lot_number exists in mtl_transaction_lots_temp
1172
1173
1174 /*
1175 ** To process the same Lot Numbers for different Inventory Lot Txns
1176 ** this check will fail all the other txns except the first one.
1177 ** Hence, we should NOT do a check like this just with the lot number
1178 ** alone. We should ensure that if the previous txns receipt qty is
1179 ** same as the current txns issue qty, then we are fine.
1180 **
1181 ** - Bala Balakumar, Sep 29th, 2000.
1182 **
1183 ** BBK - Oct 24th, 2000 - Update
1184 ** Pseudocode for check.
1185 ** Here are the conditions which are fine;
1186 ** - No Records in MTLT.
1187 ** - If a record exists in MTLT then the following check should be ensured.
1188 ** Get the Latest Receipt Quantity from MMTT for this
1189 ** org/item/lot/subinventory/locator information for this
1190 ** transaction_type_id = 42
1191 ** transaction_action_id = 27
1192 ** transaction_source_type_id = 13
1193 ** source_code = WSM
1194 ** OR
1195 ** get the max(transaction_temp_id) from mtlt for this lot_number
1196 ** and then match the quantities in mmtt for this transaction_temp_id.
1197 ** ASSUMPTION: max(transaction_temp_id) gives the latest transaction.
1198 ** VALIDATE with the PEERS.
1199 **
1200 */
1201
1202 BEGIN
1203 lProcLocation := 20;
1204
1205 /* ST 3303884 : code to check if serial-controlled..*/
1206 l_serial_ctrl := 0;
1207
1208 select nvl(serial_number_control_code,2)
1209 into l_serial_ctrl
1210 from mtl_system_items_kfv
1211 WHERE organization_id = crec.organization_id
1212 AND inventory_item_id = crec.inventory_item_id;
1213
1214 IF l_serial_ctrl <> 1 then
1215 fnd_message.set_name('WSM', 'WSM_NO_INV_LOT_TXN');
1216 o_err_message:= fnd_message.get;
1217 err_status := -1;
1218 return;
1219 END IF;
1220
1221 /* ST 3303884 : code to check if serial-controlled */
1222
1223 SELECT 1 into x_dummy
1224 FROM DUAL
1225 Where exists (select 1
1226 From mtl_transaction_lots_temp
1227 WHERE lot_number=crec.lot_number);
1228
1229 IF x_dummy <>0 then
1230 lProcLocation := 30;
1231
1232 SELECT 0 into x_dummy -- Fine if it satisfies this condition.
1233 FROM mtl_material_transactions_temp mmtt
1234 WHERE mmtt.organization_id = crec.organization_id
1235 and mmtt.inventory_item_id = crec.inventory_item_id
1236 and NVL(mmtt.lot_number, '@#$') = crec.lot_number
1237 and mmtt.subinventory_code = crec.subinventory_code
1238 and NVL(mmtt.locator_id, -9999) = NVL(crec.locator_id, -9999)
1239 and mmtt.transaction_type_id = 42 -- Miscellaneous Receipt
1240 and mmtt.transaction_action_id = 27 -- Receipt into stores
1241 and mmtt.transaction_source_type_id = 13 -- Inventory
1242 and crec.quantity = ((-1) * mmtt.transaction_quantity)
1243 and mmtt.transaction_date = (
1244 SELECT max(mmtt2.transaction_date)
1245 FROM mtl_material_transactions_temp mmtt2
1246 WHERE mmtt2.organization_id = crec.organization_id
1247 and mmtt2.inventory_item_id = crec.inventory_item_id
1248 and NVL(mmtt2.lot_number, '@#$') = crec.lot_number
1249 and mmtt2.subinventory_code = crec.subinventory_code
1250 and NVL(mmtt2.locator_id, -9999) = NVL(crec.locator_id, -9999)
1251 );
1252
1253 End If;
1254
1255 If x_dummy <> 0 Then
1256
1257 fnd_message.set_name('WSM', 'WSM_PENDING_TXN');
1258
1259 FND_MESSAGE.SET_TOKEN('TABLE',
1260 'Starting Lot:'||crec.lot_number
1261 ||'Table: mtl_transaction_lots_temp ');
1262
1263 o_err_message:= substr(fnd_message.get, 1, 2000);
1264 err_status := -1;
1265 return;
1266
1267 END IF;
1268 -- Exception added by Bala, Aug 25th, 2000.
1269
1270 EXCEPTION
1271 When NO_DATA_FOUND Then
1272 Null;
1273
1274
1275
1276 END;
1277
1278 /*EA#1422110*/ -- For processing same org/item/lots in a group.
1279
1280
1281 /*BD#1422110*/
1282 /*
1283 **
1284
1285 BEGIN
1286 SELECT count(*) into x_dummy
1287 FROM mtl_transaction_lots_temp
1288 WHERE lot_number=crec.lot_number;
1289
1290 IF x_dummy <>0 then
1291 fnd_message.set_name('WSM', 'WSM_PENDING_TXN');
1292 FND_MESSAGE.SET_TOKEN('TABLE',
1293 'Starting Lot:'||crec.lot_number
1294 ||'Table: mtl_transaction_lots_temp ');
1295 o_err_message:= substr(fnd_message.get(), 1, 2000);
1296 err_status := -1;
1297 return;
1298 END IF;
1299
1300 -- Exception added by Bala, Aug 25th, 2000.
1301
1302 EXCEPTION
1303 When NO_DATA_FOUND Then
1304 Null;
1305
1306 END ;
1307
1308 **
1309 */
1310 /*ED#1422110*/
1311
1312
1313 -- validate last_updated_by.
1314
1315 BEGIN
1316 lProcLocation := 40;
1317 SELECT 1
1318 into x_dummy
1319 FROM fnd_user
1320 WHERE user_id = crec.last_updated_by
1321 AND sysdate between start_date
1322 and nvl(END_date, sysdate + 1);
1323
1324 EXCEPTION when others THEN
1325 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1326 FND_MESSAGE.SET_TOKEN('FLD_NAME','last_updated_by in Starting Lots');
1327 o_err_message:= fnd_message.get;
1328 err_status := -1;
1329 return;
1330 END;
1331 /*
1332 BEGIN
1333 SELECT 1
1334 into x_dummy
1335 FROM wsm_lot_split_merges_interface
1336 WHERE transaction_id = p_header_id
1337 AND organization_id = crec.organization_id;
1338 EXCEPTION when others THEN
1339 --fnd_message.set_name('WSM', 'WSM_RESULTING_DIFFERENT');
1340 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1341 FND_MESSAGE.SET_TOKEN('FLD_NAME','organization_id in Starting Lots');
1342 o_err_message:= fnd_message.get;
1343 err_status := -1;
1344 return;
1345 END;
1346 */
1347 /*
1348 ** Check if this lot exists with this organization in WSM_SOURCE_LOTS_V
1349 ** If Yes, then select inventory_item_id, subinventory_code, locator_id, revision
1350 ** quantity and then validate it.
1351 */
1352
1353
1354 BEGIN
1355 lProcLocation := 50;
1356 SELECT inventory_item_id, subinventory_code,
1357 locator_id, revision, quantity
1358 into x_item_id, x_sub, x_locator_id, x_revision, x_quantity
1359 FROM WSM_SOURCE_LOTS_V
1360 WHERE lot_number = crec.lot_number
1361 AND organization_id = crec.organization_id;
1362
1363 EXCEPTION
1364 when too_many_rows then --bugfix 1823316
1365 select lot_number_uniqueness
1366 into mtl_unique
1367 from mtl_parameters
1368 where organization_id = crec.organization_id;
1369
1370 if mtl_unique <> 2 then
1371 --bugfix 1995378: changed message_name
1372 fnd_message.set_name('WSM', 'WSM_ORG_LOT_NONUNIQUE');
1373 --FND_MESSAGE.SET_TOKEN('FLD_NAME','lot_number in Starting Lots');
1374 o_err_message:= fnd_message.get;
1375 err_status := -1;
1376 return;
1377 else
1378 unique_lot := FALSE;
1379 end if;
1380
1381
1382 when others THEN
1383 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1384 FND_MESSAGE.SET_TOKEN('FLD_NAME','lot_number in Starting Lots');
1385 o_err_message:= fnd_message.get;
1386 err_status := -1;
1387 return;
1388 END;
1389 --bugfix 1823316 added condition to process multi-item or multi-subinventory lot
1390
1391 if unique_lot then
1392 /*BA#1414465*/
1393
1394 IF x_item_id <> crec.inventory_item_id THEN
1395 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1396 FND_MESSAGE.SET_TOKEN('FLD_NAME','Inventory Item in Starting Lots');
1397 o_err_message:= fnd_message.get;
1398 err_status := -1;
1399 return;
1400 END IF;
1401
1402 /*EA#1414465*/
1403
1404
1405 IF nvl(x_revision,'@@@') <> nvl(crec.revision, '@@@') THEN
1406 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1407 FND_MESSAGE.SET_TOKEN('FLD_NAME','revision in Starting Lots');
1408 o_err_message:= fnd_message.get;
1409 err_status := -1;
1410 return;
1411 END IF;
1412
1413 IF x_sub <> crec.subinventory_code THEN
1414 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1415 FND_MESSAGE.SET_TOKEN('FLD_NAME','subinventory_code in Starting Lots');
1416 o_err_message:= fnd_message.get;
1417 err_status := -1;
1418 return;
1419 END IF;
1420
1421 IF nvl(x_locator_id, -9) <> nvl(crec.locator_id, -9) THEN
1422 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1423 FND_MESSAGE.SET_TOKEN('FLD_NAME','locator_id in Starting Lots');
1424 o_err_message:= fnd_message.get;
1425 err_status := -1;
1426 return;
1427 END IF;
1428
1429
1430
1431 /*BD#1422110*/
1432 /*
1433 ** We should not check for this, because with the current
1434 ** pending records the quantities might have changed.
1435 **
1436
1437 IF x_quantity <> crec.quantity THEN
1438 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1439 FND_MESSAGE.SET_TOKEN('FLD_NAME','quantity in Starting Lots');
1440 o_err_message:= fnd_message.get;
1441 err_status := -1;
1442 return;
1443 END IF;
1444 **
1445 */
1446 /*ED#1422110*/
1447
1448 else
1449 begin
1450 if lot%ISOPEN = false then
1451 open lot(crec.organization_id, crec.lot_number);
1452 end if;
1453
1454 loop
1455 valid_lot := TRUE;
1456 fetch lot into x_item_id, x_sub, x_locator_id, x_revision, x_quantity;
1457
1458 IF x_item_id <> crec.inventory_item_id THEN
1459 invalid_field := 'Inventory Item';
1460 valid_lot := FALSE;
1461 goto loop_end;
1462 END IF;
1463
1464 IF nvl(x_revision,'@@@') <> nvl(crec.revision, '@@@') THEN
1465 invalid_field := 'Revision';
1466 valid_lot :=FALSE;
1467 goto loop_end;
1468 END IF;
1469
1470 IF x_sub <> crec.subinventory_code THEN
1471 invalid_field := 'Subinventory_Code';
1472 valid_lot :=FALSE;
1473 goto loop_end;
1474 END IF;
1475
1476 IF nvl(x_locator_id, -9) <> nvl(crec.locator_id, -9) THEN
1477 invalid_field :='Locator_id';
1478 valid_lot :=FALSE;
1479 goto loop_end;
1480 END IF;
1481 <<loop_end>>
1482 exit when valid_lot;
1483 exit when lot%NOTFOUND;
1484
1485 end loop;
1486
1487 close lot;
1488
1489 if valid_lot = FALSE then
1490 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1491 fnd_message.set_token('FLD_NAME', invalid_field||' in Starting Lots');
1492 o_err_message :=fnd_message.get;
1493 err_status :=-1;
1494 return;
1495 end if;
1496
1497 exception
1498 when others then
1499 if lot%ISOPEN then
1500 close lot;
1501 end if;
1502
1503 o_err_message := substr(SQLERRM,1,2000);
1504 err_status := -1;
1505 showProgress(
1506 processingMode => CONCURRENT
1507 , headerId => p_header_id
1508 , procName => lProcName
1509 , procLocation => lProcLocation
1510 , showMessage => o_err_message);
1511
1512 return;
1513 end;
1514 end if;
1515 --endfix 1823316
1516
1517
1518 EXCEPTION -- added by BBK
1519
1520 when OTHERS Then
1521 o_err_message:= substr(SQLERRM,1,2000);
1522 err_status := -1;
1523
1524 showProgress(
1525 processingMode => CONCURRENT
1526 , headerId => p_header_id
1527 , procName => lProcName
1528 , procLocation => lProcLocation
1529 , showMessage => o_err_message);
1530
1531 return;
1532
1533
1534
1535 END; -- END OF UNHANDLED EXCEPTION ..BBK
1536
1537
1538
1539
1540 END LOOP;
1541
1542 END Validate_Starting;
1543
1544 -------------------------------------------------------------------------
1545 /* Notes added by BBK.
1546 ** Each SQL should have an Exception Handler in this Procedure.
1547 */
1548
1549 PROCEDURE Validate_resulting(
1550 p_header_id IN NUMBER,
1551 err_status OUT NOCOPY NUMBER ,
1552 o_err_message OUT NOCOPY VARCHAR) is
1553 cursor c is
1554 SELECT
1555 m.transaction_type_id,
1556 r.lot_number,
1557 r.organization_id,
1558 r.inventory_item_id,
1559 r.quantity,
1560 r.subinventory_code,
1561 r.locator_id,
1562 r.revision,
1563 r.last_updated_by,
1564 r.created_by
1565 FROM wsm_lot_split_merges_interface m,
1566 wsm_resulting_lots_interface r
1567 WHERE r.header_id = m.header_id
1568 AND m.header_id = p_header_id;
1569
1570 x_dummy NUMBER;
1571 x_cnt1 NUMBER;
1572 x_temp_id NUMBER;
1573 x_loc_success BOOLEAN;
1574 x_segs VARCHAR2(10000);
1575 x_item_loc_control NUMBER;
1576 x_sub_loc_control NUMBER;
1577 x_org_loc_control NUMBER;
1578 x_restrict_locators_code NUMBER;
1579 x_loc_id NUMBER;
1580 x_org_id NUMBER;
1581 x_return_code NUMBER;
1582 x_err_code NUMBER;
1583 x_err_msg VARCHAR2(2000);
1584 x_exp_ast_profile NUMBER; --bug1857638
1585
1586 wsm_resulting_same_error EXCEPTION; --abedajna
1587 validate_lot_dup_error EXCEPTION; --abedajna
1588
1589 -- added by BBK for debugging
1590 lProcName VARCHAR2(32) := 'validate_resulting';
1591 lProcLocation NUMBER := 0;
1592
1593
1594 l_serial_ctrl NUMBER;
1595
1596 BEGIN
1597
1598 FOR crec in c LOOP
1599
1600 BEGIN -- FOR UNHANDLED EXCEPTION HANDLING for INNER BLOCKS --BBK
1601
1602 -- validate created_by user.
1603
1604 BEGIN
1605 lprocLocation := 10;
1606 SELECT 1
1607 into x_dummy
1608 FROM fnd_user
1609 WHERE user_id = crec.created_by
1610 AND sysdate between start_date
1611 and nvl(END_date, sysdate + 1);
1612 EXCEPTION when others THEN
1613 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1614 FND_MESSAGE.SET_TOKEN('FLD_NAME','created_by in Resulting Lots');
1615 o_err_message:= fnd_message.get;
1616 err_status := -1;
1617 return;
1618 END;
1619
1620 -- Check if valid Org.
1621 lprocLocation := 20;
1622
1623 BEGIN
1624 x_org_id:=crec.organization_id;
1625
1626 x_return_code:= WSMPUTIL.CHECK_WSM_ORG(
1627 x_org_id
1628 ,x_err_code
1629 ,x_err_msg );
1630
1631 IF (x_return_code=0) then
1632 fnd_message.set_name('WSM','WSM_ORG_INVALID');
1633 -- FND_MESSAGE.SET_TOKEN('FLD_NAME','organization_id in Resulting Lots');
1634 o_err_message:= fnd_message.get;
1635 err_status := -1;
1636 return;
1637 --fnd_message.error;
1638 END IF;
1639 END;
1640
1641
1642 /*BA#1549476*/
1643 /*
1644 ** To process the same Lot Numbers for different Inventory Lot Txns
1645 ** this check will fail all the other txns except the first one.
1646 ** Hence, we should NOT do a check like this just with the lot number
1647 ** alone. We should ensure that if the previous txns receipt qty is
1648 ** same as the current txns issue qty, then we are fine.
1649 **
1650 ** Pseudocode for check.
1651 ** Here are the conditions which are fine;
1652 ** - No Records in MTLT.
1653 ** - If a record exists in MTLT then the following check should be ensured.
1654 ** Get the Latest Receipt Quantity from MMTT for this
1655 ** org/item/lot/subinventory/locator information for this
1656 ** transaction_type_id = 42
1657 ** transaction_action_id = 27
1658 ** transaction_source_type_id = 13
1659 ** source_code = WSM
1660 **
1661 */
1662
1663 lprocLocation := 30;
1664
1665 BEGIN
1666 /* ST 3303884 : code to check if serial-controlled..*/
1667 l_serial_ctrl := 0;
1668
1669 select nvl(serial_number_control_code,2)
1670 into l_serial_ctrl
1671 from mtl_system_items_kfv
1672 WHERE organization_id = crec.organization_id
1673 AND inventory_item_id = crec.inventory_item_id;
1674
1675 IF l_serial_ctrl <> 1 then
1676 fnd_message.set_name('WSM', 'WSM_NO_INV_LOT_TXN');
1677 o_err_message:= fnd_message.get;
1678 err_status := -1;
1679 return;
1680 END IF;
1681
1682 /* ST 3303884: code to check if assembly is serial-controlled */
1683
1684
1685
1686 SELECT 1 into x_dummy
1687 FROM DUAL
1688 Where exists (select 1
1689 From mtl_transaction_lots_temp
1690 WHERE lot_number=crec.lot_number);
1691
1692 IF x_dummy <>0 then
1693
1694 lprocLocation := 40;
1695
1696 SELECT 0 into x_dummy -- Fine if it satisfies this condition.
1697 FROM mtl_material_transactions_temp mmtt
1698 WHERE mmtt.organization_id = crec.organization_id
1699 and mmtt.inventory_item_id = crec.inventory_item_id
1700 and NVL(mmtt.lot_number, '@#$') = crec.lot_number
1701 and mmtt.subinventory_code = crec.subinventory_code
1702 and NVL(mmtt.locator_id, -9999) = NVL(crec.locator_id, -9999)
1703 and mmtt.transaction_type_id = 42 -- Miscellaneous Receipt
1704 and mmtt.transaction_action_id = 27 -- Receipt into stores
1705 and mmtt.transaction_source_type_id = 13 -- Inventory
1706 and crec.quantity = ((-1) * mmtt.transaction_quantity)
1707 and mmtt.transaction_date = (
1708 SELECT max(mmtt2.transaction_date)
1709 FROM mtl_material_transactions_temp mmtt2
1710 WHERE mmtt2.organization_id = crec.organization_id
1711 and mmtt2.inventory_item_id = crec.inventory_item_id
1712 and NVL(mmtt2.lot_number, '@#$') = crec.lot_number
1713 and mmtt2.subinventory_code = crec.subinventory_code
1714 and NVL(mmtt2.locator_id, -9999) = NVL(crec.locator_id, -9999)
1715 );
1716
1717
1718 End If;
1719
1720 If x_dummy <> 0 Then
1721 fnd_message.set_name('WSM', 'WSM_PENDING_TXN');
1722 FND_MESSAGE.SET_TOKEN('TABLE',
1723 'Resulting Lot:'||crec.lot_number
1724 ||'Table: mtl_transaction_lots_temp ');
1725 o_err_message:= substr(fnd_message.get(), 1, 2000);
1726 err_status := -1;
1727 return;
1728
1729 END IF;
1730
1731 EXCEPTION
1732 When NO_DATA_FOUND Then
1733 Null;
1734
1735 END;
1736
1737 /*EA#1549476*/
1738
1739 /*BD#1549476*/
1740 /*
1741 **
1742
1743 -- check if this lot_number exists in mtl_transaction_lots_temp
1744
1745 BEGIN
1746 SELECT count(*) into x_cnt1
1747 FROM mtl_transaction_lots_temp
1748 WHERE lot_number=crec.lot_number;
1749
1750 IF x_cnt1<>0 THEN
1751 fnd_message.set_name('WSM', 'WSM_PENDING_TXN');
1752 FND_MESSAGE.SET_TOKEN('TABLE',
1753 'Resulting Lot:'||crec.lot_number
1754 ||'Table: mtl_transaction_lots_temp ');
1755 o_err_message:= substr(fnd_message.get(), 1, 2000);
1756 err_status := -1;
1757 showProgress(
1758 processingMode => CONCURRENT
1759 , headerId => p_header_id
1760 , procName => lProcName
1761 , procLocation => lProcLocation
1762 , showMessage => o_err_message);
1763
1764 return;
1765 END IF;
1766
1767 -- Exception added by Bala, Aug 25th, 2000.
1768
1769 EXCEPTION
1770 When NO_DATA_FOUND Then
1771 Null;
1772
1773 END ;
1774
1775 **
1776 */
1777 /*ED#1549476*/
1778
1779 -- validate last_updated_by.
1780 lprocLocation := 40;
1781
1782 BEGIN
1783 SELECT 1
1784 into x_dummy
1785 FROM fnd_user
1786 WHERE user_id = crec.last_updated_by
1787 AND sysdate between start_date
1788 and nvl(END_date, sysdate + 1);
1789 EXCEPTION when others THEN
1790 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1791 FND_MESSAGE.SET_TOKEN('FLD_NAME','last_updated_by in Resulting Lots');
1792 o_err_message:= fnd_message.get;
1793 err_status := -1;
1794 return;
1795 END;
1796
1797 -- Check if the organization is same as the Header Record.
1798 lprocLocation := 50;
1799
1800 BEGIN
1801 SELECT 1
1802 into x_dummy
1803 FROM wsm_lot_split_merges_interface
1804 WHERE header_id = p_header_id
1805 AND organization_id = crec.organization_id;
1806 EXCEPTION when others THEN
1807 fnd_message.set_name('WSM', 'WSM_RESULTING_DIFFERENT');
1808 FND_MESSAGE.SET_TOKEN('FLD_NAME','Resulting Lot.organization_id');
1809 o_err_message:= fnd_message.get;
1810 err_status := -1;
1811 return;
1812 END;
1813
1814 -- check if the Resulting quantity is Non-Zero.
1815 lprocLocation := 60;
1816
1817 IF crec.quantity <= 0 THEN
1818 fnd_message.set_name('WSM', 'WSM_QUANTITY_GREATER_THAN_ZERO');
1819 o_err_message:= fnd_message.get;
1820 err_status := -1;
1821 return;
1822 END IF;
1823
1824 IF crec.transaction_type_id = 1 THEN
1825
1826 -- If transaction_type_id = 1 (SPLIT Txn) Then
1827
1828 -- Resulting Lot should NOT be same as Starting Lot.
1829
1830
1831 -- commented out by abedajna on 10/12/00 for performance tuning.
1832 /* BEGIN
1833 ** SELECT 1
1834 ** into x_dummy
1835 ** FROM dual
1836 ** WHERE not exists
1837 ** (SELECT 1
1838 ** FROM wsm_starting_lots_interface s
1839 ** WHERE s.header_id = p_header_id
1840 ** and s.lot_number = crec.lot_number);
1841 ** EXCEPTION
1842 **
1843 ** when others THEN
1844 ** fnd_message.set_name('WSM', 'WSM_RESULTING_SAME');
1845 ** FND_MESSAGE.SET_TOKEN('FLD_NAME','lot_number');
1846 ** o_err_message:= fnd_message.get;
1847 ** err_status := -1;
1848 ** return;
1849 **
1850 ** END;
1851 */
1852
1853 -- modified by abedajna on 10/12/00 for performance tuning.
1854
1855
1856 BEGIN
1857
1858 lprocLocation := 70;
1859 x_dummy := 0;
1860
1861 SELECT 1
1862 into x_dummy
1863 FROM wsm_starting_lots_interface s
1864 WHERE s.header_id = p_header_id
1865 and s.lot_number = crec.lot_number;
1866
1867
1868 IF x_dummy <> 0 THEN
1869 RAISE wsm_resulting_same_error;
1870 END IF;
1871
1872 EXCEPTION
1873
1874 when wsm_resulting_same_error THEN
1875 fnd_message.set_name('WSM', 'WSM_RESULTING_SAME');
1876 FND_MESSAGE.SET_TOKEN('FLD_NAME','lot_number');
1877 o_err_message:= fnd_message.get;
1878 err_status := -1;
1879 return;
1880
1881
1882 when too_many_rows THEN
1883 fnd_message.set_name('WSM', 'WSM_RESULTING_SAME');
1884 FND_MESSAGE.SET_TOKEN('FLD_NAME','lot_number');
1885 o_err_message:= fnd_message.get;
1886 err_status := -1;
1887 return;
1888
1889
1890 when no_data_found THEN
1891 NULL;
1892
1893 when others THEN
1894 x_err_code := SQLCODE;
1895 o_err_message:= substr(('WSMPINVL.Validate_resulting'||SQLERRM), 1, 2000);
1896 err_status := -1;
1897 return;
1898
1899 END;
1900
1901
1902 -- end of modification by abedajna on 10/12/00 for performance tuning.
1903
1904
1905 -- check if the Resulting Lot will create a duplicate lot already
1906 -- existing in wip_entities, mtl_lot_numbers.
1907
1908
1909 /* BEGIN
1910 ** SELECT 1
1911 ** into x_dummy
1912 ** FROM dual
1913 ** WHERE not exists
1914 ** (
1915 ** SELECT 1
1916 ** FROM wip_entities
1917 ** WHERE wip_entity_name = crec.lot_number
1918 ** AND organization_id = crec.organization_id
1919 ** UNION
1920 ** SELECT 1
1921 ** FROM mtl_lot_numbers
1922 ** WHERE lot_number = crec.lot_number
1923 ** );
1924 ** EXCEPTION when others THEN
1925 ** fnd_message.set_name('WSM', 'WSM_VALIDATE_LOT_DUP');
1926 ** o_err_message:= fnd_message.get;
1927 ** err_status := -1;
1928 ** return;
1929 **
1930 */
1931
1932 -- modification begin for perf. tuning.. abedajna 10/12/00
1933
1934 BEGIN
1935
1936 x_dummy := 0;
1937
1938 SELECT 1
1939 into x_dummy
1940 FROM wip_entities
1941 WHERE wip_entity_name = crec.lot_number
1942 AND organization_id = crec.organization_id
1943 UNION ALL
1944 SELECT 1
1945 FROM mtl_lot_numbers
1946 WHERE lot_number = crec.lot_number
1947 AND inventory_item_id = crec.inventory_item_id --bugfix 2069033: added item_id condn.
1948 AND organization_id = crec.organization_id; -- 4401205: added org_id
1949 -- Should not be able to create a new lot for the SAME item.
1950 -- But, should be able to create lot if it exists with a
1951 -- DIFFERENT item.
1952
1953
1954 IF x_dummy <> 0 THEN
1955 RAISE validate_lot_dup_error;
1956 END IF;
1957
1958 EXCEPTION
1959
1960
1961 when validate_lot_dup_error THEN
1962 fnd_message.set_name('WSM', 'WSM_VALIDATE_LOT_DUP');
1963 o_err_message:= fnd_message.get;
1964 err_status := -1;
1965 return;
1966
1967
1968
1969 when too_many_rows THEN
1970 fnd_message.set_name('WSM', 'WSM_VALIDATE_LOT_DUP');
1971 o_err_message:= fnd_message.get;
1972 err_status := -1;
1973 return;
1974
1975
1976 when no_data_found THEN
1977 NULL;
1978
1979
1980 when others THEN
1981 x_err_code := SQLCODE;
1982 o_err_message:= substr(('WSMPINVL.Validate_resulting'||SQLERRM), 1, 2000);
1983 err_status := -1;
1984 return;
1985
1986
1987 -- modification end for perf. tuning.. abedajna 10/12/00
1988
1989 END;
1990
1991
1992
1993 --ELSIF crec.transaction_type_id <> 2 THEN -- Bug#1844972
1994 ELSIF crec.transaction_type_id = 4 THEN -- Bug#1844972
1995
1996 -- ELSE IF the transaction_type_id <> 2 (NOT a MERGE Txn and NOT a SPLIT) Then
1997 -- Resulting and Starting Lot should have the same LOT NUMBER.
1998
1999
2000 BEGIN
2001 lprocLocation := 80;
2002 SELECT 1
2003 into x_dummy
2004 FROM wsm_starting_lots_interface
2005 WHERE header_id = p_header_id
2006 and lot_number = crec.lot_number;
2007
2008
2009 EXCEPTION when others THEN
2010 fnd_message.set_name('WSM', 'WSM_RESULTING_DIFFERENT');
2011 FND_MESSAGE.SET_TOKEN('FLD_NAME','lot_number');
2012 o_err_message:= fnd_message.get;
2013 err_status := -1;
2014 return;
2015
2016 END;
2017
2018 END IF;
2019
2020
2021
2022 IF crec.transaction_type_id = 3 THEN
2023
2024 -- If the transaction_type_id = 3 (TRANSLATE Transaction) Then
2025 -- Item Id can not be same.
2026 -- Bug#1844972 Atleast Lot or Item should be different.
2027
2028 lprocLocation := 90;
2029 BEGIN
2030 SELECT inventory_item_id
2031 into x_dummy
2032 FROM wsm_starting_lots_interface
2033 WHERE header_id = p_header_id
2034 and lot_number = crec.lot_number
2035 and inventory_item_id = crec.inventory_item_id; --Bug#1844972
2036
2037 IF (x_dummy = crec.inventory_item_id )then
2038 fnd_message.set_name('WSM', 'WSM_RESULTING_SAME');
2039 FND_MESSAGE.SET_TOKEN('FLD_NAME','Both LotNumber and Assembly ');
2040 o_err_message:= fnd_message.get;
2041 err_status := -1;
2042 return;
2043 END IF ;
2044
2045 EXCEPTION
2046 when no_data_found Then
2047 Null;
2048 showProgress(
2049 processingMode => CONCURRENT
2050 , headerId => p_header_id
2051 , procName => lProcName
2052 , procLocation => lProcLocation
2053 , showMessage =>'Alteast Lot or Item is different between Starting and Resulting');
2054
2055 END;
2056 END IF; -- EndIf txnTypeId = 3
2057
2058 -- Check if Item is lot_controlled, Inventory_item, transaction Enabled.
2059
2060 lprocLocation := 100;
2061
2062 BEGIN
2063 SELECT 1
2064 into x_dummy
2065 FROM mtl_system_items
2066 WHERE inventory_item_id = crec.inventory_item_id
2067 and organization_id = crec.organization_id
2068 and mtl_transactions_enabled_flag = 'Y'
2069 and lot_control_code=2
2070 and inventory_item_flag = 'Y';
2071 EXCEPTION when others THEN
2072 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2073 FND_MESSAGE.SET_TOKEN('FLD_NAME','inventory_item_id in Resulting Lots');
2074 o_err_message:= fnd_message.get;
2075 err_status := -1;
2076 return;
2077
2078
2079 END;
2080
2081 -- Check if the Item Revision details are correct.
2082 lprocLocation := 110;
2083
2084 BEGIN
2085 SELECT 1
2086 into x_dummy
2087 FROM mtl_System_items
2088 WHERE inventory_item_id = crec.inventory_item_id
2089 and organization_id = crec.organization_id
2090 and ((crec.revision is not null
2091 and revision_qty_control_code <> 1) or
2092 ((crec.revision is null or
2093 crec.revision = '0')
2094 and revision_qty_control_code = 1));
2095 EXCEPTION when others THEN
2096 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2097 FND_MESSAGE.SET_TOKEN('FLD_NAME','revision in Resulting Lots');
2098 o_err_message:= fnd_message.get;
2099 err_status := -1;
2100
2101 END;
2102
2103 lprocLocation := 120;
2104
2105 IF crec.transaction_type_id <> 3 THEN
2106
2107 -- IF NOT Translation Txn Then Org,Item,Revision should be same
2108 -- for atleast one record between starting and resulting lots.
2109
2110 -- commented out by abedajna on 10/13/00 for performance tuning.
2111 /* BEGIN
2112 ** SELECT 1
2113 ** into x_dummy
2114 ** FROM dual
2115 ** WHERE exists
2116 ** (SELECT 1
2117 ** FROM wsm_starting_lots_interface
2118 ** WHERE header_id = p_header_id
2119 ** and inventory_item_id = crec.inventory_item_id
2120 ** and organization_id = crec.organization_id
2121 ** and nvl(revision,'@@@') = nvl(crec.revision,'@@@'));
2122 ** EXCEPTION when others THEN
2123 ** fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2124 ** FND_MESSAGE.SET_TOKEN('FLD_NAME','revision in Resulting Lots');
2125 ** o_err_message:= fnd_message.get;
2126 ** err_status := -1;
2127 ** return;
2128 **
2129 ** END;
2130 */
2131 -- modified by abedajna on 10/13/00 for performance tuning.
2132
2133 BEGIN
2134
2135 SELECT 1
2136 into x_dummy
2137 FROM wsm_starting_lots_interface
2138 WHERE header_id = p_header_id
2139 and inventory_item_id = crec.inventory_item_id
2140 and organization_id = crec.organization_id
2141 and nvl(revision,'@@@') = nvl(crec.revision,'@@@');
2142
2143 EXCEPTION
2144
2145
2146 when too_many_rows THEN
2147 NULL;
2148
2149
2150 when others THEN
2151 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2152 FND_MESSAGE.SET_TOKEN('FLD_NAME','revision in Resulting Lots');
2153 o_err_message:= fnd_message.get;
2154 err_status := -1;
2155 return;
2156
2157 END;
2158
2159
2160 -- end of modification by abedajna on 10/13/00 for performance tuning.
2161
2162 ELSIF crec.revision IS NOT NULL THEN
2163
2164 -- ELSIF TRANSLATE Txn and ITemRevision is NOT NULL Then
2165 -- check if valid revision in mtl_item_revisions.
2166
2167 BEGIN
2168 SELECT 1
2169 into x_dummy
2170 FROM mtl_item_revisions
2171 WHERE inventory_item_id = crec.inventory_item_id
2172 and organization_id = crec.organization_id
2173 and revision = crec.revision;
2174 EXCEPTION when others THEN
2175 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2176 FND_MESSAGE.SET_TOKEN('FLD_NAME','revision in Resulting Lots');
2177 o_err_message:= fnd_message.get;
2178 err_status := -1;
2179 return;
2180
2181 END;
2182
2183 END IF; -- EndIf transaction_type_id <> 3
2184
2185 BEGIN
2186
2187 -- Check for the valid subinventory code.
2188
2189 -- commented out by abedajna on 10/12/00 for perf. tuning
2190 /*
2191 ** SELECT 1 into x_dummy FROM dual WHERE exists(
2192 ** SELECT 1
2193 ** FROM MTL_SUBINVENTORIES_VAL_V MSVV,
2194 ** MTL_SYSTEM_ITEMS MSI
2195 ** WHERE MSVV.ORGANIZATION_ID = crec.ORGANIZATION_ID
2196 ** AND MSVV.SECONDARY_INVENTORY_NAME =
2197 ** crec.SUBINVENTORY_CODE
2198 ** AND crec.inventory_ITEM_ID = MSI.INVENTORY_ITEM_ID
2199 ** AND crec.orgANIZATION_ID = MSI.ORGANIZATION_ID
2200 ** AND MSI.RESTRICT_SUBINVENTORIES_CODE <> 1
2201 ** AND MSI.INVENTORY_ASSET_FLAG = 'N'
2202 ** UNION
2203 ** SELECT 1
2204 ** FROM MTL_SUB_AST_TRK_VAL_V MSVV,
2205 ** MTL_SYSTEM_ITEMS MSI
2206 ** WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
2207 ** AND MSVV.SECONDARY_INVENTORY_NAME =
2208 ** crec.subinventory_code
2209 ** AND crec.inventory_ITEM_ID = MSI.INVENTORY_ITEM_ID
2210 ** AND crec.orgANIZATION_ID = MSI.ORGANIZATION_ID
2211 ** AND MSI.RESTRICT_SUBINVENTORIES_CODE <> 1
2212 ** AND MSI.INVENTORY_ASSET_FLAG = 'Y'
2213 ** UNION
2214 ** SELECT 1
2215 ** FROM MTL_ITEM_SUB_AST_TRK_VAL_V MSVV,
2216 ** MTL_SYSTEM_ITEMS MSI
2217 ** WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
2218 ** AND MSVV.SECONDARY_INVENTORY_NAME =
2219 ** crec.subinventory_code
2220 ** AND MSVV.inventory_item_id = crec.inventory_item_id
2221 ** AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
2222 ** AND MSI.inventory_item_id= crec.inventory_item_id
2223 ** AND MSI.organization_id = crec.organization_id
2224 ** AND MSI.INVENTORY_ASSET_FLAG = 'Y'
2225 ** UNION
2226 ** SELECT 1
2227 ** FROM MTL_ITEM_SUB_VAL_V MSVV,
2228 ** MTL_SYSTEM_ITEMS MSI
2229 ** WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
2230 ** AND MSVV.SECONDARY_INVENTORY_NAME =
2231 ** crec.subinventory_code
2232 ** AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
2233 ** AND MSVV.inventory_item_id = crec.inventory_item_id
2234 ** AND MSI.inventory_item_id= crec.inventory_item_id
2235 ** AND MSI.organization_id = crec.organization_id
2236 ** AND MSI.INVENTORY_ASSET_FLAG = 'N');
2237 **
2238 ** EXCEPTION when others THEN
2239 ** fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2240 ** FND_MESSAGE.SET_TOKEN('FLD_NAME','subinventory_code in Resulting Lots');
2241 ** o_err_message:= fnd_message.get;
2242 ** err_status := -1;
2243 ** return;
2244 */
2245
2246 -- modification end for perf. tuning.. abedajna 10/12/00
2247 lprocLocation := 130;
2248 -- modification end for perf. tuning.. abedajna 10/12/00
2249
2250 --bugfix 1857638
2251 -- Modified validation of subinventory, so that if profile 'Allow Expense to Asset Transfer' is
2252 -- set to 'Yes', Asset item can be transfer into Expense Subinventory..
2253 FND_PROFILE.get('INV:EXPENSE_TO_ASSET_TRANSFER', x_dummy);
2254 x_exp_ast_profile := x_dummy;
2255 -- end 1857638
2256
2257
2258 SELECT 1 into x_dummy
2259 FROM MTL_SUBINVENTORIES_VAL_V MSVV,
2260 MTL_SYSTEM_ITEMS MSI
2261 WHERE MSVV.ORGANIZATION_ID = crec.ORGANIZATION_ID
2262 AND MSVV.SECONDARY_INVENTORY_NAME =
2263 crec.SUBINVENTORY_CODE
2264 AND crec.inventory_ITEM_ID = MSI.INVENTORY_ITEM_ID
2265 AND crec.orgANIZATION_ID = MSI.ORGANIZATION_ID
2266 AND MSI.RESTRICT_SUBINVENTORIES_CODE <> 1
2267 AND (MSI.INVENTORY_ASSET_FLAG = 'N' OR x_exp_ast_profile = 1) --bugfix 1857638
2268 UNION ALL
2269 SELECT 1
2270 FROM MTL_SUB_AST_TRK_VAL_V MSVV,
2271 MTL_SYSTEM_ITEMS MSI
2272 WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
2273 AND MSVV.SECONDARY_INVENTORY_NAME =
2274 crec.subinventory_code
2275 AND crec.inventory_ITEM_ID = MSI.INVENTORY_ITEM_ID
2276 AND crec.orgANIZATION_ID = MSI.ORGANIZATION_ID
2277 AND MSI.RESTRICT_SUBINVENTORIES_CODE <> 1
2278 AND MSI.INVENTORY_ASSET_FLAG = 'Y'
2279 UNION ALL
2280 SELECT 1
2281 FROM MTL_ITEM_SUB_AST_TRK_VAL_V MSVV,
2282 MTL_SYSTEM_ITEMS MSI
2283 WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
2284 AND MSVV.SECONDARY_INVENTORY_NAME =
2285 crec.subinventory_code
2286 AND MSVV.inventory_item_id = crec.inventory_item_id
2287 AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
2288 AND MSI.inventory_item_id= crec.inventory_item_id
2289 AND MSI.organization_id = crec.organization_id
2290 AND MSI.INVENTORY_ASSET_FLAG = 'Y'
2291 UNION ALL
2292 SELECT 1
2293 FROM MTL_ITEM_SUB_VAL_V MSVV,
2294 MTL_SYSTEM_ITEMS MSI
2295 WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
2296 AND MSVV.SECONDARY_INVENTORY_NAME =
2297 crec.subinventory_code
2298 AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
2299 AND MSVV.inventory_item_id = crec.inventory_item_id
2300 AND MSI.inventory_item_id= crec.inventory_item_id
2301 AND MSI.organization_id = crec.organization_id
2302 AND (MSI.INVENTORY_ASSET_FLAG = 'N'OR x_exp_ast_profile = 1); --1857638
2303
2304 EXCEPTION
2305
2306 when too_many_rows THEN
2307 NULL;
2308
2309 when others THEN
2310 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2311 FND_MESSAGE.SET_TOKEN('FLD_NAME','subinventory_code in Resulting Lots');
2312 o_err_message:= fnd_message.get;
2313 err_status := -1;
2314 return;
2315
2316
2317 -- modification end for perf. tuning.. abedajna 10/12/00
2318
2319 END; -- For this Big Union SQL.
2320
2321 Begin
2322
2323 -- Get Locator Details from MSI, MSECINV, MTLPARM tables.
2324 lprocLocation := 140;
2325
2326 SELECT nvl(msub.locator_type, 1) sub_loc_control,
2327 MP.stock_locator_control_code org_loc_control,
2328 MS.restrict_locators_code,
2329 MS.location_control_code item_loc_control
2330 into x_sub_loc_control,
2331 x_org_loc_control,
2332 x_restrict_locators_code,
2333 x_item_loc_control
2334 FROM mtl_system_items MS,
2335 mtl_secondary_inventories MSUB,
2336 mtl_parameters MP
2337 WHERE MP.organization_id = crec.organization_id
2338 AND MS.organization_id = crec.organization_id
2339 AND MS.inventory_item_id = crec.inventory_item_id
2340 AND MSUB.secondary_inventory_name = crec.subinventory_code
2341 AND MSUB.organization_id = crec.organization_id;
2342
2343 x_loc_id := crec.locator_id;
2344
2345 Exception
2346 When OThers Then
2347 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2348 FND_MESSAGE.SET_TOKEN('FLD_NAME','subinventory in Resulting Lots');
2349 o_err_message:= fnd_message.get;
2350 err_status := -1;
2351 return;
2352
2353 End;
2354
2355
2356 -- Check using WIP_LOCATOR.validate locator validity.
2357 lprocLocation := 140;
2358
2359 WIP_LOCATOR.validate(crec.organization_id,
2360 crec.inventory_item_id,
2361 crec.subinventory_code,
2362 x_org_loc_control,
2363 x_sub_loc_control,
2364 x_item_loc_control,
2365 x_restrict_locators_code,
2366 NULL, NULL, NULL, NULL,
2367 x_loc_id,
2368 x_Segs,
2369 x_loc_success);
2370
2371
2372 IF not x_loc_success THEN
2373 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2374 FND_MESSAGE.SET_TOKEN('FLD_NAME','locator in Resulting Lots');
2375 o_err_message:= fnd_message.get;
2376 err_status := -1;
2377 return;
2378 END IF;
2379
2380
2381 EXCEPTION -- added by BBK
2382
2383 When OTHERS Then
2384 o_err_message:= substr(SQLERRM,1,2000);
2385 err_status := -1;
2386
2387 showProgress(
2388 processingMode => CONCURRENT
2389 , headerId => p_header_id
2390 , procName => lProcName
2391 , procLocation => lProcLocation
2392 , showMessage => o_err_message);
2393
2394 return;
2395
2396 END; -- END of UNHANDLED EXCEPTION ..BBK
2397
2398 END LOOP;
2399
2400
2401 END Validate_Resulting;
2402
2403 --------------------------------------------------------------------------
2404
2405 PROCEDURE Validate_Merge(
2406 p_header_id IN NUMBER,
2407 err_status OUT NOCOPY NUMBER ,
2408 o_err_message OUT NOCOPY VARCHAR) is
2409 x_dummy NUMBER;
2410 x_start_ct NUMBER;
2411 x_result_ct NUMBER;
2412 x_transaction_type_id NUMBER;
2413 wsm_inconsitent_sl_error EXCEPTION; --abedajna
2414 check_stlot_merge_err EXCEPTION; --abedajna
2415 x_err_code NUMBER;
2416
2417 BEGIN
2418 err_status :=0;
2419 o_err_message := NULL;
2420
2421 /*
2422 ** For a MERGE txn, ensure that the Resulting Lot
2423 ** is SAME as the STARTING LOT OR it does NOT exist already
2424 ** exist in wip_entities or mtl_lot_numbers tables
2425 */
2426
2427 SELECT transaction_type_id
2428 INTO x_transaction_type_id
2429 FROM wsm_lot_split_merges_interface
2430 WHERE header_id = p_header_id;
2431 BEGIN
2432 SELECT 1
2433 into x_dummy
2434 FROM wsm_resulting_lots_interface crec
2435 WHERE header_id = p_header_id
2436 and (not exists
2437 (
2438 SELECT 1
2439 FROM wip_entities
2440 WHERE wip_entity_name = crec.lot_number
2441 AND organization_id = crec.organization_id
2442 UNION ALL
2443 SELECT 1
2444 FROM mtl_lot_numbers
2445 WHERE lot_number = crec.lot_number
2446 AND inventory_item_id = crec.inventory_item_id -- bugfix 2069033: added item_id condn.
2447 AND organization_id = crec.organization_id --4401205 : added org_id
2448 ) -- Should not be able to create a new lot for the SAME item.
2449 or exists -- But, should be able to create lot if it exists with a
2450 ( -- DIFFERENT item.
2451 SELECT 1
2452 FROM wsm_starting_lots_interface
2453 WHERE header_id = p_header_id
2454 and lot_number = crec.lot_number
2455 )
2456 );
2457 EXCEPTION when others THEN
2458 fnd_message.set_name('WSM', 'WSM_VALIDATE_LOT_DUP');
2459 o_err_message:= fnd_message.get;
2460 err_status := -1;
2461 return;
2462 END;
2463
2464 -- While Merging, Starting Lot can not be repeated as same.
2465
2466 --commented out by abedajna on 10/12/00 for perf. tuning
2467 /* BEGIN
2468 ** SELECT 1
2469 ** into x_dummy
2470 ** FROM dual
2471 ** WHERE not exists
2472 ** (SELECT count(*)
2473 ** FROM wsm_starting_lots_interface
2474 ** WHERE header_id = p_header_id
2475 ** group by lot_number
2476 ** having count(*) > 1);
2477 ** EXCEPTION when others THEN
2478 ** fnd_message.set_name('WSM', 'WSM_CHECK_STLOT_WHILE_MERGE');
2479 ** o_err_message:= fnd_message.get;
2480 ** err_status := -1;
2481 ** return;
2482 ** END;
2483 */
2484
2485 --modifications by abedajna on 10/12/00 for perf. tuning
2486
2487 BEGIN
2488
2489 x_dummy := 0;
2490
2491 SELECT 1
2492 into x_dummy
2493 FROM wsm_starting_lots_interface
2494 WHERE header_id = p_header_id
2495 group by lot_number
2496 having count(*) > 1;
2497
2498
2499 if x_dummy <> 0 then
2500 raise check_stlot_merge_err;
2501 end if;
2502
2503 EXCEPTION
2504
2505 when check_stlot_merge_err THEN
2506 fnd_message.set_name('WSM', 'WSM_CHECK_STLOT_WHILE_MERGE');
2507 o_err_message:= fnd_message.get;
2508 err_status := -1;
2509 return;
2510
2511
2512 when too_many_rows THEN
2513 fnd_message.set_name('WSM', 'WSM_CHECK_STLOT_WHILE_MERGE');
2514 o_err_message:= fnd_message.get;
2515 err_status := -1;
2516 return;
2517
2518
2519 when no_data_found then
2520 null;
2521
2522 when others then
2523 x_err_code := SQLCODE;
2524 o_err_message:= substr(('WSMPINVL.Validate_Merge: '||SQLERRM), 1, 2000);
2525 err_status := -1;
2526 return;
2527
2528 END;
2529
2530 --modifications by abedajna on 10/12/00 for perf. tuning
2531
2532
2533 /*****************************************************************/
2534 /* Some more validations added by Vikram Singhvi */
2535 /*****************************************************************/
2536
2537 BEGIN
2538
2539 SELECT count(*)
2540 INTO x_start_ct
2541 FROM wsm_starting_lots_interface
2542 WHERE header_id = p_header_id;
2543
2544 SELECT count(*)
2545 INTO x_result_ct
2546 FROM wsm_resulting_lots_interface
2547 WHERE header_id = p_header_id;
2548
2549 -- For Merge Txn, More than one Starting Lot should exist.
2550
2551 IF x_transaction_type_id= 2 and x_start_ct <= 1 THEN
2552 fnd_message.set_name('WSM', 'WSM_ONE_SLOT');
2553 o_err_message:= fnd_message.get;
2554 err_status := -1;
2555 return;
2556 END IF;
2557
2558 -- For Split Txn, More than one Resulting Lot should exist.
2559 -- Don't know why a Split condition is checked here (Bala).
2560
2561 IF x_transaction_type_id = 1 and x_result_ct < 1 THEN
2562 fnd_message.set_name('WSM', 'WSM_ATLEAST_ONE_RSLT_WHIL_SPLIT');
2563 o_err_message:= fnd_message.get;
2564 err_status := -1;
2565 return;
2566 END IF;
2567
2568 EXCEPTION --BBK
2569
2570 when others then
2571 x_err_code := SQLCODE;
2572 o_err_message:= substr(('WSMPINVL.Validate_Merge: '||SQLERRM), 1, 2000);
2573 err_status := -1;
2574 return;
2575
2576 END ;
2577
2578 /**************************************************************/
2579 -- Added June 5, 1999 by D. Joffe
2580 -- Check that all items are the same when doing a merge
2581
2582
2583 -- commented out by abedajna on 10/13/00 for performance tuning.
2584 /* BEGIN
2585 ** SELECT 1
2586 ** into x_dummy
2587 ** FROM dual
2588 ** WHERE not exists
2589 ** ( SELECT 1
2590 ** FROM wsm_starting_lots_interface s1,
2591 ** wsm_starting_lots_interface s2
2592 ** WHERE s1.header_id = p_header_id
2593 ** and s2.header_id = p_header_id
2594 ** and (s1.inventory_item_id <> s2.inventory_item_id
2595 ** or nvl(s1.revision, '!@#') <> nvl(s1.revision, '!@#')
2596 ** )
2597 ** );
2598 **
2599 ** EXCEPTION when others THEN
2600 ** fnd_message.set_name('WSM', 'WSM_INCONSISTENT_SL');
2601 ** o_err_message:= fnd_message.get;
2602 ** err_status := -1;
2603 ** return;
2604 **
2605 ** END;
2606 */
2607
2608 -- modified by abedajna on 10/13/00 for performance tuning.
2609
2610 BEGIN
2611 x_dummy := 0;
2612 SELECT 1
2613 into x_dummy
2614 FROM wsm_starting_lots_interface s1,
2615 wsm_starting_lots_interface s2
2616 WHERE s1.header_id = p_header_id
2617 and s2.header_id = p_header_id
2618 and (s1.inventory_item_id <> s2.inventory_item_id
2619 or nvl(s1.revision, '!@#') <> nvl(s1.revision, '!@#'));
2620
2621 IF x_dummy <> 0 THEN
2622 RAISE wsm_inconsitent_sl_error;
2623 END IF;
2624
2625 EXCEPTION
2626
2627 when wsm_inconsitent_sl_error THEN
2628 fnd_message.set_name('WSM', 'WSM_INCONSISTENT_SL');
2629 o_err_message:= fnd_message.get;
2630 err_status := -1;
2631 return;
2632
2633
2634 when too_many_rows THEN
2635 fnd_message.set_name('WSM', 'WSM_INCONSISTENT_SL');
2636 o_err_message:= fnd_message.get;
2637 err_status := -1;
2638 return;
2639
2640
2641 when no_data_found THEN
2642 NULL;
2643
2644 when others THEN
2645 x_err_code := SQLCODE;
2646 o_err_message:= substr(('WSMPINVL.Validate_Merge: '||SQLERRM), 1, 2000);
2647 err_status := -1;
2648 return;
2649
2650 END;
2651
2652
2653 -- end of modification by abedajna on 10/13/00 for performance tuning.
2654
2655
2656 END Validate_Merge;
2657
2658 ----------------------------------------------------------------------
2659
2660 PROCEDURE Validate_Header(
2661 p_header_id IN NUMBER,
2662 err_status OUT NOCOPY NUMBER ,
2663 o_err_message OUT NOCOPY VARCHAR) is
2664
2665 x_transaction_type_id NUMBER;
2666 x_organization_id NUMBER;
2667 x_last_updated_by NUMBER;
2668 x_created_by NUMBER;
2669 x_reason_id NUMBER;
2670 x_transaction_date DATE;
2671 x_dummy NUMBER;
2672 x_start_ct NUMBER;
2673 x_result_ct NUMBER;
2674 x_start_ct1 NUMBER;
2675 x_result_ct1 NUMBER;
2676 x_org_id NUMBER;
2677 x_return_code NUMBER;
2678 x_err_code NUMBER;
2679 x_err_msg VARCHAR2(2000);
2680 l_wmsEnabledFlag VARCHAR2(1);
2681 l_invTxnEnabledFlag NUMBER ;
2682
2683 -- added by BBK for debugging
2684 lProcName VARCHAR2(32) := 'validate_header';
2685 lProcLocation NUMBER := 0;
2686
2687
2688 BEGIN
2689 err_status :=0;
2690 o_err_message := NULL;
2691
2692 -- Get Header Details about the current record
2693
2694 BEGIN -- FOR UNHANDLED EXCEPTION HANDLING for INNER BLOCKS --BBK
2695
2696 lProcLocation := 10;
2697
2698
2699 SELECT transaction_type_id,
2700 organization_id,
2701 last_updated_by,
2702 created_by,
2703 reason_id,
2704 transaction_date
2705 INTO x_transaction_type_id,
2706 x_organization_id,
2707 x_last_updated_by,
2708 x_created_by,
2709 x_reason_id,
2710 x_transaction_date
2711 FROM wsm_lot_split_merges_interface
2712 WHERE header_id = p_header_id;
2713
2714 -- Check if the org is a valid WSM Org.
2715
2716 lProcLocation := 20;
2717 BEGIN
2718
2719 x_org_id:=x_organization_id;
2720
2721 x_return_code:= WSMPUTIL.CHECK_WSM_ORG(
2722 x_org_id
2723 ,x_err_code
2724 ,x_err_msg );
2725 IF (x_return_code=0) then
2726 fnd_message.set_name('WSM','WSM_ORG_INVALID');
2727 o_err_message:= fnd_message.get;
2728 err_status := -1;
2729 return;
2730 --fnd_message.error;
2731 END IF;
2732 END;
2733
2734
2735 /* A check to see if WMS is enabled or not is no longer
2736 done to see if the inv lot transaction is valid. Instead
2737 the inv_lot_txn_enabled column in wsm_parameters is checked
2738 commenting out the following code and adding new code for
2739 the altered check
2740
2741 -- check if org is not WMS enabled
2742 -- Added to disable OSFM transactions in
2743 -- and organization that is WMS enabled
2744
2745
2746 lProcLocation := 25;
2747
2748 BEGIN
2749
2750 l_wmsEnabledFlag := 'N';
2751
2752 SELECT mtl.wms_enabled_flag
2753 INTO l_wmsEnabledFlag
2754 FROM mtl_parameters mtl
2755 WHERE mtl.organization_id = x_organization_id;
2756
2757
2758 IF (l_wmsEnabledFlag = 'Y') THEN
2759 fnd_message.set_name('WSM','WSM_ORG_WMS_ENABLED');
2760 o_err_message:= fnd_message.get;
2761 err_status := -1;
2762 return;
2763 --fnd_message.error;
2764 END IF;
2765 END;
2766
2767
2768 */
2769
2770 -- Begin Addition of code to check INV_LOT_TXN_ENABLED column in
2771 -- WSM_PARAMETERS
2772
2773 lProcLocation := 27;
2774
2775 BEGIN
2776
2777 l_invTxnEnabledFlag := 0;
2778
2779 SELECT wp.inv_lot_txn_enabled
2780 INTO l_invTxnEnabledFlag
2781 FROM wsm_parameters wp
2782 WHERE wp.organization_id = x_organization_id;
2783
2784
2785 IF (nvl(l_invTxnEnabledFlag,0) = 0) THEN
2786 fnd_message.set_name('WSM','WSM_INV_TXN_DISABLED');
2787 o_err_message:= fnd_message.get;
2788 err_status := -1;
2789 return;
2790 --fnd_message.error;
2791 END IF;
2792 END;
2793
2794
2795
2796 -- END changes code to check INV_LOT_TXN_ENABLED column in
2797 -- WSM_PARAMETERS
2798
2799 -- check if the user is a valid user.
2800
2801 lProcLocation := 30;
2802 BEGIN
2803 SELECT 1
2804 into x_dummy
2805 FROM fnd_user
2806 WHERE user_id = x_created_by
2807 AND sysdate between start_date
2808 and nvl(END_date, sysdate + 1);
2809 EXCEPTION when others THEN
2810 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2811 FND_MESSAGE.SET_TOKEN('FLD_NAME','created_by');
2812 o_err_message:= fnd_message.get;
2813 err_status := -1;
2814 return;
2815 END;
2816 -- check if the last_updated_by is a valid user.
2817
2818 lProcLocation := 40;
2819 BEGIN
2820 SELECT 1
2821 into x_dummy
2822 FROM fnd_user
2823 WHERE user_id = x_last_updated_by
2824 AND sysdate between start_date
2825 and nvl(END_date, sysdate + 1);
2826 EXCEPTION when others THEN
2827 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2828 FND_MESSAGE.SET_TOKEN('FLD_NAME','last_updated_by');
2829 o_err_message:= fnd_message.get;
2830 err_status := -1;
2831 return;
2832 END;
2833 /*
2834 BEGIN
2835 SELECT 1
2836 into x_dummy
2837 FROM dual
2838 WHERE exists (
2839 SELECT 1
2840 FROM fnd_user_responsibility f,
2841 fnd_responsibility_tl r
2842 WHERE r.responsibility_name like '%Inventory%'
2843 and r.responsibility_id = f.responsibility_id
2844 and f.user_id = x_created_by);
2845 EXCEPTION when others THEN
2846 return(FALSE);
2847 END;
2848 */
2849 -- Check if the organization is Still an enabled org in OOD.
2850
2851 lProcLocation := 50;
2852 BEGIN
2853 -- changed from org_organization_definitions to HR_ORGANIZATION_UNITS for bug 5051883
2854 -- Performance issue on org_organization_definitions for full table scan on base tables
2855 -- SQL id 16641079
2856
2857 SELECT 1
2858 into x_dummy
2859 FROM HR_ORGANIZATION_UNITS
2860 WHERE organization_id = x_organization_id
2861 and trunc(sysdate) <= nvl(date_to, sysdate + 1);
2862 --bug 5051883
2863 EXCEPTION when others THEN
2864 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2865 FND_MESSAGE.SET_TOKEN('FLD_NAME',
2866 'organization_id in Header in org.definitions');
2867 o_err_message:= fnd_message.get;
2868 err_status := -1;
2869 return;
2870 END;
2871
2872 -- Check if the there is a record in mtl_parameters for this ORG.
2873
2874 lProcLocation := 60;
2875 BEGIN
2876 SELECT 1
2877 into x_dummy
2878 FROM mtl_parameters
2879 WHERE organization_id = x_organization_id;
2880 EXCEPTION when others THEN
2881 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2882 FND_MESSAGE.SET_TOKEN('FLD_NAME','organization_id_mp');
2883 o_err_message:= fnd_message.get;
2884 err_status := -1;
2885 return;
2886 END;
2887
2888 -- Check if this is a valid reason.
2889
2890 lProcLocation := 70;
2891 IF x_reason_id IS NOT NULL THEN
2892 BEGIN
2893 SELECT 1
2894 into x_dummy
2895 FROM mtl_transaction_reasons_val_v
2896 WHERE reason_id = x_reason_id;
2897 EXCEPTION when others THEN
2898 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2899 FND_MESSAGE.SET_TOKEN('FLD_NAME','reason_id');
2900 o_err_message:= fnd_message.get;
2901 err_status := -1;
2902 return;
2903 END;
2904 END IF;
2905
2906 /*
2907 ** Check if the transaction type id is a valid one.
2908 ** WSM_INV_LOT_TXN_TYPE 1 Lot Based Inventory Split
2909 ** WSM_INV_LOT_TXN_TYPE 2 Lot Based Inventory Merge
2910 ** WSM_INV_LOT_TXN_TYPE 3 Lot Based Inventory Translat
2911 ** WSM_INV_LOT_TXN_TYPE 4 Lot Based Inventory Transfer
2912 ** Bala BALAKUMAR, Aug 25th, 2000.
2913 */
2914
2915 lProcLocation := 80;
2916 BEGIN
2917 SELECT 1
2918 into x_dummy
2919 FROM mfg_lookups
2920 WHERE lookup_type = 'WSM_INV_LOT_TXN_TYPE'
2921 and lookup_code = x_transaction_type_id;
2922 EXCEPTION when others THEN
2923 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2924 FND_MESSAGE.SET_TOKEN('FLD_NAME','transaction_type_id in Header.');
2925 o_err_message:= fnd_message.get;
2926 err_status := -1;
2927 return;
2928 END;
2929
2930 -- check to see if the transaction date is in an open period.
2931
2932 lProcLocation := 90;
2933
2934 --BC Bug 3126650.
2935
2936 /* BEGIN
2937 SELECT count(1)
2938 into x_dummy
2939 FROM org_acct_periods
2940 WHERE organization_id = x_organization_id
2941 and period_start_date <= x_transaction_date
2942 and open_flag = 'Y';
2943 */
2944
2945 x_dummy:=WSMPUTIL.GET_INV_ACCT_PERIOD(
2946 x_err_code => x_err_code,
2947 x_err_msg => x_err_msg,
2948 p_organization_id => x_organization_id,
2949 p_date => x_transaction_date);
2950
2951 IF (x_err_code <> 0) THEN
2952 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
2953 FND_MESSAGE.SET_TOKEN('FLD_NAME','transaction_date in Header');
2954 o_err_message:= fnd_message.get;
2955 err_status := -1;
2956 return;
2957 END IF;
2958 -- END;
2959
2960 --EC bug 3126650
2961
2962
2963 BEGIN
2964
2965 lProcLocation := 100;
2966
2967 SELECT count(*)
2968 INTO x_start_ct
2969 FROM wsm_starting_lots_interface
2970 WHERE header_id = p_header_id;
2971
2972 lProcLocation := 110;
2973
2974 SELECT count(*)
2975 INTO x_result_ct
2976 FROM wsm_resulting_lots_interface
2977 WHERE header_id = p_header_id;
2978
2979 -- Check to see if the is a split txn then there should be only
2980 -- only one starting lot. BBK.
2981
2982 IF x_transaction_type_id = 1 and x_start_ct <> 1 THEN
2983 fnd_message.set_name('WSM', 'WSM_MULTIPLE_SLOTS');
2984 o_err_message:= fnd_message.get;
2985 err_status := -1;
2986 return;
2987 END IF;
2988
2989 --bugfix 2533069 remove the comment out of the following.
2990 -- we must check number of resulting lot for split transaction.
2991 -- replace message name WSM_MORETHAN_ONE_RESLOT_SPLIT with WSM_ONE_RLOT
2992 -- as previous one does not exists anymore.
2993 IF x_transaction_type_id = 1 and x_result_ct < 1 THEN
2994 fnd_message.set_name('WSM', 'WSM_ONE_RLOT');
2995 o_err_message:= fnd_message.get;
2996 err_status := -1;
2997 return;
2998 END IF;
2999
3000 -- Check to see if this is a XFR txn then there should be only
3001 -- only one starting lot and a resulting lot.
3002
3003 IF x_transaction_type_id = 4 and x_start_ct <> 1 THEN
3004 fnd_message.set_name('WSM', 'WSM_MULTIPLE_SLOTS');
3005 o_err_message:= fnd_message.get;
3006 err_status := -1;
3007 return;
3008 END IF;
3009
3010 IF x_transaction_type_id = 4 and x_result_ct <> 1 THEN
3011 fnd_message.set_name('WSM', 'WSM_MULTIPLE_RLOTS');
3012 o_err_message:= fnd_message.get;
3013 err_status := -1;
3014 return;
3015 END IF;
3016
3017 -- Check to see if this is a TRANSLATE txn then there should be only
3018 -- only one starting lot and a resulting lot.
3019
3020 IF x_transaction_type_id = 3 and x_start_ct <> 1 THEN
3021 fnd_message.set_name('WSM', 'WSM_MULTIPLE_SLOTS');
3022 o_err_message:= fnd_message.get;
3023 err_status := -1;
3024 return;
3025 END IF;
3026
3027 IF x_transaction_type_id = 3 and x_result_ct <> 1 THEN
3028 fnd_message.set_name('WSM', 'WSM_MULTIPLE_RLOTS');
3029 o_err_message:= fnd_message.get;
3030 err_status := -1;
3031 return;
3032 END IF;
3033
3034 -- Check to see if this is a MERGE txn then there should be only
3035 -- only one resulting lot and morethan on Starting Lot.
3036
3037
3038 IF x_transaction_type_id = 2 and x_result_ct <> 1 THEN
3039 fnd_message.set_name('WSM', 'WSM_MULTIPLE_RLOTS');
3040 o_err_message:= fnd_message.get;
3041 err_status := -1;
3042 return;
3043 END IF;
3044
3045 IF x_transaction_type_id = 2 and x_start_ct <= 1 THEN
3046 fnd_message.set_name('WSM', 'WSM_ONE_SLOT');
3047 o_err_message:= fnd_message.get;
3048 err_status := -1;
3049 return;
3050 END IF;
3051
3052 lProcLocation := 120;
3053
3054 SELECT sum(quantity)
3055 into x_start_ct1
3056 FROM wsm_starting_lots_interface
3057 WHERE header_id = p_header_id;
3058
3059 lProcLocation := 130;
3060
3061 SELECT sum(quantity)
3062 into x_result_ct1
3063 FROM wsm_resulting_lots_interface
3064 WHERE header_id = p_header_id;
3065
3066 -- Check to see if this is a SPLIT txn then
3067 -- StartQty should be GREATER than sumOfResultantQty
3068
3069 IF x_transaction_type_id = 1 and x_start_ct1 < x_result_ct1 THEN
3070 fnd_message.set_name('WSM', 'WSM_RL_QUANTITY_SUM');
3071 o_err_message:= fnd_message.get;
3072 err_status := -1;
3073 return;
3074 END IF;
3075
3076 -- Check to see if this is a SPLIT txn and only one resulting LOT
3077 -- and StartQty equals sumOfResultantQty, then also error.
3078
3079 IF x_transaction_type_id = 1 -- Split Txn.
3080 and x_start_ct1 = x_result_ct1 -- StartQty = SumResultingQty
3081 and x_result_ct=1 -- Only on Resulting Lot
3082 THEN
3083 fnd_message.set_name('WSM', 'WSM_RESULTING_SAME');
3084 FND_MESSAGE.SET_TOKEN('FLD_NAME','quantity');
3085 o_err_message:= fnd_message.get;
3086 err_status := -1;
3087 return;
3088 END IF;
3089
3090 -- Check to see if this is NOT a SPLIT txn and
3091 -- and StartQty DOES NOT equal sumOfResultantQty, then also error.
3092
3093
3094 IF x_transaction_type_id <> 1 and x_start_ct1 <> x_result_ct1 THEN
3095 fnd_message.set_name('WSM', 'WSM_RL_QUANTITY_SUM');
3096 o_err_message:= fnd_message.get;
3097 err_status := -1;
3098 return;
3099 END IF;
3100
3101 END;
3102
3103 EXCEPTION -- added by BBK
3104
3105 When OTHERS Then
3106 o_err_message:= substr(SQLERRM,1,2000);
3107 err_status := -1;
3108
3109 showProgress(
3110 processingMode => CONCURRENT
3111 , headerId => p_header_id
3112 , procName => lProcName
3113 , procLocation => lProcLocation
3114 , showMessage => o_err_message);
3115
3116 return;
3117
3118 END; -- END OF UNHANDLED EXCEPTION ..BBK
3119
3120
3121 END Validate_Header;
3122
3123 ----------------------------------------------------------------------
3124 /*
3125 ** This Procedure will create the extra record for the remaining start
3126 ** quantity for the original starting lot.
3127 ** Consider the following Scenario,
3128 **
3129 ** LOTB Qty60
3130 ** /
3131 ** LOTA Qty100
3132 ** \
3133 ** LOTC Qty35
3134 **
3135 ** For the remaining quantity of LOTA, Qty5, we would create a resulting
3136 ** LOT record in the WSM_RESULTING_LOT_INTERFACE table with the same
3137 ** transaction_id so that it could be imported.
3138 **
3139 ** - Bala BALAKUMAR, Aug 25th, 2000
3140 */
3141
3142
3143 PROCEDURE Create_Extra_Record(
3144 p_header_id IN NUMBER,
3145 x_err_code OUT NOCOPY Number,
3146 x_err_msg OUT NOCOPY Varchar2) IS
3147
3148 x_start NUMBER;
3149 x_result NUMBER;
3150 l_stmt_num NUMBER;
3151 l_err_num NUMBER;
3152
3153 -- added by BBK for debugging
3154 lProcName VARCHAR2(32) := 'create_extra_record';
3155
3156
3157 BEGIN
3158 x_err_code := 0;
3159 x_err_msg := NULL;
3160
3161 l_stmt_num :=10;
3162 SELECT sum(quantity)
3163 INTO x_start
3164 FROM wsm_starting_lots_interface
3165 WHERE header_id = p_header_id;
3166
3167 l_stmt_num :=20;
3168 SELECT sum(quantity)
3169 INTO x_result
3170 FROM wsm_resulting_lots_interface
3171 WHERE header_id = p_header_id;
3172 l_stmt_num :=21;
3173
3174
3175 /*BA#IIIP*/
3176 showProgress(
3177 processingMode => CONCURRENT
3178 , headerId => p_header_id
3179 , procName => lProcName
3180 , procLocation => l_stmt_num
3181 , showMessage => (
3182 'Start Qty is '||x_start ||
3183 ' ; Resulting Qty is '||x_result
3184 ));
3185 /*EA#IIIP*/
3186
3187 IF x_result < x_start THEN
3188 l_stmt_num :=23;
3189
3190 fnd_message.set_name('WSM', 'WSM_QUANTITY_REMAINING');
3191 l_stmt_num :=24;
3192 x_err_msg:= substr(fnd_message.get, 1, 200);
3193 x_err_code := -10;
3194
3195 l_stmt_num :=30;
3196 INSERT INTO wsm_resulting_lots_interface
3197 (
3198 header_id,
3199 lot_number,
3200 inventory_item_id,
3201 organization_id,
3202 revision,
3203 quantity,
3204 subinventory_code,
3205 locator_id,
3206 last_update_date,
3207 last_updated_by,
3208 creation_date,
3209 created_by,
3210 last_update_login,
3211 request_id,
3212 program_application_id,
3213 program_id,
3214 program_update_date,
3215 attribute_category,
3216 attribute1,
3217 attribute2,
3218 attribute3,
3219 attribute4,
3220 attribute5,
3221 attribute6,
3222 attribute7,
3223 attribute8,
3224 attribute9,
3225 attribute10,
3226 attribute11,
3227 attribute12,
3228 attribute13,
3229 attribute14,
3230 attribute15
3231 )
3232 SELECT
3233 header_id,
3234 lot_number,
3235 inventory_item_id,
3236 organization_id,
3237 revision,
3238 x_start - x_result,
3239 subinventory_code,
3240 locator_id,
3241 sysdate,
3242 USER,
3243 sysdate,
3244 USER,
3245 LOGIN,
3246 REQUEST,
3247 PROGAPPL,
3248 PROGRAM,
3249 sysdate,
3250 attribute_category,
3251 attribute1,
3252 attribute2,
3253 attribute3,
3254 attribute4,
3255 attribute5,
3256 attribute6,
3257 attribute7,
3258 attribute8,
3259 attribute9,
3260 attribute10,
3261 attribute11,
3262 attribute12,
3263 attribute13,
3264 attribute14,
3265 attribute15
3266 FROM wsm_starting_lots_interface
3267 WHERE header_id = p_header_id;
3268
3269 If SQL%ROWCOUNT = 0 Then
3270
3271 /*BA#IIIP*/
3272 showProgress(
3273 processingMode => CONCURRENT
3274 , headerId => p_header_id
3275 , procName => lProcName
3276 , procLocation => l_stmt_num
3277 , showMessage =>
3278 ('ERROR:No extra record created for header Id:'||p_header_id));
3279 /*EA#IIIP*/
3280
3281
3282 End If;
3283
3284
3285 /*BA#IIIP*/
3286 showProgress(
3287 processingMode => CONCURRENT
3288 , headerId => p_header_id
3289 , procName => lProcName
3290 , procLocation => l_stmt_num
3291 , showMessage =>
3292 ('Just after inserting extra record for header Id:'||p_header_id));
3293 /*EA#IIIP*/
3294
3295 Else
3296 /*BA#IIIP*/
3297 showProgress(
3298 processingMode => CONCURRENT
3299 , headerId => p_header_id
3300 , procName => lProcName
3301 , procLocation => l_stmt_num
3302 , showMessage =>
3303 ('No extra record needed for header Id:'||p_header_id));
3304 /*EA#IIIP*/
3305 END IF;
3306 EXCEPTION
3307 WHEN NO_DATA_FOUND THEN
3308 x_err_code := SQLCODE;
3309 x_err_msg := substr(('WSMPINVL.CREATE_EXTRA_RECORD('
3310 ||l_stmt_num
3311 || '): '
3312 || SQLERRM) ,1,2000);
3313 /*BA#IIIP*/
3314 showProgress(
3315 processingMode => CONCURRENT
3316 , headerId => p_header_id
3317 , procName => lProcName
3318 , procLocation => l_stmt_num
3319 , showMessage =>
3320 ('Exception:No data found for header Id:'||p_header_id));
3321 /*EA#IIIP*/
3322
3323 WHEN OTHERS THEN
3324 x_err_code := SQLCODE;
3325 x_err_msg := substr(('WSMPINVL.CREATE_EXTRA_RECORD('
3326 ||l_stmt_num
3327 || '): '
3328 || SQLERRM) ,1,2000);
3329 /*BA#IIIP*/
3330 showProgress(
3331 processingMode => CONCURRENT
3332 , headerId => p_header_id
3333 , procName => lProcName
3334 , procLocation => l_stmt_num
3335 , showMessage => substr(
3336 ('Exception:No extra record created for header Id:'
3337 ||p_header_id
3338 ||x_err_msg
3339 ) ,1, 2000));
3340 /*EA#IIIP*/
3341
3342 END Create_Extra_Record;
3343
3344 --------------------------------------------------------------------------
3345 /*
3346 ** This procedure creates MTL Records in Inventory tables.
3347 ** A Starting Lot should be a Miscellaenous Issue and
3348 ** A Resulting Lot should be a Miscellaneous Receipt in to Inventory.
3349 **
3350 ** This Proocedure in turn calls MiscIssue and MiscReceipt procedures.
3351 **
3352 ** Bala BALAKUMAR, Aug 25th, 2000.
3353 */
3354
3355 PROCEDURE Create_Mtl_Records(
3356 p_header_id IN NUMBER, -- added by Bala.
3357 P_Header_Id1 IN NUMBER,
3358 p_transaction_id IN NUMBER, -- added by Bala.
3359 P_Transaction_Type IN NUMBER,
3360 x_err_code OUT NOCOPY NUMBER ,
3361 x_err_msg OUT NOCOPY VARCHAR2) IS
3362
3363 CURSOR slots IS
3364 SELECT distinct
3365 x.expiration_date,
3366 a.lot_number,
3367 a.inventory_item_id,
3368 a.revision,
3369 a.subinventory_code,
3370 a.locator_id,
3371 a.quantity,
3372 a.representative_flag -- added by sisankar for bug 4920235
3373 FROM
3374 mtl_lot_numbers x,
3375 wsm_starting_lots_interface a
3376 WHERE header_id = p_header_id
3377 AND X.lot_number = a.lot_number
3378 -- bugfix 1995378: added the orgn id and inventory item id condition
3379 AND x.organization_id = a.organization_id
3380 AND x.inventory_item_id = a.inventory_item_id;
3381
3382 CURSOR rlots IS
3383 SELECT
3384 lot_number,
3385 inventory_item_id,
3386 revision,
3387 subinventory_code,
3388 locator_id,
3389 quantity
3390 FROM wsm_resulting_lots_interface
3391 WHERE header_id = p_header_id;
3392
3393 x_reason_id NUMBER;
3394 x_reference VARCHAR2(240);
3395 x_org_id NUMBER;
3396 x_date DATE;
3397 x_acct_period_id NUMBER;
3398 x_temp_id NUMBER ;
3399 x_cnt1 NUMBER ;
3400 x_cnt2 NUMBER;
3401 l_stmt_num NUMBER;
3402 l_err_code NUMBER := 0;
3403 l_err_msg VARCHAR2(2000);
3404 e_proc_exception EXCEPTION;
3405
3406 -- added by BBK for debugging
3407 lProcName VARCHAR2(32) := 'create_mtl_records';
3408
3409 -- added by sisankar for bug 4920235
3410 l_slot MTL_LOT_NUMBERS.LOT_NUMBER%TYPE;
3411 l_sitem MTL_LOT_NUMBERS.INVENTORY_ITEM_ID%TYPE;
3412 x_slot_context_code MTL_LOT_NUMBERS.LOT_ATTRIBUTE_CATEGORY%TYPE:=null;
3413 x_rlot_context_code MTL_LOT_NUMBERS.LOT_ATTRIBUTE_CATEGORY%TYPE:=null;
3414 x_rlot_context_entered MTL_LOT_NUMBERS.LOT_ATTRIBUTE_CATEGORY%TYPE:=null;
3415 l_wms_installed BOOLEAN:=FALSE;
3416 l_copy_from_src BOOLEAN:=FALSE;
3417 l_intf_rec_found BOOLEAN:=FALSE;
3418 l_copy_inv_attr BOOLEAN:=FALSE;
3419 l_call_inv_lotapi BOOLEAN:=FALSE;
3420 x_lot_exists VARCHAR2(1):='N';
3421 x_return_status VARCHAR2(1);
3422 x_msg_count NUMBER;
3423 x_lot_attr_entered NUMBER:=0;
3424 l_status_id NUMBER;
3425
3426 lot_attributes_rec lot_attributes_rec_type;
3427 l_invattr_tbl inv_lot_api_pub.char_tbl;
3428 l_Cattr_tbl inv_lot_api_pub.char_tbl;
3429 l_Nattr_tbl inv_lot_api_pub.number_tbl;
3430 l_Dattr_tbl inv_lot_api_pub.date_tbl;
3431
3432 l_qoh Number;
3433 l_atr Number;
3434 l_att Number;
3435
3436 BEGIN
3437
3438 l_stmt_num :=10;
3439
3440 SELECT reason_id, transaction_reference, organization_id,
3441 transaction_date
3442 INTO x_reason_id, x_reference, x_org_id, x_date
3443 FROM wsm_lot_split_merges_interface
3444 WHERE header_id = p_header_id;
3445
3446 l_stmt_num :=20;
3447
3448 --BC Bug 3126650.
3449
3450 /* SELECT max(acct_period_id)
3451 INTO x_acct_period_id
3452 FROM org_acct_periods
3453 WHERE organization_id = x_org_id
3454 AND period_start_date <= trunc(x_date)
3455 AND open_flag = 'Y'; */
3456
3457 x_acct_period_id:=WSMPUTIL.GET_INV_ACCT_PERIOD(x_err_code => l_err_code,
3458 x_err_msg => l_err_msg,
3459 p_organization_id => x_org_id,
3460 p_date => x_date);
3461 IF (l_err_code <> 0) THEN
3462 raise e_proc_exception;
3463 END IF;
3464
3465 --EC BUG 3126650
3466 x_Exp_date := NULL;
3467
3468 l_stmt_num :=30;
3469
3470 if (inv_install.adv_inv_installed(NULL) = TRUE) THEN
3471 l_wms_installed := TRUE;
3472 end if;
3473
3474 FOR slot in slots LOOP
3475
3476 -- fix for bug 4400703.
3477 -- Validation on starting lot quantity for reservations present and on on-hand quantity.
3478
3479 wsmputil.return_att_quantity(
3480 p_org_id => x_org_id,
3481 p_item_id => slot.inventory_item_id,
3482 p_rev => slot.revision,
3483 p_lot_no => slot.lot_number,
3484 p_subinv => slot.subinventory_code,
3485 p_locator_id => slot.locator_id,
3486 p_qoh => l_qoh,
3487 p_atr => l_atr,
3488 p_att => l_att,
3489 p_err_code => l_err_code,
3490 p_err_msg => l_err_msg );
3491
3492 if l_err_code <> 0 then
3493 l_err_code:=1;
3494 raise e_proc_exception;
3495 End if;
3496 -- The different checks on slot.quantity are present to give the user appropriate error message.
3497
3498 if slot.quantity > l_qoh then -- Starting lot quantity is greater than on hand availability
3499 fnd_message.set_name('WSM', 'WSM_QUANTITY_LESS_OR_EQUAL');
3500 l_err_msg:=fnd_message.get;
3501 l_err_code:=1;
3502 raise e_proc_exception;
3503 End if;
3504
3505 if slot.quantity > l_att then -- Starting lot quantity is greater than unreserved quantity of the lot
3506 fnd_message.set_name('WSM', 'WSM_USE_UNRESERVE');
3507 l_err_msg:=fnd_message.get;
3508 l_err_code:=1;
3509 raise e_proc_exception;
3510 End if;
3511
3512 if slot.quantity <> l_att then -- user must transact the entire lot quantity
3513 fnd_message.set_name('WSM', 'WSM_INVALID_TXN_QTY');
3514 l_err_msg:=fnd_message.get;
3515 l_err_code:=1;
3516 raise e_proc_exception;
3517 End if;
3518
3519 -- fix for bug 4400703
3520
3521 Misc_Issue(
3522 p_header_id1,
3523 slot.inventory_item_id,
3524 x_org_id,
3525 slot.quantity,
3526 x_acct_period_id,
3527 slot.lot_number,
3528 slot.subinventory_code,
3529 slot.locator_id,
3530 slot.revision,
3531 x_reason_id,
3532 x_reference,
3533 x_date,
3534 p_transaction_id,
3535 p_header_id, -- added by Bala.
3536 l_err_code,
3537 l_err_msg
3538 );
3539 l_stmt_num := 40;
3540 /*BA#IIIP*/
3541 showProgress(
3542 processingMode => CONCURRENT
3543 , headerId => p_header_id
3544 , procName => lProcName
3545 , procLocation => l_stmt_num
3546 , showMessage => l_err_msg);
3547 /*EA#IIIP*/
3548
3549 /* added by Bala for Error Trapping. */
3550
3551 If l_err_code <> 0 Then
3552 raise e_proc_exception;
3553 End If;
3554
3555
3556 IF nvl(slot.expiration_date, sysdate - 2000) >
3557 nvl(x_exp_date, sysdate - 1000) THEN
3558 x_exp_date := slot.expiration_date;
3559 END IF;
3560
3561 l_stmt_num := 50;
3562
3563 IF p_transaction_type <> 2 then
3564 l_slot := slot.lot_number;
3565 l_sitem := slot.inventory_item_id;
3566 ELSE
3567 IF slot.representative_flag='Y' then
3568 l_sitem:= slot.inventory_item_id;
3569 l_slot := slot.lot_number;
3570 END IF;
3571 END IF;
3572 END LOOP;
3573
3574 l_stmt_num := 60;
3575
3576 FOR rlot in rlots LOOP
3577
3578 FOR cntr in 1..10 LOOP
3579 l_invattr_tbl(cntr):=null;
3580 l_Cattr_tbl(cntr):=null;
3581 l_Nattr_tbl(cntr):=null;
3582 l_Dattr_tbl(cntr):=null;
3583 END LOOP;
3584
3585 FOR cntr in 11..15 LOOP
3586 l_invattr_tbl(cntr):=null;
3587 l_Cattr_tbl(cntr):=null;
3588 END LOOP;
3589
3590 FOR cntr in 16..20 LOOP
3591 l_Cattr_tbl(cntr):=null;
3592 END LOOP;
3593
3594 begin
3595 select 1,lot_attribute_category
3596 into x_lot_attr_entered,x_rlot_context_entered
3597 from mtl_transaction_lots_interface mtli
3598 where mtli.product_transaction_id=p_header_id
3599 and mtli.product_code='WSM'
3600 and mtli.lot_number=rlot.lot_number;
3601 exception
3602 when NO_DATA_FOUND THEN
3603 x_lot_attr_entered:=0;
3604 x_rlot_context_entered:=null;
3605 end;
3606
3607 -- select decode(count(1),0,'N','Y') into x_lot_exists from dual where exists
3608 -- (select 1 from mtl_lot_numbers mln where mln.lot_number=rlot.lot_number and
3609 -- mln.inventory_item_id=rlot.inventory_item_id and
3610 -- mln.organization_id=x_org_id);
3611
3612 begin
3613 select 'Y'
3614 into x_lot_exists
3615 from mtl_lot_numbers mln
3616 where mln.lot_number=rlot.lot_number
3617 and mln.inventory_item_id=rlot.inventory_item_id
3618 and mln.organization_id=x_org_id;
3619 exception
3620 when NO_DATA_FOUND THEN
3621 x_lot_exists:='N';
3622 end;
3623
3624
3625 l_stmt_num := 70;
3626
3627 if x_lot_attr_entered > 0 then -- user has updated lot attributes for this particular lot
3628
3629 l_intf_rec_found :=TRUE;
3630
3631 if l_wms_installed THEN
3632
3633 inv_lot_sel_attr.get_context_code(
3634 x_rlot_context_code,
3635 x_org_id,
3636 rlot.inventory_item_id,
3637 'Lot Attributes');
3638
3639 if ((x_rlot_context_code <> x_rlot_context_entered) AND
3640 ((x_rlot_context_code IS NOT NULL) OR (x_rlot_context_entered IS NOT NULL))) THEN
3641
3642 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
3643 fnd_message.set_token('FLD_NAME', 'Lot Attribute Category');
3644 l_err_msg:=fnd_message.get;
3645 Error_All(p_header_id, null,l_err_msg);
3646 l_err_code:=1;
3647 raise e_proc_exception;
3648 end if;
3649
3650 if p_transaction_type = 3 THEN -- validation for lot translate bug 4958157
3651
3652 inv_lot_sel_attr.get_context_code(
3653 x_slot_context_code,
3654 x_org_id,
3655 l_sitem,
3656 'Lot Attributes');
3657 if ( x_slot_context_code=x_rlot_context_code or
3658 x_rlot_context_code is null) THEN
3659 l_copy_from_src:=TRUE;
3660 l_copy_inv_attr:=FALSE;
3661 else
3662 fnd_message.set_name('WSM', 'WSM_LOT_CONTEXT_DIFF');
3663 l_err_msg:=fnd_message.get;
3664 fnd_file.put_line(fnd_file.log,l_err_msg);
3665 l_copy_inv_attr:=TRUE;
3666 l_copy_from_src:=FALSE;
3667
3668 end if;
3669 else -- for other transactions copy from src.
3670 l_copy_from_src:=TRUE;
3671 l_copy_inv_attr:=FALSE;
3672 end if; -- p_transaction_type = 3
3673
3674 end if; --if l_wms_installed
3675
3676
3677 else -- user has not updated lot attributes for this particular lot
3678
3679 l_intf_rec_found :=FALSE;
3680 if p_transaction_type = 3 THEN -- validation for lots in case of lot translate
3681
3682 inv_lot_sel_attr.get_context_code(
3683 x_slot_context_code,
3684 x_org_id,
3685 l_sitem,
3686 'Lot Attributes');
3687
3688 inv_lot_sel_attr.get_context_code(
3689 x_rlot_context_code,
3690 x_org_id,
3691 rlot.inventory_item_id,
3692 'Lot Attributes');
3693 /* Checking for mandatory WMS attributes */
3694 if (x_slot_context_code <> x_rlot_context_code) and (l_wms_installed)
3695 and (x_rlot_context_code IS NOT NULL)
3696 and (inv_lot_sel_attr.is_enabled('Lot Attributes',
3697 x_org_id,
3698 rlot.inventory_item_id) >= 2) THEN
3699
3700 fnd_message.set_name('WSM', 'WSM_REQUIRED_ATTR_NO_INTF');
3701 l_err_msg:=fnd_message.get;
3702 Error_All(p_header_id, null,l_err_msg);
3703 l_err_code:=1;
3704 raise e_proc_exception;
3705 end if;
3706
3707 if ( x_slot_context_code=x_rlot_context_code or
3708 x_rlot_context_code is null) THEN
3709 l_copy_from_src:=TRUE;
3710 l_copy_inv_attr:=FALSE;
3711 else
3712 fnd_message.set_name('WSM', 'WSM_LOT_CONTEXT_DIFF');
3713 l_err_msg:=fnd_message.get;
3714 fnd_file.put_line(fnd_file.log,l_err_msg);
3715 l_copy_inv_attr:=TRUE;
3716 l_copy_from_src:=FALSE;
3717 end if;
3718
3719 else -- validation for lots in case of transactions other than lot translate
3720
3721 l_copy_from_src:=TRUE;
3722
3723 end if; --if p_transaction_type = 3
3724
3725 end if; --If x_lot_attr_entered > 0
3726
3727 l_stmt_num := 80;
3728
3729 --IF (NOT l_intf_rec_found) THEN bug 4958157.
3730
3731 IF l_copy_inv_attr THEN -- Modified for Bug 9022893.
3732
3733 SELECT
3734 attribute_category
3735 ,attribute1
3736 ,attribute2
3737 ,attribute3
3738 ,attribute4
3739 ,attribute5
3740 ,attribute6
3741 ,attribute7
3742 ,attribute8
3743 ,attribute9
3744 ,attribute10
3745 ,attribute11
3746 ,attribute12
3747 ,attribute13
3748 ,attribute14
3749 ,attribute15
3750 INTO
3751 lot_attributes_rec.l_attribute_category
3752 ,l_invattr_tbl(1)
3753 ,l_invattr_tbl(2)
3754 ,l_invattr_tbl(3)
3755 ,l_invattr_tbl(4)
3756 ,l_invattr_tbl(5)
3757 ,l_invattr_tbl(6)
3758 ,l_invattr_tbl(7)
3759 ,l_invattr_tbl(8)
3760 ,l_invattr_tbl(9)
3761 ,l_invattr_tbl(10)
3762 ,l_invattr_tbl(11)
3763 ,l_invattr_tbl(12)
3764 ,l_invattr_tbl(13)
3765 ,l_invattr_tbl(14)
3766 ,l_invattr_tbl(15)
3767 FROM mtl_lot_numbers
3768 WHERE lot_number=l_slot
3769 AND inventory_item_id=l_sitem
3770 AND organization_id= x_org_id;
3771
3772 l_call_inv_lotapi:=TRUE;
3773 l_stmt_num := 120;
3774
3775 showProgress(
3776 processingMode => CONCURRENT
3777 , headerId => p_header_id
3778 , procName => lProcName
3779 , procLocation => l_stmt_num
3780 , showMessage =>
3781 ('INV attributes are copied from source lot'));
3782
3783 elsif l_copy_from_src AND x_lot_exists='N' THEN
3784
3785 SELECT
3786 description -- This is Not a named attr, right?
3787 ,grade_code
3788 ,origination_date
3789 ,date_code
3790 ,change_date
3791 ,age
3792 ,retest_date
3793 ,maturity_date
3794 ,item_size
3795 ,color
3796 ,volume
3797 ,volume_uom
3798 ,place_of_origin
3799 ,best_by_date
3800 ,length
3801 ,length_uom
3802 ,recycled_content
3803 ,thickness
3804 ,thickness_uom
3805 ,width
3806 ,width_uom
3807 ,vendor_id -- are vendor_id is missing in create_inv_lot
3808 ,vendor_name
3809 ,territory_code --MISSING in named record
3810 ,supplier_lot_number --MISSING in named record
3811 ,curl_wrinkle_fold --MISSING in named record
3812 ,lot_attribute_category
3813 ,c_attribute1
3814 ,c_attribute2
3815 ,c_attribute3
3816 ,c_attribute4
3817 ,c_attribute5
3818 ,c_attribute6
3819 ,c_attribute7
3820 ,c_attribute8
3821 ,c_attribute9
3822 ,c_attribute10
3823 ,c_attribute11
3824 ,c_attribute12
3825 ,c_attribute13
3826 ,c_attribute14
3827 ,c_attribute15
3828 ,c_attribute16
3829 ,c_attribute17
3830 ,c_attribute18
3831 ,c_attribute19
3832 ,c_attribute20
3833 ,d_attribute1
3834 ,d_attribute2
3835 ,d_attribute3
3836 ,d_attribute4
3837 ,d_attribute5
3838 ,d_attribute6
3839 ,d_attribute7
3840 ,d_attribute8
3841 ,d_attribute9
3842 ,d_attribute10
3843 ,n_attribute1
3844 ,n_attribute2
3845 ,n_attribute3
3846 ,n_attribute4
3847 ,n_attribute5
3848 ,n_attribute6
3849 ,n_attribute7
3850 ,n_attribute8
3851 ,n_attribute9
3852 ,n_attribute10
3853 ,attribute_category
3854 ,attribute1
3855 ,attribute2
3856 ,attribute3
3857 ,attribute4
3858 ,attribute5
3859 ,attribute6
3860 ,attribute7
3861 ,attribute8
3862 ,attribute9
3863 ,attribute10
3864 ,attribute11
3865 ,attribute12
3866 ,attribute13
3867 ,attribute14
3868 ,attribute15
3869 INTO
3870 lot_attributes_rec.l_description
3871 ,lot_attributes_rec.l_grade_code
3872 ,lot_attributes_rec.l_origination_date
3873 ,lot_attributes_rec.l_date_code
3874 ,lot_attributes_rec.l_change_date
3875 ,lot_attributes_rec.l_age
3876 ,lot_attributes_rec.l_retest_date
3877 ,lot_attributes_rec.l_maturity_date
3878 ,lot_attributes_rec.l_item_size
3879 ,lot_attributes_rec.l_color
3880 ,lot_attributes_rec.l_volume
3881 ,lot_attributes_rec.l_volume_uom
3882 ,lot_attributes_rec.l_place_of_origin
3883 ,lot_attributes_rec.l_best_by_date
3884 ,lot_attributes_rec.l_length
3885 ,lot_attributes_rec.l_length_uom
3886 ,lot_attributes_rec.l_recycled_content
3887 ,lot_attributes_rec.l_thickness
3888 ,lot_attributes_rec.l_thickness_uom
3889 ,lot_attributes_rec.l_width
3890 ,lot_attributes_rec.l_width_uom
3891 ,lot_attributes_rec.l_vendor_id
3892 ,lot_attributes_rec.l_vendor_name
3893 ,lot_attributes_rec.l_territory_code
3894 ,lot_attributes_rec.l_supplier_lot_number
3895 ,lot_attributes_rec.l_curl_wrinkle_fold
3896 ,lot_attributes_rec.l_lot_attribute_category
3897 ,l_Cattr_tbl(1)
3898 ,l_Cattr_tbl(2)
3899 ,l_Cattr_tbl(3)
3900 ,l_Cattr_tbl(4)
3901 ,l_Cattr_tbl(5)
3902 ,l_Cattr_tbl(6)
3903 ,l_Cattr_tbl(7)
3904 ,l_Cattr_tbl(8)
3905 ,l_Cattr_tbl(9)
3906 ,l_Cattr_tbl(10)
3907 ,l_Cattr_tbl(11)
3908 ,l_Cattr_tbl(12)
3909 ,l_Cattr_tbl(13)
3910 ,l_Cattr_tbl(14)
3911 ,l_Cattr_tbl(15)
3912 ,l_Cattr_tbl(16)
3913 ,l_Cattr_tbl(17)
3914 ,l_Cattr_tbl(18)
3915 ,l_Cattr_tbl(19)
3916 ,l_Cattr_tbl(20)
3917 ,l_Dattr_tbl(1)
3918 ,l_Dattr_tbl(2)
3919 ,l_Dattr_tbl(3)
3920 ,l_Dattr_tbl(4)
3921 ,l_Dattr_tbl(5)
3922 ,l_Dattr_tbl(6)
3923 ,l_Dattr_tbl(7)
3924 ,l_Dattr_tbl(8)
3925 ,l_Dattr_tbl(9)
3926 ,l_Dattr_tbl(10)
3927 ,l_Nattr_tbl(1)
3928 ,l_Nattr_tbl(2)
3929 ,l_Nattr_tbl(3)
3930 ,l_Nattr_tbl(4)
3931 ,l_Nattr_tbl(5)
3932 ,l_Nattr_tbl(6)
3933 ,l_Nattr_tbl(7)
3934 ,l_Nattr_tbl(8)
3935 ,l_Nattr_tbl(9)
3936 ,l_Nattr_tbl(10)
3937 ,lot_attributes_rec.l_attribute_category
3938 ,l_invattr_tbl(1)
3939 ,l_invattr_tbl(2)
3940 ,l_invattr_tbl(3)
3941 ,l_invattr_tbl(4)
3942 ,l_invattr_tbl(5)
3943 ,l_invattr_tbl(6)
3944 ,l_invattr_tbl(7)
3945 ,l_invattr_tbl(8)
3946 ,l_invattr_tbl(9)
3947 ,l_invattr_tbl(10)
3948 ,l_invattr_tbl(11)
3949 ,l_invattr_tbl(12)
3950 ,l_invattr_tbl(13)
3951 ,l_invattr_tbl(14)
3952 ,l_invattr_tbl(15)
3953 FROM mtl_lot_numbers
3954 WHERE lot_number=l_slot
3955 AND inventory_item_id=l_sitem
3956 AND organization_id= x_org_id;
3957
3958 l_call_inv_lotapi:=TRUE;
3959 l_stmt_num := 130;
3960
3961 showProgress(
3962 processingMode => CONCURRENT
3963 , headerId => p_header_id
3964 , procName => lProcName
3965 , procLocation => l_stmt_num
3966 , showMessage =>
3967 ('WMS attributes are copied from source lot'));
3968
3969 end if; --l_copy_inv_attr AND x_lot_exists='N'
3970
3971 -- ELSE bug fix 4958157
3972 IF l_intf_rec_found THEN -- bug fix 4958157
3973
3974 -- modified the query with decode in select clause for bug fix 4958157
3975
3976 SELECT
3977 transaction_interface_id
3978 ,decode(description,l_miss_char, NULL,NULL,lot_attributes_rec.l_description,description)
3979 ,decode(grade_code,l_miss_char, NULL,NULL,lot_attributes_rec.l_grade_code,grade_code)
3980 ,decode(origination_date,l_miss_date, NULL,NULL,lot_attributes_rec.l_origination_date,origination_date)
3981 ,decode(date_code,l_miss_char, NULL,NULL,lot_attributes_rec.l_date_code,date_code)
3982 ,decode(change_date,l_miss_date, NULL,NULL,lot_attributes_rec.l_change_date,change_date)
3983 ,decode(age,l_miss_num, NULL,NULL,lot_attributes_rec.l_age,age)
3984 ,decode(retest_date,l_miss_date, NULL,NULL,lot_attributes_rec.l_retest_date,retest_date)
3985 ,decode(maturity_date,l_miss_date, NULL,NULL,lot_attributes_rec.l_maturity_date,maturity_date)
3986 ,decode(item_size,l_miss_num, NULL,NULL,lot_attributes_rec.l_item_size,item_size)
3987 ,decode(color,l_miss_char, NULL,NULL,lot_attributes_rec.l_color,color)
3988 ,decode(volume,l_miss_num, NULL,NULL,lot_attributes_rec.l_volume,volume)
3989 ,decode(volume_uom,l_miss_char, NULL,NULL,lot_attributes_rec.l_volume_uom,volume_uom)
3990 ,decode(place_of_origin,l_miss_char, NULL,NULL,lot_attributes_rec.l_place_of_origin,place_of_origin)
3991 ,decode(best_by_date,l_miss_date, NULL,NULL,lot_attributes_rec.l_best_by_date,best_by_date)
3992 ,decode(length,l_miss_num, NULL,NULL,lot_attributes_rec.l_length,length)
3993 ,decode(length_uom,l_miss_char, NULL,NULL,lot_attributes_rec.l_length_uom,length_uom)
3994 ,decode(recycled_content,l_miss_num, NULL,NULL,lot_attributes_rec.l_recycled_content,recycled_content)
3995 ,decode(thickness,l_miss_num, NULL,NULL,lot_attributes_rec.l_thickness,thickness)
3996 ,decode(thickness_uom,l_miss_char, NULL,NULL,lot_attributes_rec.l_thickness_uom,thickness_uom)
3997 ,decode(width,l_miss_num, NULL,NULL,lot_attributes_rec.l_width,width)
3998 ,decode(width_uom,l_miss_char, NULL,NULL,lot_attributes_rec.l_width_uom,width_uom)
3999 ,decode(vendor_id,l_miss_num, NULL,NULL,lot_attributes_rec.l_vendor_id,vendor_id)
4000 ,decode(vendor_name,l_miss_char, NULL,NULL,lot_attributes_rec.l_vendor_name,vendor_name)
4001 ,decode(territory_code,l_miss_char,NULL,NULL,lot_attributes_rec.l_territory_code,territory_code)
4002 ,decode(supplier_lot_number,l_miss_char, NULL,NULL,lot_attributes_rec.l_supplier_lot_number,supplier_lot_number)
4003 ,decode(curl_wrinkle_fold,l_miss_char, NULL,NULL,lot_attributes_rec.l_curl_wrinkle_fold,curl_wrinkle_fold)
4004 ,decode(lot_attribute_category,l_miss_char, NULL,NULL,lot_attributes_rec.l_lot_attribute_category,lot_attribute_category)
4005 ,decode(c_attribute1,l_miss_char, NULL,NULL,l_Cattr_tbl(1),c_attribute1)
4006 ,decode(c_attribute2,l_miss_char, NULL,NULL,l_Cattr_tbl(2),c_attribute2)
4007 ,decode(c_attribute3,l_miss_char, NULL,NULL,l_Cattr_tbl(3),c_attribute3)
4008 ,decode(c_attribute4,l_miss_char, NULL,NULL,l_Cattr_tbl(4),c_attribute4)
4009 ,decode(c_attribute5,l_miss_char, NULL,NULL,l_Cattr_tbl(5),c_attribute5)
4010 ,decode(c_attribute6,l_miss_char, NULL,NULL,l_Cattr_tbl(6),c_attribute6)
4011 ,decode(c_attribute7,l_miss_char, NULL,NULL,l_Cattr_tbl(7),c_attribute7)
4012 ,decode(c_attribute8,l_miss_char, NULL,NULL,l_Cattr_tbl(8),c_attribute8)
4013 ,decode(c_attribute9,l_miss_char, NULL,NULL,l_Cattr_tbl(9),c_attribute9)
4014 ,decode(c_attribute10,l_miss_char, NULL,NULL,l_Cattr_tbl(10),c_attribute10)
4015 ,decode(c_attribute11,l_miss_char, NULL,NULL,l_Cattr_tbl(11),c_attribute11)
4016 ,decode(c_attribute12,l_miss_char, NULL,NULL,l_Cattr_tbl(12),c_attribute12)
4017 ,decode(c_attribute13,l_miss_char, NULL,NULL,l_Cattr_tbl(13),c_attribute13)
4018 ,decode(c_attribute14,l_miss_char, NULL,NULL,l_Cattr_tbl(14),c_attribute14)
4019 ,decode(c_attribute15,l_miss_char, NULL,NULL,l_Cattr_tbl(15),c_attribute15)
4020 ,decode(c_attribute16,l_miss_char, NULL,NULL,l_Cattr_tbl(16),c_attribute16)
4021 ,decode(c_attribute17,l_miss_char, NULL,NULL,l_Cattr_tbl(17),c_attribute17)
4022 ,decode(c_attribute18,l_miss_char, NULL,NULL,l_Cattr_tbl(18),c_attribute18)
4023 ,decode(c_attribute19,l_miss_char, NULL,NULL,l_Cattr_tbl(19),c_attribute19)
4024 ,decode(c_attribute20,l_miss_char, NULL,NULL,l_Cattr_tbl(20),c_attribute20)
4025 ,decode(d_attribute1,l_miss_date, NULL,NULL,l_Dattr_tbl(1),d_attribute1)
4026 ,decode(d_attribute2,l_miss_date, NULL,NULL,l_Dattr_tbl(2),d_attribute2)
4027 ,decode(d_attribute3,l_miss_date, NULL,NULL,l_Dattr_tbl(3),d_attribute3)
4028 ,decode(d_attribute4,l_miss_date, NULL,NULL,l_Dattr_tbl(4),d_attribute4)
4029 ,decode(d_attribute5,l_miss_date, NULL,NULL,l_Dattr_tbl(5),d_attribute5)
4030 ,decode(d_attribute6,l_miss_date, NULL,NULL,l_Dattr_tbl(6),d_attribute6)
4031 ,decode(d_attribute7,l_miss_date, NULL,NULL,l_Dattr_tbl(7),d_attribute7)
4032 ,decode(d_attribute8,l_miss_date, NULL,NULL,l_Dattr_tbl(8),d_attribute8)
4033 ,decode(d_attribute9,l_miss_date, NULL,NULL,l_Dattr_tbl(9),d_attribute9)
4034 ,decode(d_attribute10,l_miss_date, NULL,NULL,l_Dattr_tbl(10),d_attribute10)
4035 ,decode(n_attribute1,l_miss_num, NULL,NULL,l_Nattr_tbl(1),n_attribute1)
4036 ,decode(n_attribute2,l_miss_num, NULL,NULL,l_Nattr_tbl(2),n_attribute2)
4037 ,decode(n_attribute3,l_miss_num, NULL,NULL,l_Nattr_tbl(3),n_attribute3)
4038 ,decode(n_attribute4,l_miss_num, NULL,NULL,l_Nattr_tbl(4),n_attribute4)
4039 ,decode(n_attribute5,l_miss_num, NULL,NULL,l_Nattr_tbl(5),n_attribute5)
4040 ,decode(n_attribute6,l_miss_num, NULL,NULL,l_Nattr_tbl(6),n_attribute6)
4041 ,decode(n_attribute7,l_miss_num, NULL,NULL,l_Nattr_tbl(7),n_attribute7)
4042 ,decode(n_attribute8,l_miss_num, NULL,NULL,l_Nattr_tbl(8),n_attribute8)
4043 ,decode(n_attribute9,l_miss_num, NULL,NULL,l_Nattr_tbl(9),n_attribute9)
4044 ,decode(n_attribute10,l_miss_num, NULL,NULL,l_Nattr_tbl(10),n_attribute10)
4045 ,decode(attribute_category,l_miss_char, NULL,NULL,lot_attributes_rec.l_attribute_category,attribute_category)
4046 ,decode(attribute1,l_miss_char, NULL,NULL,l_invattr_tbl(1),attribute1)
4047 ,decode(attribute2,l_miss_char, NULL,NULL,l_invattr_tbl(2),attribute2)
4048 ,decode(attribute3,l_miss_char, NULL,NULL,l_invattr_tbl(3),attribute3)
4049 ,decode(attribute4,l_miss_char, NULL,NULL,l_invattr_tbl(4),attribute4)
4050 ,decode(attribute5,l_miss_char, NULL,NULL,l_invattr_tbl(5),attribute5)
4051 ,decode(attribute6,l_miss_char, NULL,NULL,l_invattr_tbl(6),attribute6)
4052 ,decode(attribute7,l_miss_char, NULL,NULL,l_invattr_tbl(7),attribute7)
4053 ,decode(attribute8,l_miss_char, NULL,NULL,l_invattr_tbl(8),attribute8)
4054 ,decode(attribute9,l_miss_char, NULL,NULL,l_invattr_tbl(9),attribute9)
4055 ,decode(attribute10,l_miss_char, NULL,NULL,l_invattr_tbl(10),attribute10)
4056 ,decode(attribute11,l_miss_char, NULL,NULL,l_invattr_tbl(11),attribute11)
4057 ,decode(attribute12,l_miss_char, NULL,NULL,l_invattr_tbl(12),attribute12)
4058 ,decode(attribute13,l_miss_char, NULL,NULL,l_invattr_tbl(13),attribute13)
4059 ,decode(attribute14,l_miss_char, NULL,NULL,l_invattr_tbl(14),attribute14)
4060 ,decode(attribute15,l_miss_char, NULL,NULL,l_invattr_tbl(15),attribute15)
4061 INTO
4062 lot_attributes_rec.l_mtli_txn_id
4063 ,lot_attributes_rec.l_description
4064 ,lot_attributes_rec.l_grade_code
4065 ,lot_attributes_rec.l_origination_date
4066 ,lot_attributes_rec.l_date_code
4067 ,lot_attributes_rec.l_change_date
4068 ,lot_attributes_rec.l_age
4069 ,lot_attributes_rec.l_retest_date
4070 ,lot_attributes_rec.l_maturity_date
4071 ,lot_attributes_rec.l_item_size
4072 ,lot_attributes_rec.l_color
4073 ,lot_attributes_rec.l_volume
4074 ,lot_attributes_rec.l_volume_uom
4075 ,lot_attributes_rec.l_place_of_origin
4076 ,lot_attributes_rec.l_best_by_date
4077 ,lot_attributes_rec.l_length
4078 ,lot_attributes_rec.l_length_uom
4079 ,lot_attributes_rec.l_recycled_content
4080 ,lot_attributes_rec.l_thickness
4081 ,lot_attributes_rec.l_thickness_uom
4082 ,lot_attributes_rec.l_width
4083 ,lot_attributes_rec.l_width_uom
4084 ,lot_attributes_rec.l_vendor_id
4085 ,lot_attributes_rec.l_vendor_name
4086 ,lot_attributes_rec.l_territory_code
4087 ,lot_attributes_rec.l_supplier_lot_number
4088 ,lot_attributes_rec.l_curl_wrinkle_fold
4089 ,lot_attributes_rec.l_lot_attribute_category
4090 ,l_Cattr_tbl(1)
4091 ,l_Cattr_tbl(2)
4092 ,l_Cattr_tbl(3)
4093 ,l_Cattr_tbl(4)
4094 ,l_Cattr_tbl(5)
4095 ,l_Cattr_tbl(6)
4096 ,l_Cattr_tbl(7)
4097 ,l_Cattr_tbl(8)
4098 ,l_Cattr_tbl(9)
4099 ,l_Cattr_tbl(10)
4100 ,l_Cattr_tbl(11)
4101 ,l_Cattr_tbl(12)
4102 ,l_Cattr_tbl(13)
4103 ,l_Cattr_tbl(14)
4104 ,l_Cattr_tbl(15)
4105 ,l_Cattr_tbl(16)
4106 ,l_Cattr_tbl(17)
4107 ,l_Cattr_tbl(18)
4108 ,l_Cattr_tbl(19)
4109 ,l_Cattr_tbl(20)
4110 ,l_Dattr_tbl(1)
4111 ,l_Dattr_tbl(2)
4112 ,l_Dattr_tbl(3)
4113 ,l_Dattr_tbl(4)
4114 ,l_Dattr_tbl(5)
4115 ,l_Dattr_tbl(6)
4116 ,l_Dattr_tbl(7)
4117 ,l_Dattr_tbl(8)
4118 ,l_Dattr_tbl(9)
4119 ,l_Dattr_tbl(10)
4120 ,l_Nattr_tbl(1)
4121 ,l_Nattr_tbl(2)
4122 ,l_Nattr_tbl(3)
4123 ,l_Nattr_tbl(4)
4124 ,l_Nattr_tbl(5)
4125 ,l_Nattr_tbl(6)
4126 ,l_Nattr_tbl(7)
4127 ,l_Nattr_tbl(8)
4128 ,l_Nattr_tbl(9)
4129 ,l_Nattr_tbl(10)
4130 ,lot_attributes_rec.l_attribute_category
4131 ,l_invattr_tbl(1)
4132 ,l_invattr_tbl(2)
4133 ,l_invattr_tbl(3)
4134 ,l_invattr_tbl(4)
4135 ,l_invattr_tbl(5)
4136 ,l_invattr_tbl(6)
4137 ,l_invattr_tbl(7)
4138 ,l_invattr_tbl(8)
4139 ,l_invattr_tbl(9)
4140 ,l_invattr_tbl(10)
4141 ,l_invattr_tbl(11)
4142 ,l_invattr_tbl(12)
4143 ,l_invattr_tbl(13)
4144 ,l_invattr_tbl(14)
4145 ,l_invattr_tbl(15)
4146 FROM mtl_transaction_lots_interface
4147 WHERE product_transaction_id=p_header_id
4148 AND product_code='WSM'
4149 AND lot_number=rlot.lot_number;
4150
4151 l_stmt_num := 90;
4152 showProgress(
4153 processingMode => CONCURRENT
4154 , headerId => p_header_id
4155 , procName => lProcName
4156 , procLocation => l_stmt_num
4157 , showMessage =>
4158 ('Interface Record found for copying lot attributes'));
4159
4160 END IF; --l_intf_rec_found bug fix 4958157
4161
4162 l_stmt_num := 100;
4163
4164 Misc_Receipt(
4165 p_header_id1,
4166 rlot.inventory_item_id,
4167 x_org_id,
4168 rlot.quantity,
4169 x_acct_period_id,
4170 rlot.lot_number,
4171 rlot.subinventory_code,
4172 rlot.locator_id,
4173 rlot.revision,
4174 x_reason_id,
4175 x_reference,
4176 x_date,
4177 p_transaction_id,
4178 p_header_id, -- added by Bala.
4179 lot_attributes_rec, -- added by sisankar
4180 l_invattr_tbl,
4181 l_Cattr_tbl,
4182 l_Dattr_tbl,
4183 l_Nattr_tbl,
4184 l_err_code,
4185 l_err_msg
4186 );
4187
4188 /* Updating lot attributes when resulting lot is one of the existing lots */
4189
4190 IF (x_lot_exists='Y' AND l_intf_rec_found) THEN
4191
4192 inv_lot_api_pub.Update_inv_lot(
4193 x_return_status => x_return_status,
4194 x_msg_count => x_msg_count,
4195 x_msg_data => x_err_msg,
4196 p_inventory_item_id => rlot.inventory_item_id,
4197 p_organization_id => x_org_id,
4198 p_lot_number => rlot.lot_number,
4199 p_expiration_date => x_exp_date,
4200 p_disable_flag => NULL,
4201 p_attribute_category => lot_attributes_rec.l_attribute_category,
4202 p_lot_attribute_category=> lot_attributes_rec.l_lot_attribute_category,
4203 p_attributes_tbl => l_invattr_tbl,
4204 p_c_attributes_tbl => l_Cattr_tbl,
4205 p_n_attributes_tbl => l_Nattr_tbl,
4206 p_d_attributes_tbl => l_Dattr_tbl,
4207 p_grade_code => lot_attributes_rec.l_grade_code,
4208 p_origination_date => lot_attributes_rec.l_origination_date,
4209 p_date_code => lot_attributes_rec.l_date_code,
4210 p_status_id => l_status_id,
4211 p_change_date => lot_attributes_rec.l_change_date,
4212 p_age => lot_attributes_rec.l_age,
4213 p_retest_date => lot_attributes_rec.l_retest_date,
4214 p_maturity_date => lot_attributes_rec.l_maturity_date,
4215 p_item_size => lot_attributes_rec.l_item_size,
4216 p_color => lot_attributes_rec.l_color,
4217 p_volume => lot_attributes_rec.l_volume,
4218 p_volume_uom => lot_attributes_rec.l_volume_uom,
4219 p_place_of_origin => lot_attributes_rec.l_place_of_origin,
4220 p_best_by_date => lot_attributes_rec.l_best_by_date,
4221 p_length => lot_attributes_rec.l_length,
4222 p_length_uom => lot_attributes_rec.l_length_uom,
4223 p_recycled_content => lot_attributes_rec.l_recycled_content,
4224 p_thickness => lot_attributes_rec.l_thickness,
4225 p_thickness_uom => lot_attributes_rec.l_thickness_uom,
4226 p_width => lot_attributes_rec.l_width,
4227 p_width_uom => lot_attributes_rec.l_width_uom,
4228 p_territory_code => lot_attributes_rec.l_territory_code,
4229 p_supplier_lot_number => lot_attributes_rec.l_supplier_lot_number,
4230 p_vendor_name => lot_attributes_rec.l_vendor_name,
4231 p_source => 2);
4232
4233 END IF;
4234
4235 -- reinitialising for the next resulting lot.
4236 x_lot_attr_entered :=0;
4237 l_copy_inv_attr:=FALSE;
4238 l_copy_from_src:=FALSE;
4239 x_lot_exists:='N';
4240 l_invattr_tbl.delete;
4241 l_Cattr_tbl.delete;
4242 l_Nattr_tbl.delete;
4243 l_Dattr_tbl.delete;
4244
4245 l_stmt_num := 110;
4246
4247
4248 /*BA#IIIP*/
4249 showProgress(
4250 processingMode => CONCURRENT
4251 , headerId => p_header_id
4252 , procName => lProcName
4253 , procLocation => l_stmt_num
4254 , showMessage => l_err_msg);
4255 /*EA#IIIP*/
4256
4257 /* added by Bala for Error Trapping. */
4258
4259 If l_err_code <> 0 Then
4260 raise e_proc_exception;
4261 End If;
4262
4263 END LOOP;
4264
4265 EXCEPTION
4266 -- added by Bala for error trapping.
4267
4268 WHEN e_proc_exception THEN
4269 x_err_code := l_err_code;
4270 x_err_msg := substr(l_err_msg, 1, 2000);
4271
4272 WHEN OTHERS THEN
4273 x_err_code := SQLCODE;
4274 x_err_msg := substr(
4275 ('WSMPINVL.CREATE_MTL_RECORDS('
4276 ||l_stmt_num
4277 || '): '
4278 || x_err_code
4279 ), 1, 2000);
4280
4281 END Create_Mtl_Records;
4282
4283 /*******************************************************************/
4284
4285 PROCEDURE Misc_Issue
4286 (
4287 X_Header_Id1 IN NUMBER,
4288 X_Inventory_Item_Id IN NUMBER,
4289 X_Organization_id IN NUMBER,
4290 X_Quantity IN NUMBER,
4291 X_Acct_Period_Id IN NUMBER,
4292 X_Lot_Number IN VARCHAR2,
4293 X_Subinventory IN VARCHAR2,
4294 X_Locator_Id IN NUMBER,
4295 X_Revision IN VARCHAR2,
4296 X_Reason_Id IN NUMBER,
4297 X_Reference IN VARCHAR2,
4298 X_Transaction_Date IN DATE,
4299 X_Source_Line_Id IN NUMBER, -- transaction_id in WLSMI
4300 X_Header_id IN NUMBER, -- header_id in WLSMI added by Bala.
4301 x_err_code OUT NOCOPY NUMBER ,
4302 x_err_msg OUT NOCOPY VARCHAR2) IS
4303
4304 X_Temp_Id NUMBER;
4305 X_transaction_Temp_Id NUMBER;
4306 X_cnt1 NUMBER;
4307 X_Date DATE := SYSDATE;
4308 X_Uom VARCHAR2(3);
4309 X_Dist_Acct_Id NUMBER;
4310 l_stmt_num NUMBER;
4311 l_err_code NUMBER;
4312 l_err_msg VARCHAR2(2000);
4313
4314 -- added by BBK for debugging
4315 lProcName VARCHAR2(32) := 'misc_issue';
4316
4317
4318 BEGIN
4319 l_stmt_num :=10;
4320 Set_Vars;
4321
4322 --commented out by abedajna for perf. tuning
4323 /*SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
4324 **INTO X_Temp_Id
4325 **FROM DUAL;
4326 */
4327
4328 l_stmt_num :=20;
4329
4330 SELECT msi.primary_uom_code
4331 INTO X_UOM
4332 FROM
4333 MTL_SYSTEM_ITEMS msi
4334 WHERE msi.INVENTORY_ITEM_ID = X_Inventory_Item_Id
4335 AND msi.ORGANIZATION_ID = X_Organization_Id;
4336
4337 showProgress(
4338 processingMode => CONCURRENT
4339 , headerId => x_header_id
4340 , procName => lProcName
4341 , procLocation => l_stmt_num
4342 , showMessage => x_err_msg);
4343
4344 l_stmt_num :=30;
4345
4346 /*BA#1754109*/
4347
4348 -- Replaced the fix for Bug#1752110
4349 -- ORA-1403 can occur if no alias is defined for the txn acct.
4350 -- An account alias need not have to be defined for a txn acct.
4351 -- Ensure, that the txn acct is enabled, non-summary acct and active.
4352 -- Bala Balakumar, May 1st, 2001.
4353
4354 Begin
4355
4356 select wp.transaction_account_id into x_dist_acct_id
4357 from wsm_parameters wp
4358 Where wp.organization_id = X_Organization_Id
4359 and exists (select 1
4360 From gl_code_combinations gl
4361 Where gl.code_combination_id = wp.transaction_account_id
4362 and gl.enabled_flag = 'Y'
4363 and gl.summary_flag = 'N'
4364 and NVL(gl.start_date_active, sysdate) <= sysdate
4365 and NVL(gl.end_date_active, sysdate) >= sysdate
4366 );
4367
4368
4369 EXCEPTION when others THEN
4370
4371 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
4372 FND_MESSAGE.SET_TOKEN('FLD_NAME','Transaction Account for the Organization');
4373 x_err_msg := fnd_message.get;
4374 x_err_code := SQLCODE;
4375 return;
4376
4377 End;
4378
4379 /*EA#1754109*/
4380
4381
4382 l_stmt_num :=40;
4383 INSERT INTO mtl_material_transactions_temp
4384 (last_update_date,
4385 creation_date,
4386 last_updated_by,
4387 created_by,
4388 last_update_login,
4389 transaction_header_id,
4390 inventory_item_id,
4391 organization_id,
4392 subinventory_code,
4393 locator_id,
4394 transaction_quantity,
4395 primary_quantity,
4396 transaction_uom,
4397 transaction_type_id,
4398 transaction_action_id,
4399 transaction_source_type_id,
4400 transaction_date,
4401 acct_period_id,
4402 reason_id,
4403 transaction_reference,
4404 process_flag,
4405 posting_flag,
4406 transaction_temp_id,
4407 revision,
4408 distribution_account_id,
4409 source_code,
4410 source_line_id
4411 /*BA#IIIP*/
4412 , lot_number
4413 /*EA#IIIP*/
4414
4415 )
4416 values
4417 (X_DATE, /* LAST_UPDATE_DATE */
4418 X_DATE, /* CREATION_DATE */
4419 USER, /* LAST_UPDATED_BY */
4420 USER, /* CREATED_BY */
4421 LOGIN,
4422 X_Header_Id1, /* TRANSACTION_HEADER_ID */
4423 X_Inventory_Item_Id, /* INVENTORY_ITEM_ID */
4424 X_Organization_Id, /* ORGANIZATION_ID */
4425 X_Subinventory, /* SUBINVENTORY_CODE */
4426 X_Locator_Id,
4427 -1 * X_Quantity, /* TRANSACTION_QUANTITY */
4428 -1 * X_Quantity, /* PRIMARY_QUANTITY */
4429 X_Uom, /* UNIT_OF_MEASURE */
4430 32, /* TRANSACTION_TYPE_ID */
4431 1, /* TRANSACTION_ACTION_ID */
4432 13, /* TRANSACTION_SOURCE_TYPE_ID */
4433 X_transaction_date, /* TRANSACTION_DATE */
4434 X_Acct_Period_Id, /* ACCT_PERIOD_ID */
4435 X_Reason_Id, /* REASON_ID */
4436 X_Reference, /* TRANSACTION_REFERENCE */
4437 'Y', /* PROCESS_FLAG */
4438 'Y', /* POSTING_FLAG */
4439 -- abb X_temp_id, /* Transaction Temp Id */
4440 MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, /* abedajna, tuning */
4441 X_revision,
4442 X_Dist_Acct_Id, /* distribution_account_id */
4443 'WSM', /* Source Code */
4444 X_Source_Line_Id /* Transaction Id in WLSMI table */
4445 /*BA#IIIP*/
4446 ,X_LOT_NUMBER
4447 /*EA#IIIP*/
4448 )
4449 RETURNING transaction_temp_id INTO X_Temp_Id; -- abedajna, perf. Tuning
4450
4451 /*BA#IIIP*/
4452 showProgress(
4453 processingMode => CONCURRENT
4454 , headerId => x_header_id
4455 , procName => lProcName
4456 , procLocation => l_stmt_num
4457 , showMessage => 'Sucessful insert to MMTT for Misc.Issue');
4458 /*EA#IIIP*/
4459
4460 INSERT INTO MTL_TRANSACTION_LOTS_TEMP
4461 (
4462 transaction_temp_id,
4463 last_update_date,
4464 creation_date,
4465 last_updated_by,
4466 created_by,
4467 last_update_login,
4468 transaction_quantity,
4469 primary_quantity,
4470 lot_number,
4471 DESCRIPTION, -- columns in insert query added for inserting lot attributes -- sisankar
4472 GRADE_CODE,
4473 ORIGINATION_DATE,
4474 DATE_CODE,
4475 CHANGE_DATE,
4476 AGE,
4477 RETEST_DATE,
4478 MATURITY_DATE,
4479 ITEM_SIZE,
4480 COLOR,
4481 VOLUME,
4482 VOLUME_UOM,
4483 PLACE_OF_ORIGIN,
4484 BEST_BY_DATE,
4485 LENGTH,
4486 LENGTH_UOM,
4487 RECYCLED_CONTENT,
4488 THICKNESS,
4489 THICKNESS_UOM,
4490 WIDTH,
4491 WIDTH_UOM,
4492 VENDOR_ID,
4493 VENDOR_NAME,
4494 TERRITORY_CODE,
4495 SUPPLIER_LOT_NUMBER,
4496 CURL_WRINKLE_FOLD,
4497 LOT_ATTRIBUTE_CATEGORY ,
4498 C_ATTRIBUTE1,
4499 C_ATTRIBUTE2,
4500 C_ATTRIBUTE3,
4501 C_ATTRIBUTE4,
4502 C_ATTRIBUTE5,
4503 C_ATTRIBUTE6,
4504 C_ATTRIBUTE7,
4505 C_ATTRIBUTE8,
4506 C_ATTRIBUTE9,
4507 C_ATTRIBUTE10,
4508 C_ATTRIBUTE11,
4509 C_ATTRIBUTE12,
4510 C_ATTRIBUTE13,
4511 C_ATTRIBUTE14,
4512 C_ATTRIBUTE15,
4513 C_ATTRIBUTE16,
4514 C_ATTRIBUTE17,
4515 C_ATTRIBUTE18,
4516 C_ATTRIBUTE19,
4517 C_ATTRIBUTE20,
4518 D_ATTRIBUTE1,
4519 D_ATTRIBUTE2,
4520 D_ATTRIBUTE3,
4521 D_ATTRIBUTE4,
4522 D_ATTRIBUTE5,
4523 D_ATTRIBUTE6,
4524 D_ATTRIBUTE7,
4525 D_ATTRIBUTE8,
4526 D_ATTRIBUTE9,
4527 D_ATTRIBUTE10,
4528 N_ATTRIBUTE1,
4529 N_ATTRIBUTE2,
4530 N_ATTRIBUTE3,
4531 N_ATTRIBUTE4,
4532 N_ATTRIBUTE5,
4533 N_ATTRIBUTE6,
4534 N_ATTRIBUTE7,
4535 N_ATTRIBUTE8,
4536 N_ATTRIBUTE9,
4537 N_ATTRIBUTE10,
4538 ATTRIBUTE_CATEGORY,
4539 ATTRIBUTE1,
4540 ATTRIBUTE2,
4541 ATTRIBUTE3,
4542 ATTRIBUTE4,
4543 ATTRIBUTE5,
4544 ATTRIBUTE6,
4545 ATTRIBUTE7,
4546 ATTRIBUTE8,
4547 ATTRIBUTE9,
4548 ATTRIBUTE10,
4549 ATTRIBUTE11,
4550 ATTRIBUTE12,
4551 ATTRIBUTE13,
4552 ATTRIBUTE14,
4553 ATTRIBUTE15
4554 )
4555 select
4556 X_temp_id,
4557 X_date,
4558 X_date,
4559 USER,
4560 USER,
4561 LOGIN,
4562 -1 * X_quantity,
4563 -1 * X_quantity,
4564 X_lot_number,
4565 mln.description
4566 ,mln.grade_code
4567 ,mln.origination_date
4568 ,mln.date_code
4569 ,mln.change_date
4570 ,mln.age
4571 ,mln.retest_date
4572 ,mln.maturity_date
4573 ,mln.item_size
4574 ,mln.color
4575 ,mln.volume
4576 ,mln.volume_uom
4577 ,mln.place_of_origin
4578 ,mln.best_by_date
4579 ,mln.length
4580 ,mln.length_uom
4581 ,mln.recycled_content
4582 ,mln.thickness
4583 ,mln.thickness_uom
4584 ,mln.width
4585 ,mln.width_uom
4586 ,mln.vendor_id -- are vendor_id is missing in create_inv_lot
4587 ,mln.vendor_name
4588 ,mln.territory_code --MISSING in named record
4589 ,mln.supplier_lot_number --MISSING in named record
4590 ,mln.curl_wrinkle_fold --MISSING in named record
4591 ,mln.lot_attribute_category
4592 ,mln.c_attribute1
4593 ,mln.c_attribute2
4594 ,mln.c_attribute3
4595 ,mln.c_attribute4
4596 ,mln.c_attribute5
4597 ,mln.c_attribute6
4598 ,mln.c_attribute7
4599 ,mln.c_attribute8
4600 ,mln.c_attribute9
4601 ,mln.c_attribute10
4602 ,mln.c_attribute11
4603 ,mln.c_attribute12
4604 ,mln.c_attribute13
4605 ,mln.c_attribute14
4606 ,mln.c_attribute15
4607 ,mln.c_attribute16
4608 ,mln.c_attribute17
4609 ,mln.c_attribute18
4610 ,mln.c_attribute19
4611 ,mln.c_attribute20
4612 ,mln.d_attribute1
4613 ,mln.d_attribute2
4614 ,mln.d_attribute3
4615 ,mln.d_attribute4
4616 ,mln.d_attribute5
4617 ,mln.d_attribute6
4618 ,mln.d_attribute7
4619 ,mln.d_attribute8
4620 ,mln.d_attribute9
4621 ,mln.d_attribute10
4622 ,mln.n_attribute1
4623 ,mln.n_attribute2
4624 ,mln.n_attribute3
4625 ,mln.n_attribute4
4626 ,mln.n_attribute5
4627 ,mln.n_attribute6
4628 ,mln.n_attribute7
4629 ,mln.n_attribute8
4630 ,mln.n_attribute9
4631 ,mln.n_attribute10
4632 ,mln.attribute_category
4633 ,mln.attribute1
4634 ,mln.attribute2
4635 ,mln.attribute3
4636 ,mln.attribute4
4637 ,mln.attribute5
4638 ,mln.attribute6
4639 ,mln.attribute7
4640 ,mln.attribute8
4641 ,mln.attribute9
4642 ,mln.attribute10
4643 ,mln.attribute11
4644 ,mln.attribute12
4645 ,mln.attribute13
4646 ,mln.attribute14
4647 ,mln.attribute15
4648 from MTL_LOT_NUMBERS mln
4649 where mln.LOT_NUMBER = X_lot_number
4650 and mln.ORGANIZATION_ID = X_Organization_Id
4651 and mln.INVENTORY_ITEM_ID = X_Inventory_Item_Id;
4652
4653 /*BA#IIIP*/
4654 showProgress(
4655 processingMode => CONCURRENT
4656 , headerId => x_header_id
4657 , procName => lProcName
4658 , procLocation => l_stmt_num
4659 , showMessage => 'Sucessful insert to MTLT for Misc.Issue');
4660 /*EA#IIIP*/
4661
4662 EXCEPTION
4663 WHEN OTHERS THEN
4664 x_err_code := SQLCODE;
4665 x_err_msg := substr(
4666 ('WSMPINVL.MISC_ISSUE('
4667 ||l_stmt_num
4668 || '): '
4669 || SQLERRM
4670 ), 1, 2000);
4671 END Misc_Issue;
4672 --------------------------------------------------------------------
4673
4674 PROCEDURE Misc_Receipt
4675 (
4676 X_Header_Id1 IN NUMBER,
4677 X_Inventory_Item_Id IN NUMBER,
4678 X_Organization_id IN NUMBER,
4679 X_Quantity IN NUMBER,
4680 X_Acct_Period_Id IN NUMBER,
4681 X_Lot_Number IN VARCHAR2,
4682 X_Subinventory IN VARCHAR2,
4683 X_Locator_Id IN NUMBER,
4684 X_Revision IN VARCHAR2,
4685 X_Reason_Id IN NUMBER,
4686 X_Reference IN VARCHAR2,
4687 X_Transaction_Date IN DATE,
4688 X_Source_Line_Id IN NUMBER,
4689 X_Header_Id IN NUMBER, -- added by Bala.
4690 x_lot_attributes_rec IN lot_attributes_rec_type, -- added by sisankar for bug 4920235
4691 x_invattr_tbl IN inv_lot_api_pub.char_tbl,
4692 x_Cattr_tbl IN inv_lot_api_pub.char_tbl,
4693 x_Dattr_tbl IN inv_lot_api_pub.date_tbl,
4694 x_Nattr_tbl IN inv_lot_api_pub.number_tbl,
4695 x_err_code OUT NOCOPY NUMBER ,
4696 x_err_msg OUT NOCOPY VARCHAR2) IS
4697
4698 X_Temp_Id NUMBER;
4699 X_Date DATE := SYSDATE;
4700 X_Uom VARCHAR2(3);
4701 X_Dist_Acct_Id NUMBER;
4702 l_stmt_num NUMBER;
4703 l_err_num NUMBER;
4704 l_err_code VARCHAR2(240);
4705 l_err_msg VARCHAR2(2000);
4706
4707 -- added by BBK for debugging
4708 lProcName VARCHAR2(32) := 'misc_receipt';
4709 lProcLocation NUMBER := 0;
4710
4711
4712
4713 BEGIN
4714 Set_Vars;
4715
4716 l_stmt_num :=10;
4717
4718 --commented out by abedajna for perf. tuning
4719 /*SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
4720 **INTO X_Temp_Id
4721 **FROM DUAL;
4722 */
4723
4724 l_stmt_num :=20;
4725 SELECT msi.primary_uom_code
4726 INTO x_uom
4727 FROM mtl_system_items msi
4728 WHERE msi.INVENTORY_ITEM_ID = X_Inventory_Item_Id
4729 AND msi.ORGANIZATION_ID = X_Organization_Id;
4730
4731 l_stmt_num :=30;
4732
4733 /*BA#1754109*/
4734
4735 -- Replaced the fix for Bug#1752110
4736 -- ORA-1403 can occur if no alias is defined for the txn acct.
4737 -- An account alias need not have to be defined for a txn acct.
4738 -- Ensure, that the txn acct is enabled, non-summary acct and active.
4739 -- Bala Balakumar, May 1st, 2001.
4740
4741 Begin
4742
4743 select wp.transaction_account_id into x_dist_acct_id
4744 from wsm_parameters wp
4745 Where wp.organization_id = X_Organization_Id
4746 and exists (select 1
4747 From gl_code_combinations gl
4748 Where gl.code_combination_id = wp.transaction_account_id
4749 and gl.enabled_flag = 'Y'
4750 and gl.summary_flag = 'N'
4751 and NVL(gl.start_date_active, sysdate) <= sysdate
4752 and NVL(gl.end_date_active, sysdate) >= sysdate
4753 );
4754
4755
4756 EXCEPTION when others THEN
4757
4758 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
4759 FND_MESSAGE.SET_TOKEN('FLD_NAME','Transaction Account for the Organization');
4760 x_err_msg := fnd_message.get;
4761 x_err_code := SQLCODE;
4762 return;
4763
4764 End;
4765
4766 /*EA#1754109*/
4767
4768 l_stmt_num :=40;
4769
4770
4771 INSERT INTO mtl_material_transactions_temp
4772 (last_update_date,
4773 creation_date,
4774 last_updated_by,
4775 created_by,
4776 last_update_login,
4777 transaction_header_id,
4778 inventory_item_id,
4779 organization_id,
4780 revision,
4781 subinventory_code,
4782 locator_id,
4783 transaction_quantity,
4784 primary_quantity,
4785 transaction_uom,
4786 transaction_type_id,
4787 transaction_action_id,
4788 transaction_source_type_id,
4789 transaction_date,
4790 acct_period_id,
4791 reason_id,
4792 transaction_reference,
4793 process_flag,
4794 posting_flag,
4795 transaction_temp_id,
4796 distribution_account_id,
4797 source_code,
4798 source_line_id
4799 /*BA#IIIP*/
4800 , LOT_NUMBER
4801 /*EA#IIIP*/
4802 )
4803 VALUES
4804 (X_date, /* LAST_UPDATE_DATE */
4805 X_date, /* CREATION_DATE */
4806 USER, /* LAST_UPDATED_BY */
4807 USER, /* CREATED_BY */
4808 LOGIN,
4809 X_Header_Id1, /* TRANSACTION_HEADER_ID */
4810 X_Inventory_Item_Id, /* INVENTORY_ITEM_ID */
4811 X_Organization_Id, /* ORGANIZATION_ID */
4812 X_Revision, /* REVISION */
4813 X_Subinventory, /* SUBINVENTORY_CODE */
4814 X_Locator_Id,
4815 X_Quantity, /* TRANSACTION_QUANTITY */
4816 X_Quantity, /* PRIMARY_QUANTITY */
4817 X_Uom, /* UNIT_OF_MEASURE */
4818 42, /* TRANSACTION_TYPE_ID */
4819 27, /* TRANSACTION_ACTION_ID */
4820 13, /* TRANSACTION_SOURCE_TYPE_ID */
4821 X_transaction_date, /* TRANSACTION_DATE */
4822 X_Acct_Period_Id, /* ACCT_PERIOD_ID */
4823 X_Reason_Id, /* REASON_ID */
4824 X_Reference, /* TRANSACTION_REFERENCE */
4825 'Y', /* PROCESS_FLAG */
4826 'Y', /* POSTING_FLAG */
4827 -- abb X_temp_id, /* Transaction Temp Id */
4828 MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, /* abedajna, tuning */
4829 X_Dist_Acct_Id, /* distribution account id */
4830 'WSM', /* Source Code */
4831 X_Source_Line_Id /* Transaction Id in WLSMI table */
4832 /*BA#IIIP*/
4833 ,X_LOT_NUMBER
4834 /*EA#IIIP*/
4835 )
4836 RETURNING transaction_temp_id INTO X_Temp_Id; -- abedajna, perf. Tuning
4837
4838 /*BA#IIIP*/
4839 showProgress(
4840 processingMode => CONCURRENT
4841 , headerId => x_header_id
4842 , procName => lProcName
4843 , procLocation => l_stmt_num
4844 , showMessage => 'Sucessful insert to MMTT for Misc.Receipt');
4845 /*EA#IIIP*/
4846
4847 l_stmt_num :=50;
4848
4849 INSERT INTO MTL_TRANSACTION_LOTS_TEMP
4850 (
4851 transaction_temp_id,
4852 last_update_date,
4853 creation_date,
4854 last_updated_by,
4855 created_by,
4856 last_update_login,
4857 transaction_quantity,
4858 primary_quantity,
4859 lot_number,
4860 lot_expiration_date,
4861 DESCRIPTION, -- added by sisankar for bug 4920235
4862 GRADE_CODE,
4863 ORIGINATION_DATE,
4864 DATE_CODE,
4865 CHANGE_DATE,
4866 AGE,
4867 RETEST_DATE,
4868 MATURITY_DATE,
4869 ITEM_SIZE,
4870 COLOR,
4871 VOLUME,
4872 VOLUME_UOM,
4873 PLACE_OF_ORIGIN,
4874 BEST_BY_DATE,
4875 LENGTH,
4876 LENGTH_UOM,
4877 RECYCLED_CONTENT,
4878 THICKNESS,
4879 THICKNESS_UOM,
4880 WIDTH,
4881 WIDTH_UOM,
4882 VENDOR_ID,
4883 VENDOR_NAME,
4884 TERRITORY_CODE,
4885 SUPPLIER_LOT_NUMBER,
4886 CURL_WRINKLE_FOLD,
4887 LOT_ATTRIBUTE_CATEGORY ,
4888 C_ATTRIBUTE1,
4889 C_ATTRIBUTE2,
4890 C_ATTRIBUTE3,
4891 C_ATTRIBUTE4,
4892 C_ATTRIBUTE5,
4893 C_ATTRIBUTE6,
4894 C_ATTRIBUTE7,
4895 C_ATTRIBUTE8,
4896 C_ATTRIBUTE9,
4897 C_ATTRIBUTE10,
4898 C_ATTRIBUTE11,
4899 C_ATTRIBUTE12,
4900 C_ATTRIBUTE13,
4901 C_ATTRIBUTE14,
4902 C_ATTRIBUTE15,
4903 C_ATTRIBUTE16,
4904 C_ATTRIBUTE17,
4905 C_ATTRIBUTE18,
4906 C_ATTRIBUTE19,
4907 C_ATTRIBUTE20,
4908 D_ATTRIBUTE1,
4909 D_ATTRIBUTE2,
4910 D_ATTRIBUTE3,
4911 D_ATTRIBUTE4,
4912 D_ATTRIBUTE5,
4913 D_ATTRIBUTE6,
4914 D_ATTRIBUTE7,
4915 D_ATTRIBUTE8,
4916 D_ATTRIBUTE9,
4917 D_ATTRIBUTE10,
4918 N_ATTRIBUTE1,
4919 N_ATTRIBUTE2,
4920 N_ATTRIBUTE3,
4921 N_ATTRIBUTE4,
4922 N_ATTRIBUTE5,
4923 N_ATTRIBUTE6,
4924 N_ATTRIBUTE7,
4925 N_ATTRIBUTE8,
4926 N_ATTRIBUTE9,
4927 N_ATTRIBUTE10,
4928 ATTRIBUTE_CATEGORY,
4929 ATTRIBUTE1,
4930 ATTRIBUTE2,
4931 ATTRIBUTE3,
4932 ATTRIBUTE4,
4933 ATTRIBUTE5,
4934 ATTRIBUTE6,
4935 ATTRIBUTE7,
4936 ATTRIBUTE8,
4937 ATTRIBUTE9,
4938 ATTRIBUTE10,
4939 ATTRIBUTE11,
4940 ATTRIBUTE12,
4941 ATTRIBUTE13,
4942 ATTRIBUTE14,
4943 ATTRIBUTE15
4944 )
4945 values
4946 (
4947 X_temp_id,
4948 X_date,
4949 X_date,
4950 USER,
4951 USER,
4952 LOGIN,
4953 X_quantity,
4954 X_quantity,
4955 X_lot_number,
4956 x_exp_date,
4957 x_lot_attributes_rec.l_description, -- added by sisankar for bug 4920235
4958 x_lot_attributes_rec.l_grade_code,
4959 x_lot_attributes_rec.l_origination_date,
4960 x_lot_attributes_rec.l_date_code,
4961 x_lot_attributes_rec.l_change_date,
4962 x_lot_attributes_rec.l_age,
4963 x_lot_attributes_rec.l_retest_date,
4964 x_lot_attributes_rec.l_maturity_date,
4965 x_lot_attributes_rec.l_item_size,
4966 x_lot_attributes_rec.l_color,
4967 x_lot_attributes_rec.l_volume,
4968 x_lot_attributes_rec.l_volume_uom,
4969 x_lot_attributes_rec.l_place_of_origin,
4970 x_lot_attributes_rec.l_best_by_date,
4971 x_lot_attributes_rec.l_length,
4972 x_lot_attributes_rec.l_length_uom,
4973 x_lot_attributes_rec.l_recycled_content,
4974 x_lot_attributes_rec.l_thickness,
4975 x_lot_attributes_rec.l_thickness_uom,
4976 x_lot_attributes_rec.l_width,
4977 x_lot_attributes_rec.l_width_uom,
4978 x_lot_attributes_rec.l_vendor_id,
4979 x_lot_attributes_rec.l_vendor_name,
4980 x_lot_attributes_rec.l_territory_code,
4981 x_lot_attributes_rec.l_supplier_lot_number,
4982 x_lot_attributes_rec.l_curl_wrinkle_fold,
4983 x_lot_attributes_rec.l_lot_attribute_category,
4984 x_Cattr_tbl(1),
4985 x_Cattr_tbl(2),
4986 x_Cattr_tbl(3),
4987 x_Cattr_tbl(4),
4988 x_Cattr_tbl(5),
4989 x_Cattr_tbl(6),
4990 x_Cattr_tbl(7),
4991 x_Cattr_tbl(8),
4992 x_Cattr_tbl(9),
4993 x_Cattr_tbl(10),
4994 x_Cattr_tbl(11),
4995 x_Cattr_tbl(12),
4996 x_Cattr_tbl(13),
4997 x_Cattr_tbl(14),
4998 x_Cattr_tbl(15),
4999 x_Cattr_tbl(16),
5000 x_Cattr_tbl(17),
5001 x_Cattr_tbl(18),
5002 x_Cattr_tbl(19),
5003 x_Cattr_tbl(20),
5004 x_Dattr_tbl(1),
5005 x_Dattr_tbl(2),
5006 x_Dattr_tbl(3),
5007 x_Dattr_tbl(4),
5008 x_Dattr_tbl(5),
5009 x_Dattr_tbl(6),
5010 x_Dattr_tbl(7),
5011 x_Dattr_tbl(8),
5012 x_Dattr_tbl(9),
5013 x_Dattr_tbl(10),
5014 x_Nattr_tbl(1),
5015 x_Nattr_tbl(2),
5016 x_Nattr_tbl(3),
5017 x_Nattr_tbl(4),
5018 x_Nattr_tbl(5),
5019 x_Nattr_tbl(6),
5020 x_Nattr_tbl(7),
5021 x_Nattr_tbl(8),
5022 x_Nattr_tbl(9),
5023 x_Nattr_tbl(10),
5024 x_lot_attributes_rec.l_attribute_category,
5025 x_invattr_tbl(1),
5026 x_invattr_tbl(2),
5027 x_invattr_tbl(3),
5028 x_invattr_tbl(4),
5029 x_invattr_tbl(5),
5030 x_invattr_tbl(6),
5031 x_invattr_tbl(7),
5032 x_invattr_tbl(8),
5033 x_invattr_tbl(9),
5034 x_invattr_tbl(10),
5035 x_invattr_tbl(11),
5036 x_invattr_tbl(12),
5037 x_invattr_tbl(13),
5038 x_invattr_tbl(14),
5039 x_invattr_tbl(15)
5040 );
5041
5042 /*BA#IIIP*/
5043 showProgress(
5044 processingMode => CONCURRENT
5045 , headerId => x_header_id
5046 , procName => lProcName
5047 , procLocation => l_stmt_num
5048 , showMessage => 'Sucessful insert to MTLT for Misc.Receipt');
5049 /*EA#IIIP*/
5050
5051 EXCEPTION
5052 WHEN OTHERS THEN
5053 x_err_code := SQLCODE;
5054 x_err_msg := substr(
5055 ('WSMPINVL.MISC_RECEIPT('
5056 ||l_stmt_num
5057 || '): '
5058 || SQLERRM
5059 ), 1, 2000);
5060 END Misc_Receipt;
5061 -------------------------------------------------------------------------
5062
5063 FUNCTION Launch_Worker(
5064 X_Header_Id1 IN NUMBER,
5065 X_Message OUT NOCOPY VARCHAR2
5066 ) RETURN BOOLEAN IS
5067
5068 req_id NUMBER;
5069 phase VARCHAR2(2000);
5070 status VARCHAR2(2000);
5071 devphase VARCHAR2(2000);
5072 devstatus VARCHAR2(2000);
5073 message VARCHAR2(1000);
5074 wait BOOLEAN;
5075
5076 BEGIN
5077 /*Bug 3733798
5078 req_id := FND_REQUEST.submit_request
5079 ('INV', 'INCTCW', NULL, NULL, FALSE,
5080 to_char(x_header_id1), '1', NULL, NULL);*/
5081 /*Added for Bug 3733798*/
5082 req_id := FND_REQUEST.submit_request
5083 ('INV', 'INCTCW', NULL, NULL, FALSE,
5084 to_char(x_header_id1), '4', NULL, NULL);
5085 fnd_file.put_line(fnd_file.log,'Inventory Transaction Worker request_id is '
5086 ||to_char(req_id));
5087 fnd_file.put_line(fnd_file.log,'Material Transaction temp_header_id is '
5088 ||to_char(x_header_id1));
5089
5090 /* You must COMMIT to submit the request */
5091 COMMIT;
5092 /* If req_id = 0, the request could not be submitted */
5093 IF req_id = 0 THEN
5094 X_Message := substr(FND_MESSAGE.get, 1, 2000);
5095 return(FALSE);
5096 else
5097 /* Wait for the request to finish */
5098 -- modified the parameter passing as non-positional.
5099 -- Bala Balakumar.
5100
5101 wait := FND_CONCURRENT.WAIT_FOR_REQUEST
5102 (request_id => req_id,
5103 interval => 10, -- 10 seconds interval
5104 max_wait => 36000, -- 10 Hours maximum wait.
5105 phase => phase,
5106 status => status,
5107 dev_phase => devphase,
5108 dev_status => devstatus,
5109 message => message);
5110
5111 fnd_file.put_line(fnd_file.log,
5112 'Inventory Transaction Worker status is '
5113 ||status);
5114 fnd_file.put_line(fnd_file.log,
5115 'Inventory Transaction Worker Completion Message: '
5116 ||message);
5117
5118 -- Confirmed that this condition statement is correct.
5119 -- Bala Balakumar.
5120
5121 IF devphase <> 'COMPLETE'
5122 OR devstatus <> 'NORMAL' THEN
5123
5124 X_Message := substr(message, 1, 1000); -- message returned is only 255 char.
5125 return(FALSE);
5126 END IF;
5127
5128 END IF;
5129 return(TRUE);
5130
5131 END Launch_Worker;
5132 -------------------------------------------------------------------------------
5133 PROCEDURE Success_All
5134 (p_header_id NUMBER,
5135 p_group_id NUMBER,
5136 x_err_code OUT NOCOPY NUMBER ,
5137 x_err_msg OUT NOCOPY VARCHAR2,
5138 p_mode NUMBER) /*Bug 4779518 fix*/ IS
5139
5140 x_process_status NUMBER;
5141 l_stmt_num NUMBER;
5142 l_err_num NUMBER;
5143 l_err_code VARCHAR2(240);
5144 l_err_msg VARCHAR2(2000);
5145
5146 BEGIN
5147 l_stmt_num := 10;
5148
5149 IF p_group_id is NOT NULL THEN
5150 UPDATE wsm_lot_split_merges_interface
5151 SET PROCESS_STATUS = COMPLETE
5152 , ERROR_MESSAGE = NULL
5153 WHERE PROCESS_STATUS =PENDING
5154 AND GROUP_ID = p_group_id
5155 AND header_id=p_header_id;
5156 ELSE
5157
5158 UPDATE wsm_lot_split_merges_interface
5159 SET PROCESS_STATUS = COMPLETE
5160 , ERROR_MESSAGE = NULL
5161 WHERE PROCESS_STATUS = PENDING
5162 AND header_ID = p_header_id;
5163 END IF;
5164
5165
5166 l_stmt_num :=20;
5167
5168 INSERT INTO WSM_lot_split_merges
5169 (transaction_id,
5170 transaction_type_id,
5171 organization_id,
5172 wip_flaG,
5173 split_flag ,
5174 last_update_date ,
5175 last_updated_by ,
5176 creation_date ,
5177 created_by ,
5178 transaction_reference ,
5179 reason_id ,
5180 transaction_date,
5181 last_update_login ,
5182 attribute_category ,
5183 attribute1 ,
5184 attribute2 ,
5185 attribute3 ,
5186 attribute4 ,
5187 attribute5 ,
5188 attribute6 ,
5189 attribute7 ,
5190 attribute8 ,
5191 attribute9 ,
5192 attribute10 ,
5193 attribute11 ,
5194 attribute12 ,
5195 attribute13 ,
5196 attribute14 ,
5197 attribute15 ,
5198 request_id ,
5199 program_application_id ,
5200 program_id ,
5201 program_update_date
5202 )
5203 SELECT
5204 transaction_id,
5205 transaction_type_id,
5206 organization_id,
5207 wip_flag,
5208 split_flag ,
5209 last_update_date ,
5210 last_updated_by ,
5211 creation_date ,
5212 created_by ,
5213 transaction_reference ,
5214 reason_id ,
5215 transaction_date,
5216 last_update_login ,
5217 attribute_CATEGORY ,
5218 attribute1 ,
5219 attribute2 ,
5220 attribute3 ,
5221 attribute4 ,
5222 attribute5 ,
5223 attribute6 ,
5224 attribute7 ,
5225 attribute8 ,
5226 attribute9 ,
5227 attribute10 ,
5228 attribute11 ,
5229 attribute12 ,
5230 attribute13 ,
5231 attribute14 ,
5232 attribute15 ,
5233 request_id ,
5234 program_application_id ,
5235 program_id ,
5236 program_update_date
5237 FROM wsm_lot_split_merges_interface
5238 WHERE header_id = p_header_id
5239 and process_status=COMPLETE;
5240
5241 l_stmt_num :=30;
5242
5243 INSERT INTO wsm_sm_starting_lots
5244 (
5245 transaction_id,
5246 lot_number,
5247 inventory_item_id,
5248 organization_id,
5249 revision,
5250 quantity,
5251 subinventory_code,
5252 locator_id,
5253 last_update_date,
5254 last_updated_by,
5255 creation_date,
5256 created_by,
5257 last_update_login,
5258 request_id,
5259 program_application_id,
5260 program_id,
5261 program_update_date,
5262 attribute_category,
5263 attribute1,
5264 attribute2,
5265 attribute3,
5266 attribute4,
5267 attribute5,
5268 attribute6,
5269 attribute7,
5270 attribute8,
5271 attribute9,
5272 attribute10,
5273 attribute11,
5274 attribute12,
5275 attribute13,
5276 attribute14,
5277 attribute15
5278 )
5279 SELECT
5280 h.transaction_id,
5281 s.lot_number,
5282 s.inventory_item_id,
5283 s.organization_id,
5284 s.revision,
5285 s.quantity,
5286 s.subinventory_code,
5287 s.locator_id,
5288 sysdate,
5289 user,
5290 sysdate,
5291 user,
5292 login,
5293 request,
5294 progappl,
5295 program,
5296 sysdate,
5297 s.attribute_category,
5298 s.attribute1,
5299 s.attribute2,
5300 s.attribute3,
5301 s.attribute4,
5302 s.attribute5,
5303 s.attribute6,
5304 s.attribute7,
5305 s.attribute8,
5306 s.attribute9,
5307 s.attribute10,
5308 s.attribute11,
5309 s.attribute12,
5310 s.attribute13,
5311 s.attribute14,
5312 s.attribute15
5313 FROM wsm_starting_lots_interface s,
5314 wsm_lot_split_merges_interface h
5315 WHERE h.header_id = p_header_id
5316 and s.header_id = h.header_id;
5317
5318
5319 l_stmt_num :=40;
5320
5321 INSERT INTO wsm_sm_resulting_lots
5322 (
5323 transaction_id,
5324 lot_number,
5325 inventory_item_id,
5326 organization_id,
5327 wip_entity_id,
5328 quantity,
5329 subinventory_code,
5330 locator_id,
5331 revision,
5332 last_update_date,
5333 last_updated_by,
5334 creation_date,
5335 created_by,
5336 last_update_login,
5337 request_id,
5338 program_application_id,
5339 program_id,
5340 program_update_date,
5341 attribute_category,
5342 attribute1,
5343 attribute2,
5344 attribute3,
5345 attribute4,
5346 attribute5,
5347 attribute6,
5348 attribute7,
5349 attribute8,
5350 attribute9,
5351 attribute10,
5352 attribute11,
5353 attribute12,
5354 attribute13,
5355 attribute14,
5356 attribute15
5357 )
5358 SELECT
5359 h.transaction_id,
5360 r.lot_number,
5361 r.inventory_item_id,
5362 r.organization_id,
5363 r.wip_entity_id,
5364 r.quantity,
5365 r.subinventory_code,
5366 r.locator_id,
5367 r.revision,
5368 sysdate,
5369 USER,
5370 sysdate,
5371 USER,
5372 LOGIN,
5373 REQUEST,
5374 PROGAPPL,
5375 PROGRAM,
5376 sysdate,
5377 r.attribute_category,
5378 r.attribute1,
5379 r.attribute2,
5380 r.attribute3,
5381 r.attribute4,
5382 r.attribute5,
5383 r.attribute6,
5384 r.attribute7,
5385 r.attribute8,
5386 r.attribute9,
5387 r.attribute10,
5388 r.attribute11,
5389 r.attribute12,
5390 r.attribute13,
5391 r.attribute14,
5392 r.attribute15
5393 FROM wsm_resulting_lots_interface r
5394 , wsm_lot_split_merges_interface h
5395 WHERE h.header_id = p_header_id
5396 and r.header_id = h.header_id;
5397
5398 /*Bug 4779518 fix: don't commit for online mode, it commits rows in mmtt, base tables, etc.*/
5399 IF p_mode = CONCURRENT THEN
5400 COMMIT; --moved the commit from exception block to here BBK.
5401 END IF;
5402
5403 EXCEPTION
5404 WHEN OTHERS THEN
5405 x_err_code := SQLCODE;
5406 x_err_msg := substr(
5407 ('WSMPINVL.SUCCESS_ALL('
5408 ||l_stmt_num
5409 || '): '
5410 || SQLERRM
5411 ), 1, 2000);
5412 -- COMMIT; --Why is this commit in Exception Block?BBK
5413 END Success_All;
5414 ------------------------------------------------------------------
5415
5416 PROCEDURE Error_All
5417 (p_header_id NUMBER,
5418 p_group_id NUMBER,
5419 p_message VARCHAR2) IS
5420
5421 x_message VARCHAR2(2000);
5422 l_stmt_num NUMBER;
5423 x_err_num NUMBER;
5424 x_err_msg VARCHAR2(2000);
5425
5426
5427 -- added by BBK for debugging
5428 lProcName VARCHAR2(32) := 'error_all';
5429 lProcLocation NUMBER := 0;
5430
5431
5432 BEGIN
5433 -- IF p_group_id is NOT NULL THEN -- bugfix 2449452 : not needed since its enough to use header_id parameter.
5434
5435 l_stmt_num := 10;
5436
5437 UPDATE wsm_lot_split_merges_interface
5438
5439 SET PROCESS_STATUS = ERROR
5440 /*BA#IIIP*/
5441 , error_message = p_message
5442 , request_id = REQUEST
5443 , program_id = PROGRAM
5444 , program_application_id = PROGAPPL
5445 /*EA#IIIP*/
5446
5447 WHERE PROCESS_STATUS <> ERROR
5448 --AND GROUP_ID = p_group_id --bugfix 2449452: header_id is unique.
5449 and header_id=p_header_id;
5450
5451 l_stmt_num := 20;
5452 /*
5453 ** Commented out by Bala Balakumar. We will use
5454 ** a utility procedure in WSMPUTIL.write_to_wie to do this
5455 ** job to be consistent across all OSFM Interfaces.
5456 **
5457 ** Bala Balakumar.
5458 **
5459
5460 INSERT INTO WSM_INTERFACE_ERRORS(header_id,message ,
5461 creation_date, last_update_date, last_updated_by, created_by)
5462 SELECT transaction_id,p_message, sysdate, sysdate,
5463 last_updated_by, created_by
5464 FROM wsm_lot_split_merges_interface
5465 WHERE PROCESS_STATUS = ERROR
5466 AND GROUP_ID = p_group_id
5467 and transaction_id=p_header_id;
5468
5469 */
5470
5471 -- Call to WSMPUTIL to write to WSM_INTERFACE_ERRORS table.
5472 -- added by Bala Balakumar.
5473 /*BA#IIIP*/
5474
5475 x_err_msg := (
5476 'HeaderId - '||p_header_id||';'||
5477 'MsgType - '||Message_Type_Error||';'||
5478 'RequestId - '||REQUEST||';'||
5479 'ProgramId - '||PROGRAM||';'||
5480 'ProgAppId - '||PROGAPPL||';'||
5481 substr(p_message, 1, 200)
5482 );
5483 showProgress(
5484 processingMode => CONCURRENT
5485 , headerId => p_header_id
5486 , procName => lProcName
5487 , procLocation => l_stmt_num
5488 , showMessage => x_err_msg);
5489 /*EA#IIIP*/
5490
5491 l_stmt_num := 30;
5492 WSMPUTIL.write_to_wie(
5493 p_header_id => p_header_id
5494 , p_message_type => Message_Type_Error
5495 , p_message => p_message
5496 , p_request_id => REQUEST
5497 , p_program_id => PROGRAM
5498 , p_program_application_id => PROGAPPL
5499 , x_err_code => x_err_num
5500 , x_err_msg => x_err_msg
5501 );
5502
5503 showProgress(
5504 processingMode => 2
5505 , headerId => p_header_id
5506 , procName => lProcName
5507 , procLocation => l_stmt_num
5508 , showMessage => x_err_msg);
5509
5510
5511 l_stmt_num := 40;
5512
5513
5514 -- END IF; bugfix 2449452
5515
5516 EXCEPTION
5517 WHEN OTHERS THEN
5518 x_err_num := SQLCODE;
5519 x_err_msg := substr(
5520 ('WSMPINVL.ERROR_ALL('
5521 ||l_stmt_num
5522 || '): '
5523 || SQLERRM
5524 ), 1, 2000);
5525
5526 showProgress(
5527 processingMode => CONCURRENT
5528 , headerId => p_header_id
5529 , procName => lProcName
5530 , procLocation => l_stmt_num
5531 , showMessage => x_err_msg);
5532
5533
5534 END Error_All;
5535 ------------------------------------------------------------------
5536 /*BA#IIIP*/
5537 Procedure showProgress(
5538 processingMode IN NUMBER,
5539 headerId NUMBER,
5540 procName IN VARCHAR2, --BBK for enhancing Debugging
5541 procLocation IN NUMBER, --BBK for enhancing Debugging
5542 showMessage VARCHAR2) IS
5543
5544 x_message_buffer varchar2(2000) := NULL;
5545
5546 Begin
5547
5548
5549 If g_debug = 'Y' and processingMode = CONCURRENT Then
5550
5551 x_message_buffer := substr(
5552 (procName
5553 ||'('
5554 ||procLocation
5555 ||') '
5556 || 'Header Id: '
5557 || headerId
5558 || '; '
5559 || showMessage
5560 ), 1, 2000);
5561
5562 fnd_file.put_line(fnd_file.log,x_message_buffer);
5563
5564 End If;
5565 return;
5566
5567 End showProgress;
5568 ----------------------------------------------------------------
5569 Procedure writeToLog(
5570 RequestId NUMBER
5571 , programId NUMBER
5572 , programApplnId NUMBER
5573 )IS
5574
5575 CURSOR wie_cursor IS
5576 SELECT distinct
5577 wie.header_id
5578 , wie.message_type
5579 , wie.message
5580 FROM wsm_interface_errors wie
5581 WHERE wie.request_id = requestId
5582 And wie.program_application_id = programApplnId
5583 And wie.program_id = programId;
5584
5585 x_error_message varchar2(2000) := NULL;
5586
5587 Begin
5588 --bugfix 2449452. added these debug stmts
5589
5590 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------');
5591 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERRORS ENCOUNTERED..');
5592 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------');
5593
5594 For wie_record in wie_cursor Loop
5595
5596 If wie_record.message_type = Message_Type_Warning Then
5597
5598 x_error_message := 'Header Id: '||
5599 wie_record.header_id ||
5600 ' has warning.';
5601
5602 Else
5603
5604 x_error_message := 'Header Id: '||
5605 wie_record.header_id ||
5606 ' has error.';
5607
5608 End If;
5609
5610 fnd_file.put_line(fnd_file.log, x_error_message);
5611
5612 x_error_message := substr(
5613 ('('
5614 || wie_record.header_id
5615 || ') '
5616 || wie_record.message
5617 ), 1, 2000);
5618
5619 fnd_file.put_line(fnd_file.log, x_error_message);
5620
5621 x_error_message := NULL;
5622
5623 End Loop; -- Cursor Loop
5624
5625 x_error_message := 'End of Log for Request Id: '||
5626 requestId;
5627
5628 fnd_file.put_line(fnd_file.log, x_error_message);
5629
5630 End writeToLog;
5631 /*EA#IIIP*/
5632 ------------------------------------------------------------------
5633 END WSMPINVL;