Extracting UMREN(Denominator) and UMREZ(Numerator) values from Oracle tables

Answered

Sailaja Gujjeli

• August 05, 2021 06:34

There are Fields in Material master to Store the Units of Measure

Below are the Fields and Transformation being followed from Oracle to SAP.

 

SAP Field Name	SAP Table	Transformation	Oracle Field Name	Oracle Table
MEINS (Base Unit of Measure)	MARA	copy	PRIMARY_UOM_CODE	MTL_SYSTEM_ITEMS_B
MEINH (Alternate Unit of Measure )	MARM	copy	SECONDARY_UOM_CODE	MTL_SYSTEM_ITEMS_B
UMREZ (Numerator for Conversion)	MARM
UMREN(Denominator for conversion)	MARM

 

Here UMREZ and UMREN are the fields used as factors to Convert the Alternate Unit of Measure(MEINH) to the Base Unit of Measure(MEINS)

 

As Oracle maintains Conversion factor in a single field Which can be decimal number(Ex: 2.1564785444)

But SAP maintains Conversion factors in two fields as mentioned above (UMREZ & UMREN), and these 2 fields only accepts the Integer/Whole number as per SAP configuration.

 

 

DXC Oracle team is proposing to use the following Standard function of Oracle to get the Conversion Factors.

 

 

As we can see in the above figure they are rounding off the output to the nearest integer to make it compatible to SAP.

But the Problem with the above approach is

• Whenever the function returns the value less than 0.5 then as per the above script the nearest integer would be 0, Which cannot be denominator.(Please see first record in the below picture  Column H is the denominator)
• Please refer 2^nd Row from below picture, as we are rounding off 0.5 to 1 as denominator , In case large quantity of Purchases at transactional level the impact will be huge.

 

 

 

So, We are looking for an Alternate way to get the accurate Conversion Factors compatible to SAP from Oracle Source.

0

• Best answer

  

  Ben Bauer

  • August 30, 2021 19:54
  • Edited

  Hi Gujjeli,

  This could be quite an involved scenario if the UMREZ & UMREN integer values need to be generated in the Oracle SQL query. I'm guessing it would be necessary to write a custom function in Oracle. I'm not familiar with that, but perhaps you can use some logic from some SQL Server functions I once wrote to convert a floating-point number into a numerator and denominator.

  CREATE FUNCTION GetDenominatorFromFloat
  (
  @FloatingPoint FLOAT
  )
  RETURNS INT
  AS
  BEGIN
  --Convert to Fraction
  DECLARE @Denominator INT = POWER(10, LEN(@FloatingPoint) - 1)
  DECLARE @Numerator INT = @FloatingPoint * @Denominator
  
  --Calculate GCF
  DECLARE @Dividend INT = @Denominator
  DECLARE @Divisor INT = @Numerator
  DECLARE @Remainder INT = -1
  
  WHILE @Remainder != 0
  BEGIN
  SET @Remainder = @Dividend % @Divisor
  SET @Dividend = @Divisor
  SET @Divisor = @Remainder
  END
  
  --Reduce Fraction
  SET @Denominator /= @Dividend
  
  --Return
  RETURN @Denominator
  
  END
  GO

  CREATE FUNCTION GetNumeratorFromFloat
  (
  @FloatingPoint FLOAT
  )
  RETURNS INT
  AS
  BEGIN
  --Convert to Fraction
  DECLARE @Denominator INT = POWER(10, LEN(@FloatingPoint) - 1)
  DECLARE @Numerator INT = @FloatingPoint * @Denominator
  
  --Calculate GCF
  DECLARE @Dividend INT = @Denominator
  DECLARE @Divisor INT = @Numerator
  DECLARE @Remainder INT = -1
  
  WHILE @Remainder != 0
  BEGIN
  SET @Remainder = @Dividend % @Divisor
  SET @Dividend = @Divisor
  SET @Divisor = @Remainder
  END
  
  --Reduce Fraction
  SET @Numerator /= @Dividend
  
  --Return
  RETURN @Numerator
  
  END
  GO

  0
• 

  Joseph Flesche

  • August 05, 2021 09:57

  From reading your post, it appears that there are pictures. I tried viewing this post on multiple devices, but there are no pictures.

  0
• 

  Sailaja Gujjeli

  • August 05, 2021 12:02

  Sorry images are not being displayed 

  The code proposed by the client team for extraction 

  select  inventory_item_id ,conversion_rate      std_from_rate,

               uom_class            std_from_class

         from  apps.mtl_uom_conversions

         where inventory_item_id in (1003130, 0)

         and   uom_code = 'BOX'--from_unit

         and   nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)

         order by inventory_item_id desc; 

   

  As we can see in the above code they are rounding off the output to the nearest integer to make it compatible to SAP.

  But the Problem with the above approach is

  • Whenever the function returns the value less than 0.5 then as per the above script the nearest integer would be 0, Which cannot be denominator.
  • Please refer 2^nd Row from below picture, as we are rounding off 0.5 to 1 as denominator , In case large quantity of Purchases at transactional level the impact will be huge.

  Hope this is more clear .

   

  Thanks,

  Sailaja

  0
• 

  Joseph Flesche

  • August 05, 2021 15:14

  • Please refer 2^nd Row from below picture, as we are rounding off 0.5 to 1 as denominator , In case large quantity of Purchases at transactional level the impact will be huge.

  Screenshots have not come through again.

  I'm not familiar with Oracle SQL syntax, but are you aliasing these fields?

  

  0

Please sign in to leave a comment.