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:
and
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.
Regards,
Guus
Okumaya devam et...
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.
Regards,
Guus
Okumaya devam et...