Conversiones JDE

La siguiente funcion fue creada con el objetivo de replicar la funcion estandard de JDE para convertir entre distintas unidades de medidas.

Plataforma: DB2 5.4 for iSeries

Tablas involucradas:

F41002: Item conversion

F41003: Unit of measure standard conversion

                                                                     
                                             
drop function db2dtamart.JDEConvert;

CREATE function db2dtamart.JDEConvert(Item decimal(10,0), FromUOM varchar(3), ToUOM varchar(3), Units float)
   returns decimal(19,4)
   language sql
   not deterministic
   reads sql data
begin atomic

   declare primaryUOM varchar(3);
   declare alternativeUOM varchar(3);
   declare PrimaryUnits float;
   declare ToUnits float;
   declare denominador float;
   set denominador = 10000000;


   /* This is for the sake of speed mainly */
   if Units = 0 then
      return 0;
   end if;
   if rtrim(FromUOM) = rtrim(ToUOM) then
      return Units;
   end if;
   if FromUOM is null or ToUOM is null 
   or ltrim(FromUOM) = '' or ltrim(ToUOM) = '' then
		return 0;
   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/denominador,7) fconv, round(f.umcnv1/denominador,7) fcnv1,
             t.umum tum, t.umrum trum, round(t.umconv/denominador,7) tconv, round(t.umcnv1/denominador,7) tcnv1
        from pd812dta.f41002 f join pd812dta.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)
   do
       /* This could be slightly simplified, but this way is easier to read I think. */
       if fum = FromUOM then
          set PrimaryUnits = Units * fcnv1;
       else
          set PrimaryUnits = (Units / fconv) * fcnv1;
       end if;

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

       return ToUnits;

       /*return ToUnits;*/

   end for;


   for cfF41003 as
      select f.ucum fum, f.ucrum frum, round(f.ucconv/denominador,7) fconv, 1 / round( f.ucconv /denominador,7) fcnv1
        from pd812dta.f41003 f
       where f.ucum in ( FromUOM, ToUOM)
         and f.ucrum in ( FromUOM, ToUOM)
   do
       /* This could be slightly simplified, but this way is easier to read I think. */
       if fum = toUOM then
          set ToUnits = Units * fcnv1;
       else
          set ToUnits = Units * fconv;
       end if;


       return ToUnits;
   end for;

   /* si llego hasta aca es que o pudo convertir*/


   /*********************************************/
   /* Intentamos otra conversion                */
   /*********************************************/	
   set alternativeUOM = '';
   set alternativeUOM = ( 	select 	distinct ucRUM 
							from 	pd812dta.f41003 
							where	ucUM = FromUOM
							and		ucRUM in (	select  distinct umUM 
												from 	pd812dta.f41002 
												where 	umITM = Item) 
							);
	if alternativeUOM is not null and alternativeUOM <> ''then
		set Units = ( select db2dtamart.JDEConvert(Item, FromUOM, alternativeUOM, Units) from sysibm.sysdummy1);
		set Units = ( select db2dtamart.JDEConvert(Item, alternativeUOM, ToUOM, Units) from sysibm.sysdummy1);
		return Units;
	end if;
	
	set alternativeUOM = '';
	set alternativeUOM = ( 	select 	distinct ucUM 
							from 	pd812dta.f41003 
							where	ucRUM = FromUOM
							and		ucUM in (	select  distinct umUM 
												from 	pd812dta.f41002 
												where 	umITM = Item
												) 
							);
	if alternativeUOM is not null and alternativeUOM <> '' then
		set Units = ( select db2dtamart.JDEConvert(Item, FromUOM, alternativeUOM, Units) from sysibm.sysdummy1);
		set Units = ( select db2dtamart.JDEConvert(Item, alternativeUOM, ToUOM, Units) from sysibm.sysdummy1);
		return Units;
	end if;
	
	set alternativeUOM = '';
	set alternativeUOM = 	(	select  distinct umUM 
								from 	pd812dta.f41002 
								where 	umITM = Item
								and		umUM in ( select  distinct ucUM from pd812dta.f41003 where ucRUM = toUOM )
								and		umUM not in (FromUOM)
							);
							
	if alternativeUOM is not null and alternativeUOM <> '' then
		set Units = ( select db2dtamart.JDEConvert(Item, FromUOM, alternativeUOM, Units) from sysibm.sysdummy1);
		set Units = ( select db2dtamart.JDEConvert(Item, alternativeUOM, ToUOM, Units) from sysibm.sysdummy1);
		return Units;
	end if;
   return null;
end;

COMMENT ON SPECIFIC FUNCTION DB2DTAMART.JDEConvert 
	IS ' V1.4 - Convierte cantidades entre distintas unidades de medida' ;

Algunos ejemplos de como funciona:

El ejemplo se basa en el item: 1158607 (para el fin de este ejemplo no nos interesa que es).

Unidad de media primaria del item

select imUOM1  from pd812dta.f4101 where imITM = 1158607

Resultado: CU

Conversiones definidas para este item en particular....

select *from pd812dta.f41002 where umITM = 1158607

Ahora probamos conversiones simples... solo definidas en el F41002... unidad de medida desde = UM y hacia RUM...

select umMCU, umITM, umUM, umRUM, umUSTR, umCONV, umCNV1, db2dtamart.JDEConvert(umITM, umUM, umRUM, 10) as fromUMToRUM
from pd812dta.f41002 where umITM = 1158607

Ahora probamo la inversa... unidad de medida desde = RUM y hacia UM...

select umMCU, umITM, umUM, umRUM, umUSTR, umCONV, umCNV1, db2dtamart.JDEConvert(umITM, umUM, umRUM, 10) as fromRUMToUM
from pd812dta.f41002 where umITM = 1158607

Otro Ejemplo convertimos 10 CU a 3C

select 
1158607 as umITM, 
'CU'  as umUM, 
'3C' as umRUM, 
db2dtamart.JDEConvert(1158607, 'CU', '3C', 10) as fromUMToRUM 
from  sysibm.sysdummy1 

Mas ejemplos de conversiones definidas en la tabla F41002...

select 1158607 as umITM, 'CU'  as umUM, '3C' as umRUM, db2dtamart.JDEConvert(1158607, 'CU', '3C', 10) as fromUMToRUM from  sysibm.sysdummy1 union
select 1158607 as umITM, '3C'  as umUM, 'CU' as umRUM, db2dtamart.JDEConvert(1158607, '3C', 'CU', 10) as fromUMToRUM from  sysibm.sysdummy1 union
select 1158607 as umITM, 'CA'  as umUM, '3C' as umRUM, db2dtamart.JDEConvert(1158607, 'CA', '3C', 10) as fromUMToRUM from  sysibm.sysdummy1 union
select 1158607 as umITM, '3C'  as umUM, 'CA' as umRUM, db2dtamart.JDEConvert(1158607, '3C', 'CA', 10) as fromUMToRUM from  sysibm.sysdummy1 union
select 1158607 as umITM, 'PL'  as umUM, '3C' as umRUM, db2dtamart.JDEConvert(1158607, 'PL', '3C', 10) as fromUMToRUM from  sysibm.sysdummy1 union
select 1158607 as umITM, '3C'  as umUM, 'PL' as umRUM, db2dtamart.JDEConvert(1158607, '3C', 'PL', 10) as fromUMToRUM from  sysibm.sysdummy1

Ahora vamos a utilizar conversiones que se encuentran definidas en la tabla F41003...

select ucUM, ucRUM, ucCONV from pd812dta.f41003 where 'KG' in (ucUM, uCRUM)

Por ultimo mostrarmos conversiones donde intervienen las dos tablas:

select 1158607 as umITM, 'KG'  as umUM, 'CU' as umRUM, db2dtamart.JDEConvert(1158607, 'KG', 'CU', 10) as fromUMToRUM from  sysibm.sysdummy1 union
select 1158607 as umITM, 'CU'  as umUM, 'KG' as umRUM, db2dtamart.JDEConvert(1158607, 'CU', 'KG', 10) as fromUMToRUM from  sysibm.sysdummy1 union
select 1158607 as umITM, 'GM'  as umUM, 'CU' as umRUM, db2dtamart.JDEConvert(1158607, 'GM', 'CU', 10) as fromUMToRUM from  sysibm.sysdummy1 union
select 1158607 as umITM, 'CU'  as umUM, 'GM' as umRUM, db2dtamart.JDEConvert(1158607, 'CU', 'GM', 10) as fromUMToRUM from  sysibm.sysdummy1