Converting Units of Measurement (UOM) in OM

I’ve been working with a client recently where there were a few specific problems that needed to be resolved – the best kind of consultancy project as far as I am concerned.

One of the problems that they were facing was that on a custom report, if the units of measurement were different between the sales order and the item definition, then the units were not being converted properly.  The report produces custom invoices for the client, so this is a key requirement – recently they invoiced someone for 18 million euros by mistake since the calculation was completely wrong!  For example, someone orders 17 rolls of paper at £42 per roll, which is entered into standard OM.  The item is actually stored in the system in metres – there are 30 metres on a roll.  Unfortunately, there is no direct conversion between a roll and a metre – the conversion is rolls into millimetres, millimetres into metres.  A quirk of bad programming meant that instead of being invoiced for 17 rolls at £42 a roll, the end-client was invoiced for 510 rolls at £42 (17*30 metres = 510).  So only an extra £20,000 or so!  And that was just one order line on one order…

The source of the program was that the original developer wrote their own mechanism for converting between units of measurement – if there was no direct conversion, then the code returned a ratio of 1:1.  So converting rolls to metres meant that the "default" value of 1:1 was calculated, resulting in our 510 metres.

I spent quite some time tracking the problem down – the main problem facing me was that I thought "there must be a standard API for converting units of measurement".  And there is.  The problem is that I couldn’t find it anywhere I looked – a few reference manuals, Google, Metalink all turned up a blank.  So as a last resort, I opened SQL*Developer and searched for packages containing "%UOM%" and found the right one – PO_UOM_S which has a function to convert between units of measurement for a given inventory item.

So, if you ever need to manually convert between units of measurement, hopefully I’ve saved you some time and effort by listing the package here.

1 thought on “Converting Units of Measurement (UOM) in OM”

  1. Thanks. This was helpful.

    The function is also used in the standard report POXRCIPS which reports on variances between purchase requisition and what was invoiced by vendor. The specific use is:

    po_uom_s.po_uom_convert_p pol.unit_meas_lookup_code, rct.unit_of_measure, pol.item_id)

    For more on that report, open it up. The code is in query “Q_Pos” and the report is fairly straigthforward to read.

Comments are closed.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close