How To Track A Serial Number Across Organizations

While we transact serial in organization, after transaction it only appears in the current organization.

The On-hand Quantity -> Serial Number query screen displays the current state of the serial number. It queries the serial number table (MTL_SERIAL_NUMBERS), Since the serial number table will only have one record for the serial number and item combination. MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID hold the current organization of the serial number. This maintains only the last organization to have the serial number.

• If the serial number changes organizations, the serial number will show only in the new organization.
• If a serial number is shipped to a customer, the serial is visible with status 'issued out of stores' in the last organization that owned the serial.

 

XDOException: No corresponding LOB data found

Hello There.. while I was working yesterday on XML template, I came across below error and would like to share with all of you. After the template is uploaded and report is ran the request completed in WARNING. When you check the log you will get message that OPP is failed for the request. When you check the OPP log and you will see following error for the request as below :-

[8/22/14 11:47:42 PM] [OPPServiceThread0] Post-processing request 62447806.
[8/22/14 11:47:42 PM] [1246813:RT62447806] Executing post-processing actions for request 62447806.
[8/22/14 11:47:42 PM] [1246813:RT62447806] Starting XML Publisher post-processing action.
[8/22/14 11:47:42 PM] [1246813:RT62447806]
Template code: C_CSD_PACKLIST
Template app:  C_APPS
Language:      en
Territory:     US
Output type:   PDF
[8/22/14 11:47:43 PM] [1246813:RT62447806] Output file was found but is zero sized - Deleted
[8/22/14 11:47:43 PM] [UNEXPECTED] [1246813:RT62447806] java.lang.reflect.InvocationTargetException
        at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at oracle.apps.xdo.common.xml.XSLT10gR1.invokeNewXSLStylesheet(XSLT10gR1.java:611)
        at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:239)
        at oracle.apps.xdo.common.xml.XSLTWrapper.transform(XSLTWrapper.java:182)
        at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:1044)
        at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:997)
        at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:212)
        at oracle.apps.xdo.template.FOProcessor.createFO(FOProcessor.java:1665)
        at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:975)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5936)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3459)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3548)
        at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:302)
        at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:176)
Caused by: java.io.IOException: Root problem: oracle.apps.xdo.XDOException:No corresponding LOB data found :SELECT L.FILE_DATA

Generating Serial Numbers

While setting up the serial control of an item If you specified predefined as the serial number control type for an item, you must generate the serial numbers for the item. If you specified entry at inventory receipt or at sales order issue, you can optionally predefine serial numbers for the item. 

The process of generating serial numbers does not assign serial numbers to units in inventory, but reserves specific serial numbers for an item, for later use. When you run the serial generation program, if you have the same serial prefix number across organizations, the concurrent program searches across the organizations, and generates serial numbers using the highest start number across the organizations.

• Navigate to the Generate Serial Numbers window / All Reports window / Go to View –> Request –> Single Request –> Serial Generation.

Setting Up Serial Number Control in Oracle Inventory

Oracle Inventory provides robust serial control for your organization. Before you start using serial number control, you must set the item attributes and organization parameters:

• Setting Up Serial Number Control for an item :-
  We establish serial number control for an inventory item when you define it on the Item Master. If we don’t want to control serial attribute at Master Organization level we can set at child / transaction organization. You can choose from No control, Predefined, At inventory receipt, or At sales order issue.
  • No Control – If set to ‘No Control’, item will be termed as Non-Serial controlled
  • Predefined – If set to ‘Predefined’, serial number will be set for item before its used. In this case we generate the serial number first and then use it.
  • At Inventory Receipt – If set to ‘At Inventory Receipt’, serial number will be generated when the device is received into that organization. This is mostly used in Service/Repair business.
  • At Sales Order Issue – If set to ‘At Sales Order Issue’, serial number will be generated when we ship device for the organization. Serial Number will be generate based on the prefix and suffix setup.

Serial Number Restrictions for Transferring Between Organizations

Oracle Inventory places certain restrictions on serial controlled items that we transfer between organizations where the serial control in the source and destination organizations is different. The following tables explain the expected results that occurs in source and destination organization.

Direct Inter organization Shipment

Source Org / Destination Org	At Receipt or Predefined	At Sales Order Issue	None
At Receipt or Predefined	Allowed, serial moved to destination	Allowed, serial number created as sales order issue	Allowed, serial entered at issue and set to issued from stores in source organization. No serial received in destination organization.
At Sales Order Issue	Not allowed	Allowed, serial number dynamic entered at issue	Allowed, no serial created or moved.
None	Not allowed	Allowed, serial number created at sales order issue	Allowed, no serial created or moved

Understanding MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE

Oracle Inventory provides complete serial number support for inventory transactions. We can enable serial number control for specific items in your inventory organization. For items under serial number control, we assign unique serial numbers to individual units and thereafter reference the same serial numbers each time you perform material transactions

There are various level of serialization control Oracle Inventory provides. If we query mtl_system_items_b.serial_number_control_code it provides items serialization status. Here they are…

Serialization Code	Meaning
1	No Serial Number Control
2	Predefined Serial Numbers
5	Dynamic Entry at Inventory Receipt
6	Dynamic Entry at Sales Order Issue.

Understanding MTL_SERIAL_NUMBERS.CURRENT_STATUS

So here I am after a long time with a very short post..don’t mind !!!

We often came across using serial number in oracle applications though its shipping the serial for a sales order, a repair order or receiving a serial into inventory. There would be many cases where we have a device in hand with a serial number ready to be shipping but system didn’t allow its saying some error message.. I am not going deep in what all error message comes but the bottom line is where is the serial and what is its current status.

If we query mtl_serial_numbers.current_status it gives back some number which represents its actual status. So lets find out what those numbers represent.

Serial Status	Meaning
1	Defined but not used
3	Resides in stores
4	Issued out of stores
5	Resides in intransit
7	Resides in receiving
8	Resides in WIP