Skip to Content
Author's profile photo Badal Bhasin

Material Quantity Unit Conversion using CDS Views

1.    Objective

The objective of this blog is to explain how to convert material quantity unit into base unit or another alternative unit of measure as per MARM table (defined in material master).

2.    Requirement

Requirement is to fetch material quantity from a DB table and then display that quantity on screen. But the unit of measure in which material quantity needs to be displayed on UI can be different than the unit of measure in which it is saved. The standard quantity conversion function ‘UNIT_CONVERSION’ which is provided in CDS can’t be used in this case as it performs the unit conversion based on the rules stored in transaction CUNI and in the database tables T006. But in our scenario, we need to perform material quantity conversion based on the alternative unit of measures defined in material master (available in MARM table).

 

Let’s take the below example:

Material Base Unit of Measure = ‘CV’ (Case)

 

Alternate Unit of measure maintained in material master are: ‘PC’ (Piece) & ‘PAL’(Pallet).

Entries in MARM table:

In CDS view, quantity will be fetched from database table & then it will check if the Unit of measure in which quantity needs to be displayed on UI is different from unit of measure stored in database table, then it will get the conversion formula maintained (in MARM) using associations on standard CDS view I_PRODUCTUNITSOFMEASURE. Therefore the quantity will be converted to the base unit of measure and then it will be converted from base unit of measure to the display unit of measure.

 

 

 

3.    Design

The following steps explain step by step configuration:

  • Create a CDS view and fetch the quantity data from the DB table.
  • In that CDS view make an association on SAP standard CDS view I_MATERIAL (CDS view for MARA table) to get the material base unit of measure.
  • Two times Association or Left outer join on I_PRODUCTUNITSOFMEASURE (Standard CDS view of MARM table) using key Material and unit of measure. One with the Display Unit of measure and one with unit of measure in which quantity is stored in DB.

 

 

4.    Coding

Create the below CDS View:

@AbapCatalog.sqlViewName: 'ZMATQUAN'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Material Quantity'
define view Z_MATERIAL_QUANTITY
  with parameters
    p_disp_unit : meins
  as select from zmat_quan as MatQuan

// For Base Unit of Measure
  association [0..1] to I_Material              as _MARA                 on  MatQuan.matnr = _MARA.Material

// Association on CDS view of MARM table
  association [0..1] to I_ProductUnitsOfMeasure as _ProductUnitUI        on  _ProductUnitUI .Product         = MatQuan.matnr
                                                                         and _ProductUnitUI .AlternativeUnit = :p_disp_unit
  
  association [0..1] to I_ProductUnitsOfMeasure as _ProductUnitEntryUnit on  MatQuan.matnr    = _ProductUnitEntryUnit.Product
                                                                         and MatQuan.qty_unit = _ProductUnitEntryUnit.AlternativeUnit 

{
  bukrs,
  plant,
  matnr as Material,
  del_quan as DeliveryQuantity,
  qty_unit as DeliveryUnit,
  $parameters.p_disp_unit as DispUnit,
  _ProductUnitUI,
  _ProductUnitEntryUnit,
  _MARA.Material as mat,
  case
  when MatQuan.qty_unit = :p_disp_unit
  then del_quan
  else
      case
        when _MARA.MaterialBaseUnit = $parameters.p_disp_unit
        then del_quan* DIVISION( (_ProductUnitEntryUnit.QuantityNumerator), (_ProductUnitEntryUnit.QuantityDenominator), 3)
        else        
        del_quan* DIVISION( (_ProductUnitEntryUnit.QuantityNumerator*_ProductUnitUI.QuantityDenominator), (_ProductUnitEntryUnit.QuantityDenominator*_ProductUnitUI.QuantityNumerator), 3)        
        end
  end      as DisplayQuantity
}

 

5.    Test

  • Data in the custom table:

  • Execute the CDS View & provide Display Unit of Measure (In which unit result is required). In our case we are providing ‘ST’ (which is internal format for ‘PC’ Piece).

  • Result:

The material quantity is successfully converted into Piece from Pallet.

 

 

6.    Limitations

  • All unit of measure’s (Base as well as alternative) in which quantity conversion is required should be maintained in material master.

 

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jakob Marius Kjær
      Jakob Marius Kjær

      Cool stuff. Straight forward example.

       

      Well done!

      Author's profile photo Gaurav Sharan
      Gaurav Sharan

      Awesome !! really helpful.

      Author's profile photo Rohit Khan
      Rohit Khan

      Excellent blog and a very good example of currency conversion!

      Thanks for sharing!

       

      Author's profile photo Deepa Sharma
      Deepa Sharma

      Informative Blog...

      Well Explained with example...

      Thanks for sharing ...

      Author's profile photo Nenghong Fu
      Nenghong Fu

       

      well explained with good example.

      Author's profile photo Rajesh Kumar Khatwa
      Rajesh Kumar Khatwa

      Excellent Blog, very well explained

      Author's profile photo AMIT AGARWAL
      AMIT AGARWAL

      Excellent Blog, easy to understand and to the point.

      Author's profile photo Tiago de Souza Silva
      Tiago de Souza Silva

      Helpful! Thanks!

      Author's profile photo Simon Hansen
      Simon Hansen

      Hi,

      i would share some additional helpful information for all those who will read this blog.

      We had the issue that the mentioned division above results in some rounding differences.

      del_quan* DIVISION( (_ProductUnitEntryUnit.QuantityNumerator), (_ProductUnitEntryUnit.QuantityDenominator), 3)

      So if someone has the same problem- "just" use the multiplication with your origin key figure inside the funktion division().

       

      then DIVISION( (del_quan *_ProductUnitEntryUnit.QuantityDenominator), (_ProductUnitEntryUnit.QuantityNumerator), 6)

       

      @Semantics.quantity.unitOfMeasure: 'TargetUnit'
            @DefaultAggregation : #SUM
            @EndUserText.label: 'Auslieferung TUnit' 
             case
            when DeliveryQuantityUnit = :p_disp_unit or OutbDelItem.OriginalDeliveryQuantity = 0
            then OutbDelItem.OriginalDeliveryQuantity 
            else
            case
              when BaseUnit = $parameters.p_disp_unit
              then  Division( ( OutbDelItem.OriginalDeliveryQuantity *_ProductUnitEntryUnit.QuantityDenominator) ,_ProductUnitEntryUnit.QuantityNumerator , 2)
              else        
              DIVISION((OutbDelItem.OriginalDeliveryQuantity * (_ProductUnitEntryUnit.QuantityNumerator*_ProductUnitUI.QuantityDenominator) ), (_ProductUnitEntryUnit.QuantityDenominator*_ProductUnitUI.QuantityNumerator)  , 6)       
              end
              end      
              as OutboundDeliveryQuantityBas,

       

      hope it will help you.

      thanks a lot

      Simon