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.
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.
No comments:
Post a Comment