Bookmark and Share Subscribe You may face visibility problem if http://docs.google.com is blocked on your network

Tuesday, May 27, 2008

Serial Number Generation in Oracle Apps

Serial generation in Oracle Apps is based on the parameters defined for an Organization:

  • Serial Uniqueness
    • Within organization: Enforce unique serial numbers within the current organization
    • Within inventory model and items: Enforce unique serial numbers for inventory items
    • Across organizations: Enforce unique serial numbers throughout all organizations
  • Serial Generation
    • At organization level: Define the starting prefix and serial number information for items using the information you enter in the following fields of this window
    • At item level: Define the starting serial number prefix and the starting serial number when you define the item
  • Prefix
  • Starting Serial Number Parameter
  • Allocate Serial Number: Indicate whether the system will suggest serial numbers as part of the move order line allocating process. If you do not select this option, you must manually enter the serial numbers in order to transact the move order.

Case-1 In organization parameters Serial Generation='At organization level' and Uniqueness='Within inventory model and items'

INVENTORY> On-hand Availability> Generate Serial Number

Select Organization and generate serial number for different items.

The serial number value gets incremented for each serial generation irrespective of item

e.g.

  1. INVENTORY> Setups> Organizations> Parameters
  2. Define ‘Prefix= MFG’ and ‘Starting Serial Number Parameter= 0001’ in MFG organization parameters
  3. Define ‘Prefix= SER’ and ‘Starting Serial Number Parameter= 0001’ in SER organization parameters
  4. Select MFG organization
  5. INVENTORY> On-hand Availability> Generate Serial Number
  6. Run Serial Generation program for ABC with quantity=5
  7. Run Serial Generation program for XYZ with quantity=5
  8. Serial number ‘MFG0001 to MFG0005’ will be assigned to ABC
  9. Serial number ‘MFG0006 to MFG0010’ will be assigned to XYZ
  10. Select SER organization
  11. INVENTORY> On-hand Availability> Generate Serial Number
  12. Run Serial Generation program for ABC with quantity=5
  13. Run Serial Generation program for XYZ with quantity=5
  14. Serial number ‘SER0001 to SER0005’ will be assigned to ABC
  15. Serial number ‘SER0006 to SER0010’ will be assigned to XYZ

Case-2 In organization parameters Serial Generation=’At Item Level’ and Uniqueness='Within inventory model and items'

  1. INVENTORY> Items> Organization items Query the item and go to Inventory tab
  2. Define Serial ‘Prefix= Item-MFG’ and ‘Starting Number= 0001’ for ABC and XYZ item in MFG organization
  3. Define Serial ‘Prefix= Item-SER’ and ‘Starting Number= 0001’ for ABC and XYZ item in SER organization
  4. Select MFG organization
  5. INVENTORY> On-hand Availability> Generate Serial Number
  6. Run Serial Generation program for ABC with quantity=5
  7. Run Serial Generation program for XYZ with quantity=5
  8. Serial number ‘Item-MFG0001 to Item-MFG0005’ will be assigned to ABC
  9. Serial number ‘Item-MFG0001 to Item-MFG0005’ will be assigned to XYZ
  10. Select SER organization
  11. INVENTORY> On-hand Availability> Generate Serial Number
  12. Run Serial Generation program for ABC with quantity=5
  13. Run Serial Generation program for XYZ with quantity=5
  14. Serial number ‘Item-SER0001 to Item-SER0005’ will be assigned to ABC
  15. Serial number ‘Item-SER0001 to Item-SER0005’ will be assigned to XYZ

12 comments:

  1. hi
    pls confirm me one dummy site to implement finance modules and SCM

    ReplyDelete
  2. Hi,

    Is there a way to generate the serial numbers using custom logic for serial control as "Predefined".

    Regards,
    Imran.

    ReplyDelete
  3. Hi Imran,
    There is a way, you are thinking on a right track.
    Unfortunately, I don't have details for that. But you will need to find some API for that.
    If I get something on this I will post the update.

    Thanks
    Devendra

    ReplyDelete
  4. Hi,
    Can you tell me the procedure to update Item Serial Number. Is it possible to update. If yes, explain. Ex: the user entered wrong serial number while receiving Item. Later the User realized that was mistake. So now how to correct the serial number with the correct serial number.

    Thanks,
    Sanju

    ReplyDelete
  5. Hi Sanju,
    Updating serial number records is not the suggested solution, if it is on-off case only and user agrees then, ask him to return that serial number and do receipt again with correct serial number.

    Let me know you still want a datafix.

    Thanks
    Devendra Gulve

    ReplyDelete
    Replies
    1. Do you have a datafix for this? We need to change serial numbers (so the original ones can be shipped out) but can NOT do the RMA route. Do you know if the API (INV_MATERIAL_STATUS_PUB.UPDATE_API) (How to update Serial Status (status_id) in mtl_serial_numbers table [ID 889972.1]can work for this? The update does NOT work from the screen referenced in that note so I presume it won't work for the API as well.

      Delete
  6. Dear Devendra,

    i am working for telecom client,here customer configure the item 'At receipt level'(serial control).

    here customer need to launch the new item as scratch cards and need to configutre in ERP.

    so it may huge quantity like in millions of receiving against PO to inventory and issuing against SO.

    But my customer all transactions like Receiving,Misc receipt ,issue,SO issue ,move order transfer and return order transaction is doing front for both range and individual serials.

    Can you please suggest me on this is there any alternative solution rather than the front end load .

    Regards
    viswanath.

    ReplyDelete
  7. Is client going to generate serial numbers in Oracle EBS ?
    I suppose the serial number for product like Scratch card would be generated in some separate system using a complex algorithm and high security.

    Thanks
    Devendra

    ReplyDelete
  8. Hi Devendra,

    We use EBS for ERP, and we also have many small custom apps that we use during manufacturing, to configure our product. Today, we generate serial numbers in the custom apps. I'm trying to determine if it would be more proper to let EBS generate the serial numbers, and have our custom apps read them out. We need to program them into the units electronically.

    What have you found to be the most common practice?

    thanks,
    Robb

    ReplyDelete
  9. Hi Robb,
    It completely depend on the business. Say you are manufacturing engines / computer hardware / telephone units then the serial number generation is pretty simple and can be done on EBS and then communicate it to other application.

    But for serial number of software licenses or pre-paid cards is very critically and need complex algorithm which shall be done in specialized applications and then bring it in EBS.

    So please decide based on the product and business need.
    Hope this will help you in taking decision.

    Thanks
    Devendra.

    ReplyDelete
  10. Hi Devendra,

    We are using EBS R12, and we are also using a custom app to maintain SNs for all product that is not currently setup as Dynamic in EBS.
    We are a contract manufacturer and many of our customers use Prefix logic of date codes (example: WWYYxxxx or MMYYxxxx).
    Are you familiar with a way for EBS to handle dynamic prefix data such as a date code or a regular expression?

    Thanks,
    -Ken

    ReplyDelete
  11. declare
    CURSOR c IS SELECT rownum from employees;
    cc c%rowtype;
    begin
    OPEN c;
    loop
    FETCH c INTO :ser2;
    next_record;
    exit when :system.last_record = 'TRUE';
    end loop;
    close c ;
    first_record;
    end;

    ReplyDelete

Please add your valuable feedback / comments

Declaimer:

This blog is purely personal and the thoughts expressed here represent only me. The purpose of this blog is to share information and knowledge about Oracle's product which I have come across with my exposure to the product, practice and observations. The blog has been created keeping only one intention of sharing knowledge and for learning purpose. The blog has been created solely as a educational, for storing portions of the vast Oracle knowledge world. Oracle EBS is an Oracle Corp. product and you should contact Oracle directly for any specific fact or issue.

*NOTE: Few articles on this blog are not completely prepared by me, content is edited and complied after referring various sites to make visitor's job easy