Material Quantity Unit Conversion using CDS Views

[badal_bhasin]

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.