AMDP to convert quantities based on MARM

As a developer with over 20 years of experience, this is my very first blog on blogs.sap.com. But don’t let that fool you: i have been a visitor of the old SCN forum, posted numerous times  (not with this S-id but my old ones) and oh how i miss the old coffee corner…

During those years i have seen all sorts of new solutions/technologies such as DCOM Component Connector, ITS, Webdynpro and so on being introduced by SAP. I always tried to keep up with these new developments.

And now we live in the era of Hana, the era of push-the-code-down. Or if you like: the era of code-to -data.

On my current position i participated in 2015/2016 in probably one of the first Hana DB migrations in The Netherlands. And my initial thought was: nice i got me some new goodies to play with. But reality was different. Core Data Services and ABAP Managed Database Procedures: it scared the hell out of me. I wasn’t feeling very comfortable to adopt these new toys offered by SAP. So it took me almost a year to convince myself to start exploring the possibilites i was given.

Now it’s 2018 and in the past year i have “mastered” the art of CDS and gradually becoming more and more proficient in using AMDP. Using CDS and AMDP i solved many performance issues on Business Suite on Hana and CAR on Hana. But something kept nagging me: the idea that i wasn’t fully applying the code-to-data concept. And to be honest: I wasn’t. Surely alot of logic was pushed down to the database, but still the application layer still had enough to do (processing that otherwise could have been done on the database as well). And with my very first success on code-to-data on converting a big chunk custom development into an AMDP came the hunger for more. 

Now i’m tasked with developing a transaction that has to do a lot of unit of measurement conversions. In the pre-Hana world i would simply use function MD_CONVERT_MATERIAL_UNIT. But that would be too easy and would be the opposite of what i want to achieve: code-to-data.

So i called my bff Google and found two blogs that looked very promising:

Creating Quantity Unit of Measure Conversion in HANA by Tim Korba and

How to…implement HANA dynamic material UOM conversions by Justin Molenaur

But these solutions rely on the use of Hana modelling and i wanted something that was easy to read and implement. So my search continued and I found two AMDP’s that looked very promising:

• CL_CS_BOM_AMDP=>MAT_CONVERT_TO_BASE_UOM

and

• CL_CS_BOM_AMDP=>MAT_CONVERT_UOM_TO_BASE_UOM

Mind you: i’m working on a Business suite on Hana with Netweaver 7.4 SP12 and the availabilty of AMDP’s is very scarce.

Unfortunately, both didn’t meet my requirements: they both convert a quantity to the base unit of measurement of a material. And i want a solution that returns the same result like function MD_CONVERT_MATERIAL_UNIT. It struck me as odd that SAP didn’t already have a solution for a simple unit conversion. So much for being lazy and now i had put my mind at work to come up with a solution.

After trying out several approaches (rewriting both found AMDP’s, writing code from scratch) and wasting several hours of trying to figure out a proper formula, it finally hit me.

The way SAP presents the conversion rules in for instance MM41 and MM42 is not how the conversion rules are actually stored in table MARM. Each conversion rule in table MARM is always in relation to the base unit of measurement. Even if there is a Lower-Level Unit of Measure in a Packing Hierarchy (MARM-MESUB).

When presenting these kind of conversion rules in the GUI, you will always see the conversion rule to MARM-MESUB.

But the table MARM will show something differently:

Notice the difference between the presentation of CS in the GUI and how it is stored in MARM: always in relation to the base unit of measurement.

With that realization it was no longer a problem to come up with a formula:

converted quantity = original quantity * ((denominator uom from / nominator uom from) / (denominator uom to / nominator uom to))

Now i could write the AMDP i so badly wanted to have and that would gave me the same result as MD_CONVERT_MATERIAL_UNIT and i gladly want to share it for anyone to profit from if.

CLASS zcl_amdp_unit_of_measurement DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_data,
             matnr      TYPE matnr,
             menge_from TYPE menge_d,
             uom_from   TYPE meins,
             menge_to   TYPE menge_d,
             uom_to     TYPE meins,
           END OF ty_data,
           tty_data TYPE TABLE OF ty_data.

    CLASS-METHODS marm_convert_material_unit IMPORTING VALUE(it_data)   TYPE tty_data
                                             EXPORTING VALUE(et_result) TYPE tty_data.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS zcl_amdp_unit_of_measurement IMPLEMENTATION.

  METHOD marm_convert_material_unit BY DATABASE PROCEDURE FOR HDB
                                  LANGUAGE SQLSCRIPT
                                  OPTIONS READ-ONLY
                                  USING mara
                                        marm.

* The standard SAP AMDP CL_CS_BOM_AMDP=>MAT_CONVERT_UOM_TO_BASE_UOM does not factor in MARM-MESUB.
* Therefore, calculation will be incomplete if MESUB is used.
*
* EXAMPLE:  material with base UoM PCE and the following UoM conversion rules
* 1 PCE = 1 PCE
* 1 CS = 4 MP (MESUB = MP)
* 1 MP = 10 PCE
*
* When using CL_CS_BOM_AMDP=>MAT_CONVERT_UOM_TO_BASE_UOM the result is:
* 1 MP => 10 PCE
* 1 CS => 4 PCE
*
* This is not correct and should be:
* 1 CS = 4 MP and 1 MP = 10 PCE thus 1 CS = 4*10 PCE = 40 PCE

* Since all UoM conversions rules are stored in MARM in relation to the base UoM (even with MESUB), with a simple formula we
* can make sure the correct calculation is performed and get a converted quantity just like using function MD_CONVERT_MATERIAL_UNIT:

* Formula to convert quantity unit_from = > unit_to
* converted quantity = quantity * ( ( denominator uom_from / nominator uom_from ) / ( denominator uom_to / nominator uom_to ) )


    tmp_from =
        select marm.mandt               as mandt,
               marm.matnr               as matnr,
               marm.meinh               as uom,
               marm.umrez               as umrez,
               marm.umren               as umren
        from :it_data as data_source
        inner join mara as mara on mara.mandt   = session_context( 'CLIENT' ) and
                                   mara.matnr   = data_source.matnr
        inner join marm as marm on marm.mandt   = mara.mandt and
                                   marm.matnr   = data_source.matnr    and
                                   marm.meinh   = data_source.uom_from;

    tmp_to =
        select marm.mandt               as mandt,
               marm.matnr               as matnr,
               marm.meinh               as uom,
               marm.umrez               as umrez,
               marm.umren               as umren
        from :it_data as data_source
        inner join mara as mara on mara.mandt   = session_context( 'CLIENT' ) and
                                   mara.matnr   = data_source.matnr
        inner join marm as marm on marm.mandt   = mara.mandt and
                                   marm.matnr   = data_source.matnr    and
                                   marm.meinh   = data_source.uom_to;

* Convert original quantity to requested unit
* converted quantity = quantity * ( ( denominator uom_from / nominator uom_from ) / ( denominator uom_to / nominator uom_to ) )

    et_result =
        select data_source.matnr,
               data_source.menge_from,
               data_from.uom as uom_from,
               ROUND( ( data_source.menge_from * ( ( data_from.umrez / data_from.umren )  / ( data_to.umrez / data_to.umren) ) ), 3, ROUND_HALF_EVEN) as menge_to,
               data_to.uom   as uom_to
        from :it_data as data_source
        left join :tmp_from as data_from on data_from.matnr = data_source.matnr and
                                            data_from.uom   = data_source.uom_from
        left join :tmp_to as data_to     on data_to.matnr = data_source.matnr and
                                            data_to.uom   = data_source.uom_to;


  ENDMETHOD.
ENDCLASS.

To conclude: I had fun in writing this blog and I hope you can put the AMDP i wrote to good use. I have no idea if SAP will deliver their standard AMDP that will do the same. But until then, consider this as a nice alternative that you can implement and use.

I have done several comparisons with function MD_CONVERT_MATERIAL_UNIT  and have yet to find any differences/incorrect results. Maybe someone can break the code in which case i’d be happy to know about it so I can adjust my code.

Version 2.

After posting this blog, i had another look on the AMDP and decided the amount of code could be less. So here’s a newer version.

  METHOD marm_convert_material_unit BY DATABASE PROCEDURE FOR HDB
                                  LANGUAGE SQLSCRIPT
                                  OPTIONS READ-ONLY
                                  USING marm.

* The standard SAP AMDP CL_CS_BOM_AMDP=>MAT_CONVERT_UOM_TO_BASE_UOM does not factor in MARM-MESUB.
* Therefore, calculation will be incomplete if MESUB is used.
*
* EXAMPLE:  material with base UoM PCE and the following UoM conversion rules
* 1 PCE = 1 PCE
* 1 CS = 4 MP (MESUB = MP)
* 1 MP = 10 PCE
*
* When using CL_CS_BOM_AMDP=>MAT_CONVERT_UOM_TO_BASE_UOM the result is:
* 1 MP => 10 PCE
* 1 CS => 4 PCE
*
* This is not correct and should be:
* 1 CS = 4 MP and 1 MP = 10 PCE thus 1 CS = 4*10 PCE = 40 PCE

* Since all UoM conversions rules are stored in MARM in relation to the base UoM (even with MESUB), with a simple formula we
* can make sure the correct calculation is performed and get a converted quantity just like using function MD_CONVERT_MATERIAL_UNIT:

* Formula to convert quantity unit_from = > unit_to
* converted quantity = quantity * ( ( denominator uom_from / nominator uom_from ) / ( denominator uom_to / nominator uom_to ) )


    et_result =
        select data_source.matnr,
               data_source.menge_from,
               data_source.uom_from,
               case when marm_to.umren   = 0 or marm_to.umren   is NULL then 0.000
                    when marm_from.umren = 0 or marm_from.umren is NULL then 0.000
                    when marm_to.umren is NOT NULL and marm_from.umren is NOT NULL then
                         ROUND( ( data_source.menge_from * ( ( marm_from.umrez / marm_from.umren )  / ( marm_to.umrez / marm_to.umren) ) ), 3, ROUND_HALF_EVEN)
               end as menge_to,
               marm_to.meinh as uom_to
        from :it_data as data_source
        left outer join marm as marm_from on marm_from.mandt = session_context( 'CLIENT' ) and
                                        marm_from.matnr = data_source.matnr and
                                        marm_from.meinh = data_source.uom_from
        left outer join marm as marm_to   on marm_to.mandt   = session_context( 'CLIENT' ) and
                                        marm_to.matnr   = data_source.matnr and
                                        marm_to.meinh   = data_source.uom_to;


  ENDMETHOD.

Version 3.

This version uses the conversion factor for UoM in T006 and T006D. If a normal conversion fails because a required UoM is not maintained in the masterdata, another conversion is executed against T006 and T006D. But the UoM from and UoM to must be both of the same dimension and at least 1 UoM is maintained in the masterdata.

  METHOD marm_convert_material_unit BY DATABASE PROCEDURE FOR HDB
                                  LANGUAGE SQLSCRIPT
                                  OPTIONS READ-ONLY
                                  USING marm.

* The standard SAP AMDP CL_CS_BOM_AMDP=>MAT_CONVERT_UOM_TO_BASE_UOM does not factor in MARM-MESUB.
* Therefore, calculation will be incomplete if MESUB is used.
*
* EXAMPLE:  material with base UoM PCE and the following UoM conversion rules
* 1 PCE = 1 PCE
* 1 CS = 4 MP (MESUB = MP)
* 1 MP = 10 PCE
*
* When using CL_CS_BOM_AMDP=>MAT_CONVERT_UOM_TO_BASE_UOM the result is:
* 1 MP => 10 PCE
* 1 CS => 4 PCE
*
* This is not correct and should be:
* 1 CS = 4 MP and 1 MP = 10 PCE thus 1 CS = 4*10 PCE = 40 PCE

* Since all UoM conversions rules are stored in MARM in relation to the base UoM (even with MESUB), with a simple formula we
* can make sure the correct calculation is performed and get a converted quantity just like using function MD_CONVERT_MATERIAL_UNIT:

* Formula to convert quantity unit_from = > unit_to
* converted quantity = quantity * ( ( denominator uom_from / nominator uom_from ) / ( denominator uom_to / nominator uom_to ) )


    tmp_result =
        select data_source.matnr,
               data_source.menge_from,
               data_source.uom_from,
               case when marm_to.umren   = 0 or marm_to.umren   is NULL then 0.000
                    when marm_from.umren = 0 or marm_from.umren is NULL then 0.000
                    when marm_to.umren is NOT NULL and marm_from.umren is NOT NULL then
                         ROUND( ( data_source.menge_from * ( ( marm_from.umrez / marm_from.umren )  / ( marm_to.umrez / marm_to.umren) ) ), 3, ROUND_HALF_EVEN)
               end as menge_to,
               marm_to.meinh as uom_to
        from :it_data as data_source
        left outer join marm as marm_from on marm_from.mandt = session_context( 'CLIENT' ) and
                                        marm_from.matnr = data_source.matnr and
                                        marm_from.meinh = data_source.uom_from
        left outer join marm as marm_to   on marm_to.mandt   = session_context( 'CLIENT' ) and
                                        marm_to.matnr   = data_source.matnr and
                                        marm_to.meinh   = data_source.uom_to;

* If no conversion could be carried out, retry with standard CONVERT_UNIT
    et_result =
        select matnr,
               menge_from,
               uom_from,
               menge_to,
               uom_to
        from :tmp_result
        where menge_to <> 0 and menge_to is NOT NULL

        union all

        select no_cnv.matnr,
               no_cnv.menge_from,
               no_cnv.uom_from,
               CONVERT_UNIT("QUANTITY"=>no_cnv.menge_from,
                            "SOURCE_UNIT_COLUMN"=>no_cnv.uom_from,
                            "SCHEMA"=>'SAPSR3',
                            "TARGET_UNIT_COLUMN"=>data_source.uom_to,
                            "ERROR_HANDLING"=>'set to null',
                            "CLIENT"=>session_context('CLIENT')) AS menge_to,
               data_source.uom_to
        from :tmp_result as no_cnv
        inner join :it_data as data_source on no_cnv.matnr = data_source.matnr
        where no_cnv.menge_to = 0 or no_cnv.menge_to is NULL;


  ENDMETHOD.

Example: material A has a conversion factor of 1 PCE = 1 KG. Now you can calculate the amount in unit TO. Even though TO is not maintained.

Regards,

Guus