As long as we are discussing JD Edwards running on DB/2. Here’s another potentially useful function.  This one converts a value from one unit of measure to another based on the F41002 table.  It does this by converting the FromUOM to the primary UOM and thence to the ToUOM.

drop function myuser.JDEConvert!

CREATE function myuser.JDEConvert(Item decimal(10,0), FromUOM varchar(3),
                                  ToUOM varchar(3), Units decimal(15,7))
   returns decimal(15,7)
   language sql
   not deterministic
   reads sql data
begin atomic

   declare PrimaryUnits decimal(15,7);
   declare ToUnits decimal(15,7);

   /*declare exit handler for sqlexception
      return null;*/

   /* This is for the sake of speed mainly */
   if Units = 0 then
      return 0;
   end if;
   if FromUOM = ToUOM then
      return Units;
   end if;

   /* -----------------------------------------------------------------
      we know we don't have any conversion directly between the from unit
      and the to unit, so what we do is look for a conversion like so...
      FromUOM to PrimaryUOM to ToUOM
      ----------------------------------------------------------------- */
   for cfF41002 as
      select f.umum fum, f.umrum frum, round(f.umconv/10000000,7) fconv, round(f.umcnv1/10000000,7) fcnv1,
             t.umum tum, t.umrum trum, round(t.umconv/10000000,7) tconv, round(t.umcnv1/10000000,7) tcnv1
        from f41002 f join F41002 t on t.umitm = f.umitm
       where f.umitm = Item
         and FromUOM in (f.umum, f.umrum)
         and ToUOM in (t.umum, t.umrum)
       /* This could be slightly simplified, but this way is easier to read I think. */
       if fum = FromUOM then
          set PrimaryUnits = Units * fcnv1;
          set PrimaryUnits = (Units / fconv) * fcnv1;
       end if;

       if tum = ToUOM then
          set ToUnits = PrimaryUnits / tcnv1;
          set ToUnits = (PrimaryUnits * tconv) / tcnv1;
       end if;

       return ToUnits;

       /*return ToUnits;*/

   end for;

   return null;


select imsrtx,
       '1' || imuom1 || ' =', JDEConvert(imitm, imuom1, 'LB', 1) || ' lbs.',
       '1' || imuom2 || ' =', JDEConvert(imitm, imuom2, 'LB', 1) || ' lbs.'
  from f4101

(In the past, we had a function that found the requested conversion by recursively searching direct conversions till it found a path between the From UOM and the ToUOM. We all know recursion is more fun and usually requires less code. In this case the recursive method was less efficient and created more problems than it solved, so we sadly had to rewrite to this method, which we are just as proud of.)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>