Extracting UMREN(Denominator) and UMREZ(Numerator) values from Oracle tables
AnsweredThere 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.

Best answer
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 floatingpoint 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
GOCREATE 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
GOComment actions 
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

 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?
Please sign in to leave a comment.
Comments
4 comments