[Home] [Help]
PACKAGE BODY: APPS.WSMPINVL
Source
1 PACKAGE BODY WSMPINVL AS
2 /* $Header: WSMINVLB.pls 120.7 2006/07/12 06:24:50 mprathap noship $ */
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_LOT_NONUNIQUE');
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_LOT_NONUNIQUE');
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_LOT_NONUNIQUE');
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 AND x_lot_exists='N' THEN
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 )
4472 values
4473 (
4474 X_temp_id,
4475 X_date,
4476 X_date,
4477 USER,
4478 USER,
4479 LOGIN,
4480 -1 * X_quantity,
4481 -1 * X_quantity,
4482 X_lot_number
4483 )
4484 ;
4485 /*BA#IIIP*/
4486 showProgress(
4487 processingMode => CONCURRENT
4488 , headerId => x_header_id
4489 , procName => lProcName
4490 , procLocation => l_stmt_num
4491 , showMessage => 'Sucessful insert to MTLT for Misc.Issue');
4492 /*EA#IIIP*/
4493
4494 EXCEPTION
4495 WHEN OTHERS THEN
4496 x_err_code := SQLCODE;
4497 x_err_msg := substr(
4498 ('WSMPINVL.MISC_ISSUE('
4499 ||l_stmt_num
4500 || '): '
4501 || SQLERRM
4502 ), 1, 2000);
4503 END Misc_Issue;
4504 --------------------------------------------------------------------
4505
4506 PROCEDURE Misc_Receipt
4507 (
4508 X_Header_Id1 IN NUMBER,
4509 X_Inventory_Item_Id IN NUMBER,
4510 X_Organization_id IN NUMBER,
4511 X_Quantity IN NUMBER,
4512 X_Acct_Period_Id IN NUMBER,
4513 X_Lot_Number IN VARCHAR2,
4514 X_Subinventory IN VARCHAR2,
4515 X_Locator_Id IN NUMBER,
4516 X_Revision IN VARCHAR2,
4517 X_Reason_Id IN NUMBER,
4518 X_Reference IN VARCHAR2,
4519 X_Transaction_Date IN DATE,
4520 X_Source_Line_Id IN NUMBER,
4521 X_Header_Id IN NUMBER, -- added by Bala.
4522 x_lot_attributes_rec IN lot_attributes_rec_type, -- added by sisankar for bug 4920235
4523 x_invattr_tbl IN inv_lot_api_pub.char_tbl,
4524 x_Cattr_tbl IN inv_lot_api_pub.char_tbl,
4525 x_Dattr_tbl IN inv_lot_api_pub.date_tbl,
4526 x_Nattr_tbl IN inv_lot_api_pub.number_tbl,
4527 x_err_code OUT NOCOPY NUMBER ,
4528 x_err_msg OUT NOCOPY VARCHAR2) IS
4529
4530 X_Temp_Id NUMBER;
4531 X_Date DATE := SYSDATE;
4532 X_Uom VARCHAR2(3);
4533 X_Dist_Acct_Id NUMBER;
4534 l_stmt_num NUMBER;
4535 l_err_num NUMBER;
4536 l_err_code VARCHAR2(240);
4537 l_err_msg VARCHAR2(2000);
4538
4539 -- added by BBK for debugging
4540 lProcName VARCHAR2(32) := 'misc_receipt';
4541 lProcLocation NUMBER := 0;
4542
4543
4544
4545 BEGIN
4546 Set_Vars;
4547
4548 l_stmt_num :=10;
4549
4550 --commented out by abedajna for perf. tuning
4551 /*SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
4552 **INTO X_Temp_Id
4553 **FROM DUAL;
4554 */
4555
4556 l_stmt_num :=20;
4557 SELECT msi.primary_uom_code
4558 INTO x_uom
4559 FROM mtl_system_items msi
4560 WHERE msi.INVENTORY_ITEM_ID = X_Inventory_Item_Id
4561 AND msi.ORGANIZATION_ID = X_Organization_Id;
4562
4563 l_stmt_num :=30;
4564
4565 /*BA#1754109*/
4566
4567 -- Replaced the fix for Bug#1752110
4568 -- ORA-1403 can occur if no alias is defined for the txn acct.
4569 -- An account alias need not have to be defined for a txn acct.
4570 -- Ensure, that the txn acct is enabled, non-summary acct and active.
4571 -- Bala Balakumar, May 1st, 2001.
4572
4573 Begin
4574
4575 select wp.transaction_account_id into x_dist_acct_id
4576 from wsm_parameters wp
4577 Where wp.organization_id = X_Organization_Id
4578 and exists (select 1
4579 From gl_code_combinations gl
4580 Where gl.code_combination_id = wp.transaction_account_id
4581 and gl.enabled_flag = 'Y'
4582 and gl.summary_flag = 'N'
4583 and NVL(gl.start_date_active, sysdate) <= sysdate
4584 and NVL(gl.end_date_active, sysdate) >= sysdate
4585 );
4586
4587
4588 EXCEPTION when others THEN
4589
4590 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
4591 FND_MESSAGE.SET_TOKEN('FLD_NAME','Transaction Account for the Organization');
4592 x_err_msg := fnd_message.get;
4593 x_err_code := SQLCODE;
4594 return;
4595
4596 End;
4597
4598 /*EA#1754109*/
4599
4600 l_stmt_num :=40;
4601
4602
4603 INSERT INTO mtl_material_transactions_temp
4604 (last_update_date,
4605 creation_date,
4606 last_updated_by,
4607 created_by,
4608 last_update_login,
4609 transaction_header_id,
4610 inventory_item_id,
4611 organization_id,
4612 revision,
4613 subinventory_code,
4614 locator_id,
4615 transaction_quantity,
4616 primary_quantity,
4617 transaction_uom,
4618 transaction_type_id,
4619 transaction_action_id,
4620 transaction_source_type_id,
4621 transaction_date,
4622 acct_period_id,
4623 reason_id,
4624 transaction_reference,
4625 process_flag,
4626 posting_flag,
4627 transaction_temp_id,
4628 distribution_account_id,
4629 source_code,
4630 source_line_id
4631 /*BA#IIIP*/
4632 , LOT_NUMBER
4633 /*EA#IIIP*/
4634 )
4635 VALUES
4636 (X_date, /* LAST_UPDATE_DATE */
4637 X_date, /* CREATION_DATE */
4638 USER, /* LAST_UPDATED_BY */
4639 USER, /* CREATED_BY */
4640 LOGIN,
4641 X_Header_Id1, /* TRANSACTION_HEADER_ID */
4642 X_Inventory_Item_Id, /* INVENTORY_ITEM_ID */
4643 X_Organization_Id, /* ORGANIZATION_ID */
4644 X_Revision, /* REVISION */
4645 X_Subinventory, /* SUBINVENTORY_CODE */
4646 X_Locator_Id,
4647 X_Quantity, /* TRANSACTION_QUANTITY */
4648 X_Quantity, /* PRIMARY_QUANTITY */
4649 X_Uom, /* UNIT_OF_MEASURE */
4650 42, /* TRANSACTION_TYPE_ID */
4651 27, /* TRANSACTION_ACTION_ID */
4652 13, /* TRANSACTION_SOURCE_TYPE_ID */
4653 X_transaction_date, /* TRANSACTION_DATE */
4654 X_Acct_Period_Id, /* ACCT_PERIOD_ID */
4655 X_Reason_Id, /* REASON_ID */
4656 X_Reference, /* TRANSACTION_REFERENCE */
4657 'Y', /* PROCESS_FLAG */
4658 'Y', /* POSTING_FLAG */
4659 -- abb X_temp_id, /* Transaction Temp Id */
4660 MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, /* abedajna, tuning */
4661 X_Dist_Acct_Id, /* distribution account id */
4662 'WSM', /* Source Code */
4663 X_Source_Line_Id /* Transaction Id in WLSMI table */
4664 /*BA#IIIP*/
4665 ,X_LOT_NUMBER
4666 /*EA#IIIP*/
4667 )
4668 RETURNING transaction_temp_id INTO X_Temp_Id; -- abedajna, perf. Tuning
4669
4670 /*BA#IIIP*/
4671 showProgress(
4672 processingMode => CONCURRENT
4673 , headerId => x_header_id
4674 , procName => lProcName
4675 , procLocation => l_stmt_num
4676 , showMessage => 'Sucessful insert to MMTT for Misc.Receipt');
4677 /*EA#IIIP*/
4678
4679 l_stmt_num :=50;
4680
4681 INSERT INTO MTL_TRANSACTION_LOTS_TEMP
4682 (
4683 transaction_temp_id,
4684 last_update_date,
4685 creation_date,
4686 last_updated_by,
4687 created_by,
4688 last_update_login,
4689 transaction_quantity,
4690 primary_quantity,
4691 lot_number,
4692 lot_expiration_date,
4693 DESCRIPTION, -- added by sisankar for bug 4920235
4694 GRADE_CODE,
4695 ORIGINATION_DATE,
4696 DATE_CODE,
4697 CHANGE_DATE,
4698 AGE,
4699 RETEST_DATE,
4700 MATURITY_DATE,
4701 ITEM_SIZE,
4702 COLOR,
4703 VOLUME,
4704 VOLUME_UOM,
4705 PLACE_OF_ORIGIN,
4706 BEST_BY_DATE,
4707 LENGTH,
4708 LENGTH_UOM,
4709 RECYCLED_CONTENT,
4710 THICKNESS,
4711 THICKNESS_UOM,
4712 WIDTH,
4713 WIDTH_UOM,
4714 VENDOR_ID,
4715 VENDOR_NAME,
4716 TERRITORY_CODE,
4717 SUPPLIER_LOT_NUMBER,
4718 CURL_WRINKLE_FOLD,
4719 LOT_ATTRIBUTE_CATEGORY ,
4720 C_ATTRIBUTE1,
4721 C_ATTRIBUTE2,
4722 C_ATTRIBUTE3,
4723 C_ATTRIBUTE4,
4724 C_ATTRIBUTE5,
4725 C_ATTRIBUTE6,
4726 C_ATTRIBUTE7,
4727 C_ATTRIBUTE8,
4728 C_ATTRIBUTE9,
4729 C_ATTRIBUTE10,
4730 C_ATTRIBUTE11,
4731 C_ATTRIBUTE12,
4732 C_ATTRIBUTE13,
4733 C_ATTRIBUTE14,
4734 C_ATTRIBUTE15,
4735 C_ATTRIBUTE16,
4736 C_ATTRIBUTE17,
4737 C_ATTRIBUTE18,
4738 C_ATTRIBUTE19,
4739 C_ATTRIBUTE20,
4740 D_ATTRIBUTE1,
4741 D_ATTRIBUTE2,
4742 D_ATTRIBUTE3,
4743 D_ATTRIBUTE4,
4744 D_ATTRIBUTE5,
4745 D_ATTRIBUTE6,
4746 D_ATTRIBUTE7,
4747 D_ATTRIBUTE8,
4748 D_ATTRIBUTE9,
4749 D_ATTRIBUTE10,
4750 N_ATTRIBUTE1,
4751 N_ATTRIBUTE2,
4752 N_ATTRIBUTE3,
4753 N_ATTRIBUTE4,
4754 N_ATTRIBUTE5,
4755 N_ATTRIBUTE6,
4756 N_ATTRIBUTE7,
4757 N_ATTRIBUTE8,
4758 N_ATTRIBUTE9,
4759 N_ATTRIBUTE10,
4760 ATTRIBUTE_CATEGORY,
4761 ATTRIBUTE1,
4762 ATTRIBUTE2,
4763 ATTRIBUTE3,
4764 ATTRIBUTE4,
4765 ATTRIBUTE5,
4766 ATTRIBUTE6,
4767 ATTRIBUTE7,
4768 ATTRIBUTE8,
4769 ATTRIBUTE9,
4770 ATTRIBUTE10,
4771 ATTRIBUTE11,
4772 ATTRIBUTE12,
4773 ATTRIBUTE13,
4774 ATTRIBUTE14,
4775 ATTRIBUTE15
4776 )
4777 values
4778 (
4779 X_temp_id,
4780 X_date,
4781 X_date,
4782 USER,
4783 USER,
4784 LOGIN,
4785 X_quantity,
4786 X_quantity,
4787 X_lot_number,
4788 x_exp_date,
4789 x_lot_attributes_rec.l_description, -- added by sisankar for bug 4920235
4790 x_lot_attributes_rec.l_grade_code,
4791 x_lot_attributes_rec.l_origination_date,
4792 x_lot_attributes_rec.l_date_code,
4793 x_lot_attributes_rec.l_change_date,
4794 x_lot_attributes_rec.l_age,
4795 x_lot_attributes_rec.l_retest_date,
4796 x_lot_attributes_rec.l_maturity_date,
4797 x_lot_attributes_rec.l_item_size,
4798 x_lot_attributes_rec.l_color,
4799 x_lot_attributes_rec.l_volume,
4800 x_lot_attributes_rec.l_volume_uom,
4801 x_lot_attributes_rec.l_place_of_origin,
4802 x_lot_attributes_rec.l_best_by_date,
4803 x_lot_attributes_rec.l_length,
4804 x_lot_attributes_rec.l_length_uom,
4805 x_lot_attributes_rec.l_recycled_content,
4806 x_lot_attributes_rec.l_thickness,
4807 x_lot_attributes_rec.l_thickness_uom,
4808 x_lot_attributes_rec.l_width,
4809 x_lot_attributes_rec.l_width_uom,
4810 x_lot_attributes_rec.l_vendor_id,
4811 x_lot_attributes_rec.l_vendor_name,
4812 x_lot_attributes_rec.l_territory_code,
4813 x_lot_attributes_rec.l_supplier_lot_number,
4814 x_lot_attributes_rec.l_curl_wrinkle_fold,
4815 x_lot_attributes_rec.l_lot_attribute_category,
4816 x_Cattr_tbl(1),
4817 x_Cattr_tbl(2),
4818 x_Cattr_tbl(3),
4819 x_Cattr_tbl(4),
4820 x_Cattr_tbl(5),
4821 x_Cattr_tbl(6),
4822 x_Cattr_tbl(7),
4823 x_Cattr_tbl(8),
4824 x_Cattr_tbl(9),
4825 x_Cattr_tbl(10),
4826 x_Cattr_tbl(11),
4827 x_Cattr_tbl(12),
4828 x_Cattr_tbl(13),
4829 x_Cattr_tbl(14),
4830 x_Cattr_tbl(15),
4831 x_Cattr_tbl(16),
4832 x_Cattr_tbl(17),
4833 x_Cattr_tbl(18),
4834 x_Cattr_tbl(19),
4835 x_Cattr_tbl(20),
4836 x_Dattr_tbl(1),
4837 x_Dattr_tbl(2),
4838 x_Dattr_tbl(3),
4839 x_Dattr_tbl(4),
4840 x_Dattr_tbl(5),
4841 x_Dattr_tbl(6),
4842 x_Dattr_tbl(7),
4843 x_Dattr_tbl(8),
4844 x_Dattr_tbl(9),
4845 x_Dattr_tbl(10),
4846 x_Nattr_tbl(1),
4847 x_Nattr_tbl(2),
4848 x_Nattr_tbl(3),
4849 x_Nattr_tbl(4),
4850 x_Nattr_tbl(5),
4851 x_Nattr_tbl(6),
4852 x_Nattr_tbl(7),
4853 x_Nattr_tbl(8),
4854 x_Nattr_tbl(9),
4855 x_Nattr_tbl(10),
4856 x_lot_attributes_rec.l_attribute_category,
4857 x_invattr_tbl(1),
4858 x_invattr_tbl(2),
4859 x_invattr_tbl(3),
4860 x_invattr_tbl(4),
4861 x_invattr_tbl(5),
4862 x_invattr_tbl(6),
4863 x_invattr_tbl(7),
4864 x_invattr_tbl(8),
4865 x_invattr_tbl(9),
4866 x_invattr_tbl(10),
4867 x_invattr_tbl(11),
4868 x_invattr_tbl(12),
4869 x_invattr_tbl(13),
4870 x_invattr_tbl(14),
4871 x_invattr_tbl(15)
4872 );
4873
4874 /*BA#IIIP*/
4875 showProgress(
4876 processingMode => CONCURRENT
4877 , headerId => x_header_id
4878 , procName => lProcName
4879 , procLocation => l_stmt_num
4880 , showMessage => 'Sucessful insert to MTLT for Misc.Receipt');
4881 /*EA#IIIP*/
4882
4883 EXCEPTION
4884 WHEN OTHERS THEN
4885 x_err_code := SQLCODE;
4886 x_err_msg := substr(
4887 ('WSMPINVL.MISC_RECEIPT('
4888 ||l_stmt_num
4889 || '): '
4890 || SQLERRM
4891 ), 1, 2000);
4892 END Misc_Receipt;
4893 -------------------------------------------------------------------------
4894
4895 FUNCTION Launch_Worker(
4896 X_Header_Id1 IN NUMBER,
4897 X_Message OUT NOCOPY VARCHAR2
4898 ) RETURN BOOLEAN IS
4899
4900 req_id NUMBER;
4901 phase VARCHAR2(2000);
4902 status VARCHAR2(2000);
4903 devphase VARCHAR2(2000);
4904 devstatus VARCHAR2(2000);
4905 message VARCHAR2(1000);
4906 wait BOOLEAN;
4907
4908 BEGIN
4909 /*Bug 3733798
4910 req_id := FND_REQUEST.submit_request
4911 ('INV', 'INCTCW', NULL, NULL, FALSE,
4912 to_char(x_header_id1), '1', NULL, NULL);*/
4913 /*Added for Bug 3733798*/
4914 req_id := FND_REQUEST.submit_request
4915 ('INV', 'INCTCW', NULL, NULL, FALSE,
4916 to_char(x_header_id1), '4', NULL, NULL);
4917 fnd_file.put_line(fnd_file.log,'Inventory Transaction Worker request_id is '
4918 ||to_char(req_id));
4919 fnd_file.put_line(fnd_file.log,'Material Transaction temp_header_id is '
4920 ||to_char(x_header_id1));
4921
4922 /* You must COMMIT to submit the request */
4923 COMMIT;
4924 /* If req_id = 0, the request could not be submitted */
4925 IF req_id = 0 THEN
4926 X_Message := substr(FND_MESSAGE.get, 1, 2000);
4927 return(FALSE);
4928 else
4929 /* Wait for the request to finish */
4930 -- modified the parameter passing as non-positional.
4931 -- Bala Balakumar.
4932
4933 wait := FND_CONCURRENT.WAIT_FOR_REQUEST
4934 (request_id => req_id,
4935 interval => 10, -- 10 seconds interval
4936 max_wait => 36000, -- 10 Hours maximum wait.
4937 phase => phase,
4938 status => status,
4939 dev_phase => devphase,
4940 dev_status => devstatus,
4941 message => message);
4942
4943 fnd_file.put_line(fnd_file.log,
4944 'Inventory Transaction Worker status is '
4945 ||status);
4946 fnd_file.put_line(fnd_file.log,
4947 'Inventory Transaction Worker Completion Message: '
4948 ||message);
4949
4950 -- Confirmed that this condition statement is correct.
4951 -- Bala Balakumar.
4952
4953 IF devphase <> 'COMPLETE'
4954 OR devstatus <> 'NORMAL' THEN
4955
4956 X_Message := substr(message, 1, 1000); -- message returned is only 255 char.
4957 return(FALSE);
4958 END IF;
4959
4960 END IF;
4961 return(TRUE);
4962
4963 END Launch_Worker;
4964 -------------------------------------------------------------------------------
4965 PROCEDURE Success_All
4966 (p_header_id NUMBER,
4967 p_group_id NUMBER,
4968 x_err_code OUT NOCOPY NUMBER ,
4969 x_err_msg OUT NOCOPY VARCHAR2,
4970 p_mode NUMBER) /*Bug 4779518 fix*/ IS
4971
4972 x_process_status NUMBER;
4973 l_stmt_num NUMBER;
4974 l_err_num NUMBER;
4975 l_err_code VARCHAR2(240);
4976 l_err_msg VARCHAR2(2000);
4977
4978 BEGIN
4979 l_stmt_num := 10;
4980
4981 IF p_group_id is NOT NULL THEN
4982 UPDATE wsm_lot_split_merges_interface
4983 SET PROCESS_STATUS = COMPLETE
4984 , ERROR_MESSAGE = NULL
4985 WHERE PROCESS_STATUS =PENDING
4986 AND GROUP_ID = p_group_id
4987 AND header_id=p_header_id;
4988 ELSE
4989
4990 UPDATE wsm_lot_split_merges_interface
4991 SET PROCESS_STATUS = COMPLETE
4992 , ERROR_MESSAGE = NULL
4993 WHERE PROCESS_STATUS = PENDING
4994 AND header_ID = p_header_id;
4995 END IF;
4996
4997
4998 l_stmt_num :=20;
4999
5000 INSERT INTO WSM_lot_split_merges
5001 (transaction_id,
5002 transaction_type_id,
5003 organization_id,
5004 wip_flaG,
5005 split_flag ,
5006 last_update_date ,
5007 last_updated_by ,
5008 creation_date ,
5009 created_by ,
5010 transaction_reference ,
5011 reason_id ,
5012 transaction_date,
5013 last_update_login ,
5014 attribute_category ,
5015 attribute1 ,
5016 attribute2 ,
5017 attribute3 ,
5018 attribute4 ,
5019 attribute5 ,
5020 attribute6 ,
5021 attribute7 ,
5022 attribute8 ,
5023 attribute9 ,
5024 attribute10 ,
5025 attribute11 ,
5026 attribute12 ,
5027 attribute13 ,
5028 attribute14 ,
5029 attribute15 ,
5030 request_id ,
5031 program_application_id ,
5032 program_id ,
5033 program_update_date
5034 )
5035 SELECT
5036 transaction_id,
5037 transaction_type_id,
5038 organization_id,
5039 wip_flag,
5040 split_flag ,
5041 last_update_date ,
5042 last_updated_by ,
5043 creation_date ,
5044 created_by ,
5045 transaction_reference ,
5046 reason_id ,
5047 transaction_date,
5048 last_update_login ,
5049 attribute_CATEGORY ,
5050 attribute1 ,
5051 attribute2 ,
5052 attribute3 ,
5053 attribute4 ,
5054 attribute5 ,
5055 attribute6 ,
5056 attribute7 ,
5057 attribute8 ,
5058 attribute9 ,
5059 attribute10 ,
5060 attribute11 ,
5061 attribute12 ,
5062 attribute13 ,
5063 attribute14 ,
5064 attribute15 ,
5065 request_id ,
5066 program_application_id ,
5067 program_id ,
5068 program_update_date
5069 FROM wsm_lot_split_merges_interface
5070 WHERE header_id = p_header_id
5071 and process_status=COMPLETE;
5072
5073 l_stmt_num :=30;
5074
5075 INSERT INTO wsm_sm_starting_lots
5076 (
5077 transaction_id,
5078 lot_number,
5079 inventory_item_id,
5080 organization_id,
5081 revision,
5082 quantity,
5083 subinventory_code,
5084 locator_id,
5085 last_update_date,
5086 last_updated_by,
5087 creation_date,
5088 created_by,
5089 last_update_login,
5090 request_id,
5091 program_application_id,
5092 program_id,
5093 program_update_date,
5094 attribute_category,
5095 attribute1,
5096 attribute2,
5097 attribute3,
5098 attribute4,
5099 attribute5,
5100 attribute6,
5101 attribute7,
5102 attribute8,
5103 attribute9,
5104 attribute10,
5105 attribute11,
5106 attribute12,
5107 attribute13,
5108 attribute14,
5109 attribute15
5110 )
5111 SELECT
5112 h.transaction_id,
5113 s.lot_number,
5114 s.inventory_item_id,
5115 s.organization_id,
5116 s.revision,
5117 s.quantity,
5118 s.subinventory_code,
5119 s.locator_id,
5120 sysdate,
5121 user,
5122 sysdate,
5123 user,
5124 login,
5125 request,
5126 progappl,
5127 program,
5128 sysdate,
5129 s.attribute_category,
5130 s.attribute1,
5131 s.attribute2,
5132 s.attribute3,
5133 s.attribute4,
5134 s.attribute5,
5135 s.attribute6,
5136 s.attribute7,
5137 s.attribute8,
5138 s.attribute9,
5139 s.attribute10,
5140 s.attribute11,
5141 s.attribute12,
5142 s.attribute13,
5143 s.attribute14,
5144 s.attribute15
5145 FROM wsm_starting_lots_interface s,
5146 wsm_lot_split_merges_interface h
5147 WHERE h.header_id = p_header_id
5148 and s.header_id = h.header_id;
5149
5150
5151 l_stmt_num :=40;
5152
5153 INSERT INTO wsm_sm_resulting_lots
5154 (
5155 transaction_id,
5156 lot_number,
5157 inventory_item_id,
5158 organization_id,
5159 wip_entity_id,
5160 quantity,
5161 subinventory_code,
5162 locator_id,
5163 revision,
5164 last_update_date,
5165 last_updated_by,
5166 creation_date,
5167 created_by,
5168 last_update_login,
5169 request_id,
5170 program_application_id,
5171 program_id,
5172 program_update_date,
5173 attribute_category,
5174 attribute1,
5175 attribute2,
5176 attribute3,
5177 attribute4,
5178 attribute5,
5179 attribute6,
5180 attribute7,
5181 attribute8,
5182 attribute9,
5183 attribute10,
5184 attribute11,
5185 attribute12,
5186 attribute13,
5187 attribute14,
5188 attribute15
5189 )
5190 SELECT
5191 h.transaction_id,
5192 r.lot_number,
5193 r.inventory_item_id,
5194 r.organization_id,
5195 r.wip_entity_id,
5196 r.quantity,
5197 r.subinventory_code,
5198 r.locator_id,
5199 r.revision,
5200 sysdate,
5201 USER,
5202 sysdate,
5203 USER,
5204 LOGIN,
5205 REQUEST,
5206 PROGAPPL,
5207 PROGRAM,
5208 sysdate,
5209 r.attribute_category,
5210 r.attribute1,
5211 r.attribute2,
5212 r.attribute3,
5213 r.attribute4,
5214 r.attribute5,
5215 r.attribute6,
5216 r.attribute7,
5217 r.attribute8,
5218 r.attribute9,
5219 r.attribute10,
5220 r.attribute11,
5221 r.attribute12,
5222 r.attribute13,
5223 r.attribute14,
5224 r.attribute15
5225 FROM wsm_resulting_lots_interface r
5226 , wsm_lot_split_merges_interface h
5227 WHERE h.header_id = p_header_id
5228 and r.header_id = h.header_id;
5229
5230 /*Bug 4779518 fix: don't commit for online mode, it commits rows in mmtt, base tables, etc.*/
5231 IF p_mode = CONCURRENT THEN
5232 COMMIT; --moved the commit from exception block to here BBK.
5233 END IF;
5234
5235 EXCEPTION
5236 WHEN OTHERS THEN
5237 x_err_code := SQLCODE;
5238 x_err_msg := substr(
5239 ('WSMPINVL.SUCCESS_ALL('
5240 ||l_stmt_num
5241 || '): '
5242 || SQLERRM
5243 ), 1, 2000);
5244 -- COMMIT; --Why is this commit in Exception Block?BBK
5245 END Success_All;
5246 ------------------------------------------------------------------
5247
5248 PROCEDURE Error_All
5249 (p_header_id NUMBER,
5250 p_group_id NUMBER,
5251 p_message VARCHAR2) IS
5252
5253 x_message VARCHAR2(2000);
5254 l_stmt_num NUMBER;
5255 x_err_num NUMBER;
5256 x_err_msg VARCHAR2(2000);
5257
5258
5259 -- added by BBK for debugging
5260 lProcName VARCHAR2(32) := 'error_all';
5261 lProcLocation NUMBER := 0;
5262
5263
5264 BEGIN
5265 -- IF p_group_id is NOT NULL THEN -- bugfix 2449452 : not needed since its enough to use header_id parameter.
5266
5267 l_stmt_num := 10;
5268
5269 UPDATE wsm_lot_split_merges_interface
5270
5271 SET PROCESS_STATUS = ERROR
5272 /*BA#IIIP*/
5273 , error_message = p_message
5274 , request_id = REQUEST
5275 , program_id = PROGRAM
5276 , program_application_id = PROGAPPL
5277 /*EA#IIIP*/
5278
5279 WHERE PROCESS_STATUS <> ERROR
5280 --AND GROUP_ID = p_group_id --bugfix 2449452: header_id is unique.
5281 and header_id=p_header_id;
5282
5283 l_stmt_num := 20;
5284 /*
5285 ** Commented out by Bala Balakumar. We will use
5286 ** a utility procedure in WSMPUTIL.write_to_wie to do this
5287 ** job to be consistent across all OSFM Interfaces.
5288 **
5289 ** Bala Balakumar.
5290 **
5291
5292 INSERT INTO WSM_INTERFACE_ERRORS(header_id,message ,
5293 creation_date, last_update_date, last_updated_by, created_by)
5294 SELECT transaction_id,p_message, sysdate, sysdate,
5295 last_updated_by, created_by
5296 FROM wsm_lot_split_merges_interface
5297 WHERE PROCESS_STATUS = ERROR
5298 AND GROUP_ID = p_group_id
5299 and transaction_id=p_header_id;
5300
5301 */
5302
5303 -- Call to WSMPUTIL to write to WSM_INTERFACE_ERRORS table.
5304 -- added by Bala Balakumar.
5305 /*BA#IIIP*/
5306
5307 x_err_msg := (
5308 'HeaderId - '||p_header_id||';'||
5309 'MsgType - '||Message_Type_Error||';'||
5310 'RequestId - '||REQUEST||';'||
5311 'ProgramId - '||PROGRAM||';'||
5312 'ProgAppId - '||PROGAPPL||';'||
5313 substr(p_message, 1, 200)
5314 );
5315 showProgress(
5316 processingMode => CONCURRENT
5317 , headerId => p_header_id
5318 , procName => lProcName
5319 , procLocation => l_stmt_num
5320 , showMessage => x_err_msg);
5321 /*EA#IIIP*/
5322
5323 l_stmt_num := 30;
5324 WSMPUTIL.write_to_wie(
5325 p_header_id => p_header_id
5326 , p_message_type => Message_Type_Error
5327 , p_message => p_message
5328 , p_request_id => REQUEST
5329 , p_program_id => PROGRAM
5330 , p_program_application_id => PROGAPPL
5331 , x_err_code => x_err_num
5332 , x_err_msg => x_err_msg
5333 );
5334
5335 showProgress(
5336 processingMode => 2
5337 , headerId => p_header_id
5338 , procName => lProcName
5339 , procLocation => l_stmt_num
5340 , showMessage => x_err_msg);
5341
5342
5343 l_stmt_num := 40;
5344
5345
5346 -- END IF; bugfix 2449452
5347
5348 EXCEPTION
5349 WHEN OTHERS THEN
5350 x_err_num := SQLCODE;
5351 x_err_msg := substr(
5352 ('WSMPINVL.ERROR_ALL('
5353 ||l_stmt_num
5354 || '): '
5355 || SQLERRM
5356 ), 1, 2000);
5357
5358 showProgress(
5359 processingMode => CONCURRENT
5360 , headerId => p_header_id
5361 , procName => lProcName
5362 , procLocation => l_stmt_num
5363 , showMessage => x_err_msg);
5364
5365
5366 END Error_All;
5367 ------------------------------------------------------------------
5368 /*BA#IIIP*/
5369 Procedure showProgress(
5370 processingMode IN NUMBER,
5371 headerId NUMBER,
5372 procName IN VARCHAR2, --BBK for enhancing Debugging
5373 procLocation IN NUMBER, --BBK for enhancing Debugging
5374 showMessage VARCHAR2) IS
5375
5376 x_message_buffer varchar2(2000) := NULL;
5377
5378 Begin
5379
5380
5381 If g_debug = 'Y' and processingMode = CONCURRENT Then
5382
5383 x_message_buffer := substr(
5384 (procName
5385 ||'('
5386 ||procLocation
5387 ||') '
5388 || 'Header Id: '
5389 || headerId
5390 || '; '
5391 || showMessage
5392 ), 1, 2000);
5393
5394 fnd_file.put_line(fnd_file.log,x_message_buffer);
5395
5396 End If;
5397 return;
5398
5399 End showProgress;
5400 ----------------------------------------------------------------
5401 Procedure writeToLog(
5402 RequestId NUMBER
5403 , programId NUMBER
5404 , programApplnId NUMBER
5405 )IS
5406
5407 CURSOR wie_cursor IS
5408 SELECT distinct
5409 wie.header_id
5410 , wie.message_type
5411 , wie.message
5412 FROM wsm_interface_errors wie
5413 WHERE wie.request_id = requestId
5414 And wie.program_application_id = programApplnId
5415 And wie.program_id = programId;
5416
5417 x_error_message varchar2(2000) := NULL;
5418
5419 Begin
5420 --bugfix 2449452. added these debug stmts
5421
5422 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------');
5423 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERRORS ENCOUNTERED..');
5424 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------');
5425
5426 For wie_record in wie_cursor Loop
5427
5428 If wie_record.message_type = Message_Type_Warning Then
5429
5430 x_error_message := 'Header Id: '||
5431 wie_record.header_id ||
5432 ' has warning.';
5433
5434 Else
5435
5436 x_error_message := 'Header Id: '||
5437 wie_record.header_id ||
5438 ' has error.';
5439
5440 End If;
5441
5442 fnd_file.put_line(fnd_file.log, x_error_message);
5443
5444 x_error_message := substr(
5445 ('('
5446 || wie_record.header_id
5447 || ') '
5448 || wie_record.message
5449 ), 1, 2000);
5450
5451 fnd_file.put_line(fnd_file.log, x_error_message);
5452
5453 x_error_message := NULL;
5454
5455 End Loop; -- Cursor Loop
5456
5457 x_error_message := 'End of Log for Request Id: '||
5458 requestId;
5459
5460 fnd_file.put_line(fnd_file.log, x_error_message);
5461
5462 End writeToLog;
5463 /*EA#IIIP*/
5464 ------------------------------------------------------------------
5465 END WSMPINVL;