While inserting Item related data into the MTL_SYSTEM_ITEMS_INTERFACE interface table, we need to validate the Unit of Measure field. In order to validate the Unit of Measure (UOM) already exists in the system, check if the UOM exists in the MTL_UNITS_OF_MEASURE table.

DECLARE

l_unit_of_measure mtl_units_of_measure.unit_of_measure%TYPE; 
p_uom_code        mtl_units_of_measure.uom_code%TYPE;

BEGIN

p_uom_code := 'EA';

   SELECT unit_of_measure
     INTO l_unit_of_measure
     FROM mtl_units_of_measure
    WHERE uom_code = p_uom_code;
    
    DBMS_OUTPUT.put_line ('Unit of Measure is: ' || l_unit_of_measure);
    
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unit of Measure does not exist: ' || SQLERRM);
END;