Skip to Content
Technical Articles

Alternative to MD_STOCK_REQUIREMENTS_LIST_API to get Item in MRP document (MDPS), Individual lines of the MRP elements (MDEZ) for multiple materials / plant combination

Introduction

Normally there is a need to get stock requirements information for certain selected materials and plants. After scouting through various answers in sap forums and blogs we knew we had to use the FM MD_STOCK_REQUIREMENTS_LIST_API or BAPI_MATERIAL_STOCK_REQ_LIST to get results similar to MD04 transaction.

We faced huge performance issues after doing a Loop at material/ plant internal table and calling the FM. Hence we started analyzing the FM and stumbled across an AMDP method (ABAP managed Database Procedure) which will suits our need.

The approach below may answer the questions raised here:

Alternate fn mod for MD_STOCK_REQUIREMENTS_LIST_API

Multiple material for MD04 output

https://answers.sap.com/questions/7487401/md04-but-for-multiple-materials.html

 

I could find only one blog post which came near to the requirement but still calling the FM MD_STOCK_REQUIREMENTS_LIST_API in loop seemed too costly.

Custom Report: Stock and Requirement List by Exception Message

 

An approach:

Code Declaration:

*-Types Declaration-*
    TYPES : BEGIN OF lty_mdez,
              matnr TYPE mara-matnr,
              werks TYPE marc-werks,
              plaab TYPE mdps-plaab,
              delkz TYPE mdps-delkz,
              mng01 TYPE mdps-mng01,
            END OF lty_mdez.

    TYPES : BEGIN OF lty_mat_werks,
              matnr TYPE mara-matnr,
              werks TYPE marc-werks,
            END OF lty_mat_werks.

types: BEGIN OF gty_on_hand ,
        matnr     TYPE mara-matnr,
        werks     TYPE mard-werks,
        avail_qty TYPE bapi_mrp_total_lines-avail_qty,
       END OF gty_on_hand.

data gt_on_hand TYPE STANDARD TABLE OF gty_on_hand .

*-Data Declaration for various MRP elements-*
    DATA lt_mdez TYPE STANDARD TABLE OF lty_mdez.
    DATA lt_mdez_wb TYPE STANDARD TABLE OF lty_mdez.
    DATA lt_mdez_vc TYPE STANDARD TABLE OF lty_mdez.
    DATA lt_mdez_kb TYPE STANDARD TABLE OF lty_mdez.
    DATA lt_mdez_vj TYPE STANDARD TABLE OF lty_mdez.
    DATA lt_mdez_ar TYPE STANDARD TABLE OF lty_mdez.

*-Included for OnHandQty calculation
    DATA lt_mdez_ba TYPE STANDARD TABLE OF lty_mdez.
    DATA lt_mdez_be TYPE STANDARD TABLE OF lty_mdez.
    DATA lt_mdez_la TYPE STANDARD TABLE OF lty_mdez.

*-Global temporary table for selecting from HANA (PP)
    DATA lt_selection TYPE pph_matnr_werks_berid_sel_tab.
    DATA lt_mdps TYPE pph_mdps_ext_tab.

*-Access HANA functions for PP
      lo_hana_access_agg TYPE REF TO cl_pph_hana_access,
      lv_cutoff_date     TYPE sydatum,
      lx_sql_exc         TYPE REF TO cx_sql_exception.

 

1.Get required material and plant in LT_SELECTION internal table

*-Select materials and Plants based on selection screen-*
      SELECT matnr, 
             werks 
      FROM marc 
      INTO TABLE @lt_selection
      WHERE matnr in @s_materials
      AND   werks in @s_plants.

 

2.Call the AMDP method

   lv_cutoff_date = '99991231'.

    TRY.
        lo_hana_access_agg =
        cl_pph_hana_access=>get_instance(
        iv_result_view_name = cl_pph_hana_access=>gc_default_result_view_agg ).
        lo_hana_access_agg->execute_procedure_s4h(
        EXPORTING
          iv_agg           = abap_true
          iv_cutoff_date   = lv_cutoff_date
          it_selection     = lt_selection
        IMPORTING
          et_mdps_out =  lt_mdps ).

      CATCH cx_sql_exception INTO lx_sql_exc.
        ASSERT lx_sql_exc IS INITIAL.
    ENDTRY.

 

3.To get Sales Requirements Total Records (VBBS)

Additionally to fetch Sales Requirements Total Records (VBBS) then you can create custom CDS

view with VBBS and use AMDP method as below:-

 

3.a) CDS View for VBBS:

@AbapCatalog.sqlViewName: 'YGP_SQL_VBBS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'DDL for VBBS'
define view YGP_DDL_VBBS as select from vbbs
inner join marc as matctrl
    on vbbs.matnr = matctrl.matnr and
       vbbs.werks = matctrl.werks 
{
  vbbs.matnr,
      vbbs.werks,
      mbdat as dat00,
      case vbtyp when 'T' then '1'
                          else '2'
      end as sort1,
      '03' as sort2,
      case vbtyp
        when 'A' then 'VA'
          when 'B' then 'VB'
            when 'C' then 'VC'
          when 'E' then 'VE'
          when 'F' then 'VF'
          when 'G' then 'VG'
          when 'I' then 'VI'
          when 'J' then 'VJ'
          when 'c' then 'VZ'
          when 'T' then 'VT'
          when 'W' then 'VW'
          when 'C' then 'VC'
          else ''
      end as delkz,
      '' as vrfkz,
      -- VRFKZ ABAP from NODIS and MARA/MARD-DISFV
      case when vbtyp = 'T' then '+'
                                else '-'
      end as plumi,
      case when vbtyp = 'T' then omeng
                            else - omeng
      end  as mng01,

      case when vpzuo = '3' and 
             ( vpmat != ''  and ( vpmat != matctrl.matnr or 
                                  vpwrk != matctrl.werks ) ) 
           then 0
         else pzmng
      end as mng03,
      mbdat as dat01,
      -- DAT02 ABAP factory calendar
      bdart as baart,
      plart,
      lgort,
      charg,

      case when vpzuo = '3' and 
              ( vpmat = '' or 
              ( vpmat =  matctrl.matnr and vpwrk =  matctrl.werks ) ) 
              then '2'
           else vpzuo
      end as vpzuo,

      case when vpzuo != ''then '1'
           else ''
      end as vervp,
      sernr,
      sobkz,
  -- from here fields which are necessary to determine other fields in ABAP
      nodis as VBBS_no_disp,
      kzvbr as kzvbr_db 
    
}
 where ( omeng > 0 or pzmng > 0 )
      and nodis <> '2';

 

3.b) AMDP method:

  PUBLIC SECTION.
  types:
     begin of gty_vbbs,
      MATNR   TYPE    MATNR   ,
      WERKS   TYPE    WERKS_D ,
      DAT00   TYPE    DAT00   ,
      SORT1   TYPE    SORT1   ,
      SORT2   TYPE    SORT2   ,
      DELKZ   TYPE    DELKZ   ,
      VRFKZ   TYPE    VRFKZ   ,
      PLUMI   TYPE    PLUMI   ,
      MNG01   TYPE    MNG01   ,
      MNG03   TYPE    MNG03   ,
      DAT01   TYPE    DAT01   ,
      BAART   TYPE    BAART   ,
      PLART   TYPE    PLART   ,
      LGORT   TYPE    LGORT_D ,
      CHARG   TYPE    CHARG_D ,
      VPZUO   TYPE    VPZUO   ,
      VERVP   TYPE    VERVP   ,
      SERNR   TYPE    SERNR   ,
      SOBKZ   TYPE    SOBKZ   ,
      VBBS_NO_DISP    TYPE    nodis   ,
      KZVBR_DB    TYPE  kzvbr  ,

     end of gty_vbbs,
     gtt_vbbs type TABLE OF gty_vbbs.

    INTERFACES if_amdp_marker_hdb.
    CLASS-METHODS get_vbbs
      IMPORTING
        VALUE(iv_mandt)       TYPE sy-mandt
        VALUE(iv_agg)         TYPE char1
        VALUE(iv_cutoff_date) TYPE sydatum
        VALUE(it_sel)         TYPE pph_mrp_sel_mat_plant_tab
      EXPORTING
        VALUE(et_vbbs_out)    TYPE gtt_vbbs.

  METHOD get_vbbs
  BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
          OPTIONS READ-ONLY
          USING ygp_sql_vbbs.
    et_vbbs_out = select m.matnr,
  m.werks,
  m.dat00,
  m.sort1,
  m.sort2,
  m.delkz,
  m.vrfkz,
  m.plumi,
  m.mng01,
  m.mng03,
  m.dat01,
  m.baart,
  m.plart,
  m.lgort,
  m.charg,
  m.vpzuo,
  m.vervp,
  m.sernr,
  m.sobkz,
  m.vbbs_no_disp,
  m.kzvbr_db
   from ygp_sql_vbbs as m
   where ( m.matnr, m.werks ) in ( select matnr, werks from :it_sel  ) AND M.MANDT = IV_MANDT
AND dat00 <= :iv_cutoff_date;

  endmethod.

 

3.c) Call the AMDP method

*-Call the custom AMDP method
    DATA lt_sel TYPE pph_mrp_sel_mat_plant_tab.

    lt_sel[] = CORRESPONDING #( lt_selection[] ).
    CALL METHOD Ycl_mdp=>get_vbbs
      EXPORTING
        iv_mandt       = sy-mandt
        iv_agg         = abap_true
        iv_cutoff_date = lv_cutoff_date
        it_sel         = lt_sel
      IMPORTING
        et_vbbs_out    = DATA(lt_vbbs).

    LOOP AT lt_vbbs ASSIGNING FIELD-SYMBOL(<lfs_indv_vbbs>).
      APPEND INITIAL LINE TO lt_mdps ASSIGNING FIELD-SYMBOL(<lfs_vbbe>).
      MOVE-CORRESPONDING <lfs_indv_vbbs> TO <lfs_vbbe>.
    ENDLOOP.

 

4. Collect the data from MRP elements

    lt_mdez =  CORRESPONDING #( lt_mdps ).
    DATA(lt_mdez_master) = lt_mdez.
    lt_mat_werks = CORRESPONDING #( lt_mdez_master ).
    SORT lt_mat_werks BY matnr werks.
    DELETE ADJACENT DUPLICATES FROM lt_mat_werks COMPARING matnr werks.
    SORT lt_mdez BY matnr werks delkz.

    LOOP AT lt_mdez INTO DATA(lw_mdez) .
      IF lw_mdez-delkz EQ 'WB'.
        COLLECT lw_mdez INTO  lt_mdez_wb.
      ENDIF.
      IF lw_mdez-delkz EQ 'VC'.
        COLLECT lw_mdez INTO  lt_mdez_vc.
      ENDIF.
      IF lw_mdez-delkz EQ 'AR'.
        COLLECT lw_mdez INTO  lt_mdez_ar.
      ENDIF.
      IF lw_mdez-delkz EQ 'KB'.
        COLLECT lw_mdez INTO  lt_mdez_kb.
      ENDIF.
      IF lw_mdez-delkz EQ 'VJ'.
        COLLECT lw_mdez INTO lt_mdez_vj.
      ENDIF.
*-Included for OnHandQty calculation
      IF lw_mdez-delkz EQ 'BA' and lw_mdez-plaab eq 20.
        COLLECT lw_mdez INTO  lt_mdez_BA.
      ENDIF.
      IF lw_mdez-delkz EQ 'BE' and lw_mdez-plaab eq 20.
        COLLECT lw_mdez INTO  lt_mdez_BE.
      ENDIF.
      IF lw_mdez-delkz EQ 'LA' and lw_mdez-plaab eq 20.
        COLLECT lw_mdez INTO lt_mdez_LA.
      ENDIF.
      CLEAR lw_mdez.
    ENDLOOP.

    SORT : lt_mdez_wb BY matnr werks.
    SORT : lt_mdez_vc BY matnr werks.
    SORT : lt_mdez_vj BY matnr werks. 
    SORT : lt_mdez_kb BY matnr werks.
    SORT : lt_mdez_ar BY matnr werks.

*-Included for OnHandQty calculation
    SORT : lt_mdez_ba BY matnr werks.
    SORT : lt_mdez_be BY matnr werks.
    SORT : lt_mdez_la BY matnr werks.

    LOOP AT lt_mat_werks INTO DATA(lw_mat_werks).
      APPEND INITIAL LINE TO gt_on_hand ASSIGNING FIELD-SYMBOL(<gf_on_onhand>).
      <gf_on_onhand>-matnr =  lw_mat_werks-matnr.
      <gf_on_onhand>-werks =  lw_mat_werks-werks.
      READ TABLE lt_mdez_wb INTO DATA(lw_wb) WITH KEY matnr = lw_mat_werks-matnr
            werks = lw_mat_werks-werks BINARY SEARCH.
      IF sy-subrc IS INITIAL.
        ADD lw_wb-mng01 TO <gf_on_onhand>-avail_qty.
        CLEAR lw_wb.
      ENDIF.
      READ TABLE lt_mdez_vc INTO DATA(lw_vc) WITH KEY matnr = lw_mat_werks-matnr
            werks = lw_mat_werks-werks BINARY SEARCH.
      IF sy-subrc IS INITIAL.
        ADD lw_vc-mng01 TO <gf_on_onhand>-avail_qty.
        CLEAR lw_vc.
      ENDIF.
      READ TABLE lt_mdez_vj INTO DATA(lw_vj) WITH KEY matnr = lw_mat_werks-matnr
            werks = lw_mat_werks-werks BINARY SEARCH.
      IF sy-subrc IS INITIAL.
        ADD lw_vj-mng01 TO <gf_on_onhand>-avail_qty.
        CLEAR lw_vj.
      ENDIF.

      READ TABLE lt_mdez_kb INTO DATA(lw_kb) WITH KEY matnr = lw_mat_werks-matnr
            werks = lw_mat_werks-werks BINARY SEARCH.
      IF sy-subrc IS INITIAL.
        ADD lw_kb-mng01 TO <gf_on_onhand>-avail_qty.
        CLEAR lw_kb.
      ENDIF.
      READ TABLE lt_mdez_ar INTO DATA(lw_ar) WITH KEY matnr = lw_mat_werks-matnr
            werks = lw_mat_werks-werks BINARY SEARCH.
      IF sy-subrc IS INITIAL.
        ADD lw_ar-mng01 TO <gf_on_onhand>-avail_qty.
        CLEAR lw_ar.
      ENDIF.

*-Included for OnHandQty calculation
      READ TABLE lt_mdez_ba INTO DATA(lw_ba) WITH KEY matnr = lw_mat_werks-matnr
                                                      werks = lw_mat_werks-werks BINARY SEARCH.
      IF sy-subrc IS INITIAL.
        ADD lw_ba-mng01 TO <gf_on_onhand>-avail_qty.
        CLEAR lw_ba.
      ENDIF.
      READ TABLE lt_mdez_be INTO DATA(lw_be) WITH KEY matnr = lw_mat_werks-matnr
                                                      werks = lw_mat_werks-werks BINARY SEARCH.
      IF sy-subrc IS INITIAL.
        ADD lw_be-mng01 TO <gf_on_onhand>-avail_qty.
        CLEAR lw_be.
      ENDIF.
      READ TABLE lt_mdez_la INTO DATA(lw_la) WITH KEY matnr = lw_mat_werks-matnr
                                                      werks = lw_mat_werks-werks BINARY SEARCH.
      IF sy-subrc IS INITIAL.
        ADD lw_la-mng01 TO <gf_on_onhand>-avail_qty.
        CLEAR lw_la.
      ENDIF.
      IF <gf_on_onhand>-avail_qty LT 0.
        CLEAR <gf_on_onhand>-avail_qty .
      ENDIF.
    ENDLOOP.

5. Testing –

Result of Step 2 execution ( in LT_MDPS)

Result of Step 3.c) execution (in LT_VBBS)

After summing up the quantities for every material/ plant combination we get On hand Quantity as below-

 

 

While this might not seem to be the best approach, if you come across something else, let me know.

Thanks for reading till the end!

1 Comment
You must be Logged on to comment or reply to a post.