[Home] [Help]
PACKAGE BODY: APPS.INV_TRANSACTIONS
Source
1 PACKAGE BODY INV_TRANSACTIONS AS
2 /* $Header: INVVMWAB.pls 120.1 2005/06/11 07:26:47 appldev $ */
3
4
5 /****************************************************************************************/
6 /***********************LINE_INTERFACE_INSERT PROCEDURE*********************************/
7 /****************************************************************************************/
8 /**This procedure Inserts lines related transaction information in the **/
9 /**mtl_transactions_interface table **/
10 /**USED BY: ON_SUBMIT and ON_SERIAL Procedures **/
11 /****************************************************************************************/
12 /****************************************************************************************/
13
14
15 PROCEDURE LINE_INTERFACE_INSERT( p_Inventory_Item_Id IN NUMBER,
16 p_Item_Revision IN VARCHAR2,
17 p_Organization_Id IN NUMBER,
18 p_Transaction_Source_Id IN NUMBER,
19 p_Transaction_Action_Id IN NUMBER,
20 p_From_Subinventory_Code IN VARCHAR2,
21 p_To_Subinventory_Code IN VARCHAR2,
22 p_From_Locator_Id IN NUMBER,
23 p_To_Locator_Id IN NUMBER,
24 p_To_Organization IN NUMBER,
25 p_Transaction_Type_Id IN NUMBER,
26 p_Transaction_Source_Type_Id IN NUMBER,
27 p_Transaction_Quantity IN NUMBER,
28 p_Transaction_UOM IN VARCHAR2,
29 p_Transaction_Date IN DATE,
30 p_Reason_Id IN NUMBER,
31 p_User_Id IN NUMBER,
32 x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
33 x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
34
35 IS
36 l_dist_acct_id NUMBER; --Accounting info
37 l_source_id NUMBER;
38 BEGIN
39
40 IF p_Transaction_Source_Type_Id = 6 THEN
41 l_source_id := p_Transaction_Source_Id;
42 l_dist_acct_id :=NULL;
43
44 ELSIF p_Transaction_Source_Type_Id = 13 THEN
45 l_source_id :=NULL;
46 l_dist_acct_id := p_Transaction_Source_Id;
47 END IF;
48
49
50
51 --INSERTING VALUES INTO MTL_TRANSACTIONS_INTERFACE
52
53 Insert into MTL_TRANSACTIONS_INTERFACE
54 (
55 transaction_interface_id,
56 transaction_header_id,
57 Source_Code,
58 Source_Line_Id,
59 Source_Header_Id,
60 Process_flag,
61 Transaction_Mode,
62 Lock_Flag,
63 Inventory_Item_Id,
64 Organization_id,
65 Subinventory_Code,
66 Transfer_Subinventory,
67 Locator_Id,
68 Transfer_Locator,
69 Transfer_Organization,
70 Transaction_Type_Id,
71 Transaction_Source_Type_Id,
72 --transaction_source_name,
73 Transaction_Action_Id,
74 Transaction_Quantity,
75 Transaction_UOM,
76 Transaction_Date,
77 Last_Update_Date,
78 Last_Updated_By,
79 Creation_Date,
80 Created_By,
81 reason_id,
82 transaction_source_id,
83 revision,
84 distribution_account_id
85 )
86 Values (
87 G_Interface_Id, -- Global Variable set/unset by On_Submit and On_Serial
88 G_Header_Id, -- Same as above
89 1, -- Source_Code,
90 -1, -- Source_Line_Id,
91 -1, -- Source_Header_Id,
92 1, -- Process_flag,
93 1, -- Transaction_Mode,
94 2, -- Lock_Flag,
95 p_Inventory_Item_Id, -- Inventory_Item_Id,
96 p_Organization_Id, -- Organization_Id for
97 p_From_Subinventory_Code, -- Subinventory_Code
98 p_To_Subinventory_Code, -- Transfer_Subinventory
99 p_From_Locator_Id, -- Locator_ID
100 p_To_Locator_Id, -- Transfer_Locator
101 p_To_Organization,
102 p_Transaction_Type_Id, -- Transaction_Type_Id,
103 p_Transaction_Source_Type_Id,-- Transaction_Source_Type_Id,
104 --'OnLine API Testing', -- transaction_source_name
105 -- 12831, -- Transaction_Source_id,is gl_code_combinations.code_combination_id
106 p_Transaction_Action_Id, -- Transaction_Action_Id
107 p_Transaction_Quantity, -- Transaction_Quantity,
108 p_Transaction_UOM, -- Transaction_UOM,
109 p_Transaction_Date, -- Transaction_Date,
110 sysdate, -- Last_Update_Date,
111 p_User_Id, -- Last_Updated_By,
112 sysdate, -- Creation_Date,
113 p_User_Id, -- Created_By,
114 p_Reason_Id, -- reason id,
115 l_source_id, -- Transaction Source Id
116 p_Item_Revision, --Inventory Item Revision
117 l_dist_acct_id -- distribution account
118 );
119
120 x_Message := ' ';
121 x_Status := 'C';
122
123
124 END;
125
126
127
128 /****************************************************************************************/
129 /***********************LOT_INTERFACE_INSERT PROCEDURE *********************************/
130 /****************************************************************************************/
131 /**This procedure Inserts lot related transaction information in the **/
132 /**mtl_lot_numbers_interface table **/
133 /**USED BY: ON_SUBMIT and ON_SERIAL Procedures **/
134 /****************************************************************************************/
135 /****************************************************************************************/
136
137 PROCEDURE LOT_INTERFACE_INSERT(p_Transaction_Quantity IN NUMBER,
138 p_Lot_Number IN VARCHAR2,
139 p_User_Id IN NUMBER,
140 p_serial_number_control_code IN NUMBER)
141
142 IS
143 BEGIN
144 --INSERTING VALUES INTO MTL_TRANSACTION_LOTS_INTERFACE
145
146 IF p_serial_number_control_code <>1 AND p_serial_number_control_code <>6 THEN
147
148 Insert into MTL_TRANSACTION_LOTS_INTERFACE
149 (
150 transaction_interface_id,
151 Source_Code,
152 Source_Line_Id,
153 Process_Flag, --Why is this one a VARCHAR2 whereas the rest are NUMBERs
154 Last_Update_Date,
155 Last_Updated_By,
156 Creation_Date,
157 Created_By,
158 Lot_Number,
159 Transaction_Quantity,
160 Serial_transaction_temp_id
161 )
162 Values (
163 G_Header_Id, --Global Variable (set and unset in ON_SUBMIT and ON_SERIAL procs
164 1,
165 -1,
166 'Y',
167 sysdate,
168 p_User_Id,
169 sysdate,
170 p_User_Id,
171 p_Lot_Number,
172 p_Transaction_Quantity,
173 G_Serial_Id
174 );
175 ELSE
176 Insert into MTL_TRANSACTION_LOTS_INTERFACE
177 (
178 transaction_interface_id,
179 Source_Code,
180 Source_Line_Id,
181 Process_Flag, --Why is this one a VARCHAR2 whereas the rest are NUMBERs
182 Last_Update_Date,
183 Last_Updated_By,
184 Creation_Date,
185 Created_By,
186 Lot_Number,
187 Transaction_Quantity,
188 Serial_transaction_temp_id
189 )
190 Values (
191 G_Header_Id, --Global Variable (set and unset in ON_SUBMIT and ON_SERIAL procs
192 1,
193 -1,
194 'Y',
195 sysdate,
196 p_User_Id,
197 sysdate,
198 p_User_Id,
199 p_Lot_Number,
200 p_Transaction_Quantity,
201 NULL
202 );
203
204 END IF;
205
206 END;
207
208
209
210 /****************************************************************************************/
211 /***********************SERIAL_INTERFACE_INSERT PROCEDURE********************************/
212 /****************************************************************************************/
213 /**This procedure Inserts SN related transaction information in the **/
214 /**mtl_serial_numbers_interface table **/
215 /**USED BY: ON_SUBMIT and ON_SERIAL Procedures **/
216 /****************************************************************************************/
217 /****************************************************************************************/
218
219
220
221 PROCEDURE SERIAL_INTERFACE_INSERT(p_From_Serial IN VARCHAR2,
222 p_To_Serial IN VARCHAR2,
223 p_User_Id IN NUMBER,
224 p_lot_control_code IN NUMBER)
225
226 IS
227 l_header_id NUMBER;
228 BEGIN
229 --INSERTING VALUES INTO MTL_SERIAL_NUMBERS_INTERFACE
230
231 IF p_lot_control_code = 1 THEN l_header_id := G_Interface_Id;
232 ELSE l_header_id := G_Serial_Id;
233 END IF;
234
235 Insert into MTL_SERIAL_NUMBERS_INTERFACE
236 (
237 transaction_interface_id,
238 Source_Code,
239 Source_Line_Id,
240 Process_flag, --Is this the same process_flag as above?
241 Last_Update_Date,
242 Last_Updated_By,
243 Creation_Date,
244 Created_By,
245 Fm_Serial_Number,
246 To_Serial_Number
247 )
248 Values (
249 l_header_id, -- transaction_interface_id
250 1, -- Source_Code,
251 -1, -- Source_Line_Id,
252 1, -- Process_flag,
253 sysdate, -- Last_Update_Date,
254 p_User_Id, -- Last_Updated_By,
255 sysdate, -- Creation_Date,
256 p_User_Id, -- Created_By,
257 p_From_Serial, -- from_Serial_Number,
258 p_To_Serial -- To_Serial_Number
259 );
260
261
262 END SERIAL_INTERFACE_INSERT;
263
264
265
266
267 /****************************************************************************************/
268 /******************************* PROCESS PROCEDURE **************************************/
269 /****************************************************************************************/
270 /**This procedure Actually processes the transaction online using information inserted **/
271 /**In the interface tables using the Mobile transactions form. **/
272 /****************************************************************************************/
273 /****************************************************************************************/
274
275 PROCEDURE PROCESS(x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
276 x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
277 IS
278 l_error_code VARCHAR2(80);
279 l_date_start DATE;
280 BEGIN
281 l_date_start :=sysdate;
282
283 commit;
284 --fnd_global.apps_initialize(1001, 20634, 401);
285
286 IF mtl_online_transaction_pub.process_online
287 (p_transaction_header_id => G_Header_Id,
288 p_timeout => 120,
289 p_error_code => l_error_code,
290 p_error_explanation => x_Message)
291 THEN
292 x_Status := 'C';
293 --dbms_output.put_line('Transaction processed');
294 x_Message := 'Transaction Completed Successfully(start: '||to_char(l_date_start,'hh24:MI:SS')||' end: '||to_char(sysdate,'hh24:MI:SS')||')';
295 ELSE
296 x_Status := 'E';
297 --dbms_output.put_line('Transaction failed:'||l_error_code);
298 --dbms_output.put_line('Transaction failed:'||x_Message);
299 END IF;
300 END PROCESS;
301
302
303
304
305 /****************************************************************************************/
306 /*******************************GET_ACCT_ID PROCEDURE********************************/
307 /****************************************************************************************/
308 /**This procedure returns the correct txn source id. If the source type is account **/
309 /**alias then it will return the p_Transaction_Source_Id_AA. If it is inventory, then **/
310 /**it will return p_Transaction_Source_Id_Inv. This procedure is needed because on the**/
311 /**mobile txn form the transaction source is taken from different places depending on **/
312 /**what the txn source type iswhether the transaction is **/
313 /****************************************************************************************/
314 /****************************************************************************************/
315
316 PROCEDURE GET_ACCT_ID(x_Transaction_Source_Id OUT NOCOPY /* file.sql.39 change */ NUMBER,
317 p_Transaction_Source_Id_AA IN NUMBER,
318 p_Transaction_Source_Id_Inv IN NUMBER,
319 p_Transaction_Source_Type_Id IN NUMBER)
320 IS
321 BEGIN
322
323 IF p_Transaction_Source_Type_Id = 6 THEN
324
325 x_Transaction_Source_Id := p_Transaction_Source_Id_AA;
326
327 ELSE
328
329 x_Transaction_Source_Id := p_Transaction_Source_Id_Inv;
330
331 END IF;
332
333 END GET_ACCT_ID;
334
335
336
337
338
339 --Beginning of attempt with quantity manager**
340
341 PROCEDURE SUBMIT_PRESSED(x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
342 x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
343 p_Transaction_Action_Id IN NUMBER,
344 p_To_Sub IN VARCHAR2,
345 p_To_Loc_Id IN NUMBER)
346 IS
347 BEGIN
348 IF p_Transaction_Action_Id = 2 or p_Transaction_Action_Id = 3 THEN
349 UPDATE MTL_TRANSACTIONS_INTERFACE SET
350 Transfer_Subinventory = p_To_Sub,
351 Transfer_Locator = p_To_Loc_Id
352 WHERE Transaction_Interface_Id = G_Interface_Id;
353 END IF;
354 process(x_Message,
355 x_Status);
356
357
358
359 END SUBMIT_PRESSED;
360
361
362
363
364
365
366
367
368 PROCEDURE UOM_ENTERED(x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
369 x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
370 p_Inventory_Item_Id IN NUMBER,
371 p_Item_Revision IN VARCHAR2,
372 p_Organization_Id IN NUMBER,
373 p_Transaction_Source_Id_AA IN NUMBER,
374 p_Transaction_Source_Id_Inv IN NUMBER,
375 p_Transaction_Action_Id IN NUMBER,
376 p_From_Subinventory_Code IN VARCHAR2,
377 p_To_Subinventory_Code IN VARCHAR2,
378 p_From_Locator_Id IN NUMBER,
379 p_To_Locator_Id IN NUMBER,
380 p_Transfer_Organization IN NUMBER,
381 p_Transaction_Type_Id IN NUMBER,
382 p_Transaction_Source_Type_Id IN NUMBER,
383 p_Transaction_UOM IN VARCHAR2,
384 p_Transaction_Date IN DATE,
385 p_Reason_Id IN NUMBER,
386 p_User_Id IN NUMBER)
387
388 IS
389 l_Transaction_Source_Id NUMBER;
390
391 Type t_ref IS REF CURSOR;
392
393 l_UOMS t_ref;
394
395 v_UOM VARCHAR2(3);
396 v_holder1 VARCHAR2(25);
397 v_holder2 VARCHAR2(50);
398 l_count NUMBER := 0;
399
400 BEGIN
401
402 x_Status := 'C';
403 --First we need to validate UOM (for now not using LOVs)
404
405 INV_TRANSACTION_LOVS.GET_VALID_UOMS(l_UOMS,
406 p_Organization_Id,
407 p_Inventory_Item_Id,
408 p_Transaction_UOM);
409 LOOP
410 FETCH l_UOMS INTO
411 v_UOM,v_holder1,v_holder2;
412 EXIT WHEN l_UOMS%NOTFOUND;
413 l_count := l_count + 1;
414
415
416
417 END LOOP;
418
419 IF l_count <> 1 THEN
420
421 x_Status := 'E';
422 x_Message := 'Not a Valid UOM. Please Try Again...';
423 RETURN;
424 END IF;
425
426
427
428
429 --insert into MTI values that have been entered. Header is generated here.
430 --Qty is entered as 0, to be updated by txn_qty_changed field.
434
431 --in the case of serial controlled items everything is entered into the lines
432 --table except the from sub,from loc, and to sub and to loc these will
433 --be entered in the SERIAL_ENTERED procedure.
435 --Generate Header and store as global variable.
436
437 SELECT mtl_material_transactions_s.NEXTVAL
438 INTO G_Header_Id
439 FROM DUAL;
440
441 G_Interface_Id := G_Header_Id;
442
443 GET_ACCT_ID(l_Transaction_Source_Id,
444 p_Transaction_Source_Id_AA,
445 p_Transaction_Source_Id_Inv,
446 p_Transaction_Source_Type_Id);
447
448 LINE_INTERFACE_INSERT( p_Inventory_Item_Id,
449 p_Item_Revision,
450 p_Organization_Id,
451 l_Transaction_Source_Id,
452 p_Transaction_Action_Id,
453 p_From_Subinventory_Code,
454 p_To_Subinventory_Code,
455 p_From_Locator_Id,
456 p_To_Locator_Id,
457 p_Transfer_Organization,
458 p_Transaction_Type_Id,
459 p_Transaction_Source_Type_Id,
460 0,
461 p_Transaction_UOM,
462 p_Transaction_Date,
463 p_Reason_Id,
464 p_User_Id,
465 x_Message,
466 x_Status);
467
468
469
470
471
472 END UOM_ENTERED;
473
474
475
476
477
478
479
480 PROCEDURE TXN_QTY_CHANGED(p_txn_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
481 p_txn_uom_code IN VARCHAR2,
482 p_prev_txn_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
483 p_serial_number_control_code IN NUMBER,
484 p_lot_control_code IN NUMBER,
485 p_primary_uom_code IN NUMBER,
486 p_organization_id IN NUMBER,
487 p_inventory_item_id IN NUMBER,
488 p_total_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
489 p_total_serial_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
490 p_transaction_action_id IN NUMBER,
491 x_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
492 x_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
493 x_message OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
494
495 IS
496 l_txn_uom_code VARCHAR2(3);
497 l_primary_uom_code VARCHAR2(3);
498 l_txn_qty NUMBER;
499 BEGIN
500
501 l_txn_uom_code := p_txn_uom_code;
502 l_primary_uom_code := p_primary_uom_code;
503
504 INV_QTY_MANAGEMENT.when_txn_qty_entered(p_txn_qty,
505 l_txn_uom_code,
506 NULL,
507 p_prev_txn_qty,
508 p_serial_number_control_code,
509 p_lot_control_code,
510 l_primary_uom_code,
511 p_organization_id,
512 p_inventory_item_id,
513 p_total_lot_qty,
514 p_total_serial_qty,
515 x_done,
516 x_Status,
517 x_message,
518 p_transaction_action_id);
519
520
521 --We want to update the correct row of the MTI table based on the
522 --global header id such that
523
524 IF x_status = 'C' THEN
525
526 IF p_transaction_action_id <>27 THEN
527 l_txn_qty := -p_txn_qty;
528 ELSE
529 l_txn_qty := p_txn_qty;
530 END IF;
531
532 UPDATE MTL_TRANSACTIONS_INTERFACE SET
533 transaction_quantity = l_txn_qty
534 WHERE Transaction_Header_Id = G_Header_Id;
535
536 IF (p_transaction_action_id = 2 or p_transaction_action_id =3
537 or p_transaction_action_id = 1) AND
538 (p_serial_number_control_code <>1 AND p_serial_number_control_code <>6) THEN
539
540 UPDATE MTL_TRANSACTION_LOTS_INTERFACE SET
541 transaction_quantity = p_txn_qty
542 WHERE Transaction_Interface_Id = G_Interface_Id;
543
544 END IF;
545 END IF;
546
547 END TXN_QTY_CHANGED;
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563 PROCEDURE LOT_QTY_CHANGED(p_Lot_Number IN VARCHAR2,
564 p_txn_qty IN NUMBER,
565 p_txn_uom_code IN VARCHAR2,
566 p_inventory_item_id IN NUMBER,
567 p_lot_control_code IN NUMBER,
568 p_serial_control_code IN NUMBER,
569 p_current_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
570 p_prev_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
571 p_total_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
572 p_total_serial_qty IN NUMBER,
573 x_done IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
574 x_lot_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
575 x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
576 x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
577 p_Organization_Id IN NUMBER)
578 IS
579 l_primary_uom_code VARCHAR2(3);
580 BEGIN
581
582 select primary_uom_code into l_primary_uom_code
583 from mtl_system_items where
584 inventory_item_id = p_inventory_item_id and
585 organization_id = p_organization_id;
586 --Call quantity manager
587 INV_QTY_MANAGEMENT.when_lot_qty_entered(p_txn_qty,
588 p_txn_uom_code,
589 l_primary_uom_code,
590 p_inventory_item_id,
591 p_lot_control_code,
592 p_serial_control_code,
593 p_current_lot_qty,
594 p_prev_lot_qty,
595 p_total_lot_qty,
599 x_Status,
596 p_total_serial_qty,
597 x_done,
598 x_lot_done,
600 x_Message);
601
602 --update MTL_TRANSACTION_LOTS_INTERFACE table
603 IF x_Status = 'C' THEN
604
605 UPDATE MTL_TRANSACTION_LOTS_INTERFACE SET
606 transaction_quantity = p_current_lot_qty
607 WHERE Transaction_Interface_Id = G_Interface_Id
608 AND Lot_Number = p_Lot_Number;
609 END IF;
610
611 END LOT_QTY_CHANGED;
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628 PROCEDURE LOT_CHANGED( p_total_serial_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
629 p_serial_number_control_code IN NUMBER,
630 p_Lot_Number IN VARCHAR2,
631 p_User_Id IN NUMBER)
632 IS
633 BEGIN
634
635 --reset serial count for new lot, if item is serial controlled.
636 --this will only happen in the case of a receipt.
637
638 INV_QTY_MANAGEMENT.when_lot_num_entered(p_total_serial_qty,
639 p_serial_number_control_code);
640
641 IF p_serial_number_control_code <>1 AND p_serial_number_control_code <>6 THEN
642
643 SELECT mtl_material_transactions_s.NEXTVAL
644 INTO G_Serial_Id
645 FROM DUAL;
646
647
648 END IF;
649
650 LOT_INTERFACE_INSERT(0,
651 p_Lot_Number,
652 p_User_Id,
653 p_serial_number_control_code);
654 END LOT_CHANGED;
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670 PROCEDURE SERIAL_ENTERED( x_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
671 x_lot_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
672 x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
673 x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
674 p_txn_qty IN NUMBER,
675 p_txn_uom_code IN VARCHAR2,
676 p_Organization_Id IN NUMBER,
677 p_inventory_item_id IN NUMBER,
678 p_current_lot_qty IN NUMBER,
679 p_lot_control_code IN NUMBER,
680 p_serial_control_code IN NUMBER,
681 p_from_serial IN VARCHAR2,
682 p_to_serial IN VARCHAR2,
683 p_total_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
684 p_total_serial_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
685 p_transaction_action_id IN NUMBER,
686 p_User_Id IN NUMBER)
687 IS
688 Type t_ref IS REF CURSOR;
689 l_Serials t_ref;
690 v_serial_number VARCHAR2(30);
691 v_holder1 VARCHAR2(10);
692 v_holder2 NUMBER;
693 v_holder3 VARCHAR2(30);
694 l_prim VARCHAR2(3);
695
696 l_from_sub VARCHAR2(10);
697 l_from_loc NUMBER;
698 l_revision VARCHAR2(3);
699 --After add lot functionality too!!
700 l_count number := 0;
701 BEGIN
702
703 --For receipts line/lot information will not change and therefore this
704 --procedure is a simple matter of just calling the quantity manager, then
705 --inserting the serial into the mtl_serial_numbers_interface table.
706
707 --For Serial triggered Issues and transfers,
708 -- We will enforce that the item stays the same, from sub stays the same,
709 --the from loc stays the same, and also the Lot information remains the same.
710 --Therefore, this entails, simply validating the serial number, calling the
711 --quantity manager and inserting a row into the serial interface table.
712
713 x_Status := 'C';
714
715 INV_TRANSACTION_LOVS.GET_VALID_SERIALS(l_Serials,
716 p_serial_control_code,
717 p_inventory_item_id,
718 p_organization_id,
719 NULL,
720 NULL,
721 NULL,
722 p_transaction_action_id,
723 p_from_serial);
724
725
726 LOOP
727
728 FETCH l_Serials INTO
729 v_serial_number,v_holder1,v_holder2,v_holder3;
730 EXIT WHEN l_Serials%NOTFOUND;
731
732 l_count := l_count +1;
733
734
735 END LOOP;
736
737 IF l_count = 1 THEN
738 x_Status := 'C';
739 ELSE
740 x_Status := 'E';
741 x_Message := 'Not a valid Serial number. Try Again...';
742 END IF;
743
744
745 IF x_Status = 'E' THEN RETURN;
746 ELSE
747 --Call quantity Manager:
748
749 select primary_uom_code into l_prim from mtl_system_items where
750 organization_id = p_Organization_Id
751 and inventory_item_id = p_inventory_item_id;
752
753 INV_QTY_MANAGEMENT.when_srl_num_entered(p_txn_qty,
754 p_txn_uom_code,
755 l_prim,
756 p_inventory_item_id,
757 p_current_lot_qty,
758 p_lot_control_code,
759 p_serial_control_code,
760 p_from_serial,
761 p_to_serial,
762 p_total_lot_qty,
763 p_total_serial_qty,
764 x_done,
765 x_lot_done,
766 x_Status,
767 x_Message,
768 p_transaction_action_id);
769
770 if x_Status = 'C' THEN
771
772 select revision,current_subinventory_code,current_locator_id
773 into l_revision,l_from_sub, l_from_loc from mtl_serial_numbers
774 where current_organization_id = p_organization_id and inventory_item_id =
775 p_inventory_item_id and serial_number = p_from_serial;
776
777 UPDATE MTL_TRANSACTIONS_INTERFACE SET
778 subinventory_code = l_from_sub,
779 revision = l_revision,
780 locator_id = l_from_loc
781 WHERE Transaction_Header_Id = G_Header_Id;
782
783
784 SERIAL_INTERFACE_INSERT(p_from_serial,
785 p_to_serial,
786 p_User_Id,
787 p_lot_control_code);
788 END IF;
789
790 END IF;
791
792 END SERIAL_ENTERED;
793
794
795 END INV_Transactions;