API for UOM Conversion

A unit of measure conversion is a mathematical relationship between two different units of measure. For example, 12 Each = 1 Dozen. If you want to transact items in units of measure belonging to classes other than their primary UOM class, you must define conversions between the base units of measure in different UOM classes.

Method 1:

DECLARE
lv_conv NUMBER; --converted quantity
BEGIN
--
lv_conv :=  inv_convert.inv_um_convert(
      item_id           => 494031,
      organization_id   => 9932,
      PRECISION         => 5,
      from_quantity     => 40,  -- source quantity
      from_unit         => 'DZ',  
      to_unit           => 'EA',
      from_name         => NULL,
      to_name           => NULL);
--
      IF lv_conv = -99999
      THEN
      --
         raise_application_error (
            -20101,
               'Error!! No UOM Conversion rule exists');
      --
      END IF;
dbms_output.put_line('Converted Quantity lv_conv: '|| lv_conv);	  
END;
/

Function would return -99999 if no UOM conversion defined for given parameters in UOM conversion setup.

Method 2:
There is one other overloaded function which has only 3 parameters item_id, from uom and to uom

Function Signature:

FUNCTION inv_um_convert(
    p_item_id       IN NUMBER,
    p_from_uom_code IN VARCHAR2,
    p_to_uom_code   IN VARCHAR2)
  RETURN NUMBER;

Example:

  SELECT msi.segment1,
    msi.primary_uom_code,
    'DZ'                                                                           AS second_uom,
    inv_convert.inv_um_convert (msi.inventory_item_id, 'DZ', msi.primary_uom_code) AS conversion
  FROM mtl_system_items_b msi
  WHERE msi.segment1 = 'MANGOES';

More about UOM Conversion: http://docs.oracle.com/cd/E18727_01/doc.121/e13450/T291651T291797.htm