12 Replies Latest reply: Jun 23, 2013 2:49 AM by MohammedRiyasAM RSS

    Important Validations for MTL Material Transaction

    MohammedRiyasAM
      Hi All,

      I am creating an OAF page for material transaction -- for material issue and receipt.
      For this I am inserting a record into MTL transaction interface table and the running the transaction manager API in oracle, which populate mtl_material_transactions table.

      But after general insertion i found that the record is getting error out due to oracle validation. The item may be lot or serial controlled.

      Please let me know the validations performed by this program, so that I can handle these before inserting into interface tables.

      Any help/document will be appreciated.
      You can mail me to ecriyas4u@gmail.com as well.

      Regards
      Riyas
        • 1. Re: Important Validations for MTL Material Transaction
          Syed Hasan IT Consultant

          Riyas,

           

          You have to insert the serial number if it is a serial controlled item or it will fail always. Before inserting into the interface table you can validate whether it is serial no is exists or not from the wsh_serial_numbers or mtl_serial_numbers.

           

          You can compare the values from the inventory_itme_id to get the serial numbers.

           

          You can use the below query to validate the records. It might be helpful.

          1. 1stà select * from oe_order_headers_all where header_id=4838351

           

          1. 2nd à select * from wsh_delivery_details where source_header_id=4839902

           

          1. 3rd à select * from wsh_serial_numbers where delivery_detail_id=5088694

           

           

          INSERT INTO mtl_serial_numbers_interface

          (source_code, source_line_id,

          transaction_interface_id,

          last_update_date, last_updated_by,

          creation_date, created_by,

          last_update_login, fm_serial_number,

          to_serial_number, product_code

          --product_transaction_id

          )

          VALUES ('Miscellaneous issue', 7730351,

          71737725,

          --mtl_material_transactions_s.NEXTVAL, --transaction_interface_id

          SYSDATE, --LAST_UPDATE_DATE

          fnd_global.user_id, --LAST_UPDATED_BY

          SYSDATE, --CREATION_DATE

          fnd_global.user_id, --CREATED_BY

          fnd_global.login_id, --LAST_UPDATE_LOGIN

          '168-154-701',

          --FM_SERIAL_NUMBER

          '168-154-701', --TO_SERIAL_NUMBER

          'RCV'

          --PRODUCT_CODE

          --l_rcv_transactions_interface_s

          --v_txn_interface_id --product_transaction_id

          );

          • 2. Re: Important Validations for MTL Material Transaction
            MohammedRiyasAM

            Hi Syed,

             

            Thank you for your reply.

             

            My precise requirement is to change the miscellaneous transaction forms to OAF page.

             

            I have created the page with inputs and on clicking the create button, will call the insert for lot and serial interface tables.

             

            I am generating lot by lot generation API. But once I run the interface manager, the records in mtl_transaction_interface table errors out with Serial number is invalid,  serial number already exist etc as error_explanation.

            So I need to know on what basis should we fetch serial number ? or should we generate serial number ?

            fm_serial_number and to_serial_number.

             

            I am inserting the lot and serial interface values but they are erroring out with one or the other reason for each combination, so i need to know the different validations performed by the interface manager API, so that I can incorporate them.

             

            Regards

            Riyas

            • 3. Re: Important Validations for MTL Material Transaction
              Syed Hasan IT Consultant

              hi,

               

              From the above query select serial_number from wsh_delivery_details corresponding to delivery_detail_id to its source_header_id to fetch the serial number.

               

              Hope it might help you.

               

              Regards,

              Syed

              • 4. Re: Important Validations for MTL Material Transaction
                David Weber

                Hi Riyas,

                 

                ...so i need to know the different validations performed by the interface manager API...

                 

                That is not easy to answer. You need to know all details about the inventory module. And actually you don't want to incorporate all the business rules in your new form/page. However you need to know that in case an item is "Lot Control", then people will have to enter the Lot Number(s) as part of the material transaction. If the item is "Serial Controlled", then users will have to enter the Serial Numbers. There are different ways to enter the Lot and Serial Numbers: either people enter them manually, or they choose from a list of existing Lot/Serial Numbers (depends on the transaction type) or the system auto-generates the numbers.

                 

                If an item is Lot Controlled or Serial Controlled can be found out in the item master:

                select msi.inventory_item_id

                     , msi.lot_control_code

                     , lu1.meaning

                     , msi.serial_number_control_code

                     , lu2.meaning

                  from mtl_system_items_b msi

                     , fnd_lookup_values_vl lu1

                     , fnd_lookup_values_vl lu2

                where msi.lot_control_code = lu1.lookup_code

                   and lu1.lookup_type = 'MTL_LOT_CONTROL'

                   and msi.serial_number_control_code = lu2.lookup_code

                   and lu2.lookup_type = 'MTL_SERIAL_NUMBER'

                ;

                 

                I am curious on your results. How much time it takes to re-implement the form in OAF etc?! Maybe you are willing to share your experience on that when finished?

                 

                regards,

                David.

                • 5. Re: Important Validations for MTL Material Transaction
                  MohammedRiyasAM

                  Thank you David.

                  My Item under testing is lot controlled and serial number code is 5.

                  transaction type is 32 or 42 (miscelleneous issue or reciept)

                  I am inserting a record on transaction interface, lot interface (generating a lot number tru API), serial interface and the calling the interface manager API. but the API error out giving invalid serial number as error explanation. I even gave the serial number from select serial_number from wsh_delivery_details. So there is something else that i need to take care whn i insert a serial number in serial_interface table ?

                   

                  Appreciate your response and knowledge sharing.

                   

                  Regards

                  Riyas

                  • 6. Re: Important Validations for MTL Material Transaction
                    MohammedRiyasAM

                    I am getting the below errors if I provide serial numbers

                     

                    If i give a random serial number

                    1) Serial number &TOKEN does not exist for the given item; and you cannot dynamically create a new one for this item.

                     

                    if i give a serial number from mtl_serial_numbers for the same item-org-subinv combination

                     

                     

                    2) Invalid Serial Number

                     

                    Regards

                    Riyas

                    • 7. Re: Important Validations for MTL Material Transaction
                      Syed Hasan IT Consultant

                      Hi,

                       

                      Generate the Serial Number for the Item You are using or make the used Item current_status to 1(Defined Not Used).

                       

                      My initial advice should be try with the Item which is not serial controlled.

                       

                      If it goes through, Used one Item whoose serial is not used ( you can check with the CurrenT status)

                       

                      Let me know your results.

                       

                      Regards,

                      Syed

                      • 8. Re: Important Validations for MTL Material Transaction
                        MohammedRiyasAM

                        Hi Syed,

                        I will try the same and keep you updated.

                        Meanwhile can you guide me on how to generate the serial number for an Item.

                        Regards

                        Riyas

                        • 9. Re: Important Validations for MTL Material Transaction
                          Syed Hasan IT Consultant

                          Hi,

                           

                          Goto Inventory --> On-Hand Availability --> Generate Serial Numbers

                           

                          You can generate the Serial Numbers of an Item.

                           

                          Please Note :-  Item should be Defined as Serial-Controlled.

                           

                          Regards,

                          Syed

                          • 10. Re: Important Validations for MTL Material Transaction
                            MohammedRiyasAM

                            Hi Syed,

                            Appreciate your response.

                            Serial Number generation is not possible through an API which can be called inside and concurrent program ? I don't have much functional knowledge in Inventory, but I have taken up this challenging development work. So I need somebody's help or some documents to identify the process in creating a miscellaneous transaction screen.

                             

                            My screen looks like below :

                            Select the transaction type -- Item Id -- subinventory -- location (under the item and subinventory, select * from MTL_ITEM_LOCATIONS where item_id = 123 and subinv_code = abcd )

                             

                            Now when u select all these fields and clicking ok.. then insert records to mtl_transaction_interface, lot_int and serial_int.

                            for lot insertion, I create lot_number through an API. Is it correct way of doing ?

                            And how can I generate or get serial number in the same method.

                             

                            Regards

                            Riyas

                            • 11. Re: Important Validations for MTL Material Transaction
                              David Weber

                              Hi,

                               

                              without functional knowledge of the inventory module you might get lost, as you will need to know at least the basics of items, transactions, serial numbers, lot numbers etc. You should have someone with functional knowledge next to while developing this.. Anyway, lets try to go step by step:

                               

                              first of all: you do not have to insert records into the lot numbers interface and the serial numbers interface, in case the item is not under lot or serial control. So I suggest you start with a simple case: an item that is not under lot and serial control.

                               

                              Then you extend it in a way that: when the user enters the item number your program needs to check whether the item is under lot control. If yes, then the person will have to provide the input on the lot numbers and your program inserts the data into the lot numbers interface. Same for serial numbers. You should read the inventory user guide to learn the concept of the lot/serial numbers. You can have a look at following note as well:

                              Lot / Serial Control Setups [ID 112055.1]

                               

                              regards,

                              David.

                              • 12. Re: Important Validations for MTL Material Transaction
                                MohammedRiyasAM

                                Thank you David.

                                 

                                I am able to create issue/receipt for the items which is not lot/serial controlled. But the problem exist for serial controlled items.

                                 

                                Let me put my requirement very clear. Please answer me for the below doubts.

                                 

                                When I do a MISCELLANEOUS ISSUE

                                Q1. what quantity check should i perform (may be on hand qty or something, coz there was some error related to net quantity) ?

                                Q2. when the item is lot controlled : Can i populate lot number through API (currently I am doing the same with inv_lot_api_pub.auto_gen_lot).

                                Q3. When the item is serial controlled, Can i populate serial number through API ?

                                 

                                When I do a MISCELLANEOUS RECEIPT

                                Q1. what quantity check should i perform (may be on hand qty or something, coz there was some error related to net quantity) ?

                                Q2. when the item is lot controlled : Can i populate lot number through API (currently I am doing the same with inv_lot_api_pub.auto_gen_lot).

                                Q3. When the item is serial controlled, Can i populate serial number through API ?

                                 

                                Regards

                                Riyas